[Home] [Help]
PACKAGE BODY: APPS.ASO_MAP_QUOTE_ORDER_INT
Source
1 PACKAGE BODY ASO_MAP_QUOTE_ORDER_INT as
2 /* $Header: asoimqob.pls 120.29.12010000.3 2008/11/13 04:57:53 rassharm ship $ */
3 -- Start of Comments
4 -- Package name : ASO_MAP_QUOTE_ORDER_INT
5 -- Purpose :
6 -- History :
7 -- 10/18/2002 hyang - 2633507, performance fix
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_MAP_QUOTE_ORDER_INT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoimqob.pls';
13
14
15 PROCEDURE Map_Quote_to_order(
16 P_Operation IN VARCHAR2,
17 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
18 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
19 P_Header_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
20 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
21 P_Header_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
22 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
23 P_Header_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
24 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
25 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
26 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
27 P_Header_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
28 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
29 P_Header_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
30 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
31 P_Header_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
32 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
33 P_Header_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
34 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
35 P_header_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
36 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
37 P_Qte_Line_Tbl IN ASO_QUOTE_PUB.Qte_Line_Tbl_Type
38 := ASO_QUOTE_PUB.G_MISS_qte_line_TBL,
39 P_Qte_Line_Dtl_TBL IN ASO_QUOTE_PUB.Qte_Line_Dtl_TBL_Type
40 := ASO_QUOTE_PUB.G_MISS_qte_line_dtl_TBL,
41 P_Line_Payment_Tbl IN ASO_QUOTE_PUB.Payment_Tbl_Type
42 := ASO_QUOTE_PUB.G_MISS_Payment_TBL,
43 P_Line_Price_Adj_Tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type
44 := ASO_QUOTE_PUB.G_MISS_Price_Adj_TBL,
45 P_Line_Price_Attributes_Tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
46 := ASO_QUOTE_PUB.G_MISS_Price_attributes_TBL,
47 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
48 := ASO_QUOTE_PUB.G_MISS_price_adj_rltship_TBL,
49 P_Line_Price_Adj_Attr_Tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type
50 := ASO_QUOTE_PUB.G_Miss_PRICE_ADJ_ATTR_Tbl,
51 P_Line_Shipment_Tbl IN ASO_QUOTE_PUB.Shipment_Tbl_Type
52 := ASO_QUOTE_PUB.G_MISS_shipment_TBL,
53 P_Line_TAX_DETAIL_Tbl IN ASO_QUOTE_PUB.TAX_DETAIL_Tbl_Type
54 := ASO_QUOTE_PUB.G_MISS_TAX_DETAIL_TBL,
55 P_Line_FREIGHT_CHARGE_Tbl IN ASO_QUOTE_PUB.FREIGHT_CHARGE_Tbl_Type
56 := ASO_QUOTE_PUB.G_MISS_FREIGHT_CHARGE_Tbl,
57 P_Line_Rltship_Tbl IN ASO_QUOTE_PUB.Line_Rltship_Tbl_Type
58 := ASO_QUOTE_PUB.G_MISS_line_rltship_TBL,
59 P_Line_sales_credit_TBL IN ASO_QUOTE_PUB.Sales_credit_tbl_type
60 := ASO_QUOTE_PUB.G_MISS_sales_credit_TBL,
61 P_Lot_Serial_Tbl IN ASO_QUOTE_PUB.Lot_Serial_Tbl_Type
62 := ASO_QUOTE_PUB.G_MISS_Lot_Serial_Tbl,
63 P_Calculate_Price_Flag IN VARCHAR2 := FND_API.G_FALSE
64 , x_header_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Rec_Type
65 , x_header_val_rec OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Val_Rec_Type
66 , x_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type
67 , x_Header_Adj_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Val_Tbl_Type
68 , x_Header_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type
69 , x_Header_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type
70 , x_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
71 , x_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type
72 , x_Header_Scredit_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Val_Tbl_Type
73 , x_Header_Payment_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Payment_Tbl_Type
74 , x_line_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Tbl_Type
75 , x_line_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Val_Tbl_Type
76 , x_Line_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Tbl_Type
77 , x_Line_Adj_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Val_Tbl_Type
78 , x_Line_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Price_Att_Tbl_Type
79 , x_Line_Adj_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Att_Tbl_Type
80 , x_Line_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
81 , x_Line_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Tbl_Type
82 , x_Line_Scredit_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Scredit_Val_Tbl_Type
83 , x_Lot_Serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type
84 , x_Lot_Serial_val_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Val_Tbl_Type
85 , x_Line_Payment_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Line_Payment_Tbl_Type
86 )
87 IS
88
89 CURSOR c_functional_currency (X_set_of_books_id NUMBER) IS
90 SELECT currency_code
91 FROM gl_sets_of_books
92 WHERE set_of_books_id = X_set_of_books_id;
93
94 /*
95 * 2633507 - hyang: use OE_ORDER_SOURCES instead of aso_i_order_sources_v
96 */
97
98 CURSOR C_order_source(quote_source_code VARCHAR2) IS
99 SELECT order_source_id
100 FROM OE_ORDER_SOURCES
101 WHERE name = quote_source_code;
102
103 -- Change START
104 -- Release 12 MOAC Changes : Bug 4500739
105 -- Changes Done by : Girish
106 -- Comments : Changed the reference from ASO_I_OE_ORDER_HEADERS_V to OE_ORDER_HEADERS_V
107
108 CURSOR get_cust_account_id(order_id NUMBER) IS
109 select sold_to_org_id from oe_order_headers_v
110 where header_id = order_id;
111
112
113 -- Change END
114
115 CURSOR get_cust_acct_site_id(l_site_use_id number) IS
116 select cust_acct_site_id from hz_cust_site_uses
117 where site_use_id = l_site_use_id;
118
119 CURSOR C_get_quote_number(l_qte_hdr_id NUMBER) IS
120 SELECT quote_number, quote_version
121 FROM aso_quote_headers_all
122 WHERE quote_header_id = l_qte_hdr_id ;
123
124 CURSOR scheduling_level_cur(p_type_id number) IS
125 SELECT nvl(scheduling_level_code,' ')
126 FROM OE_TRANSACTION_TYPES_ALL
127 WHERE transaction_type_id = p_type_id;
128
129 l_order_scheduling_level VARCHAR2(30) := ' ' ;
130 l_line_scheduling_level VARCHAR2(30);
131
132 -- declaration for line and header level record types
133
134 l_api_version_number CONSTANT NUMBER := 1.0;
135 l_api_name CONSTANT VARCHAR2(30):= 'MAP_QUOTE_TO_Order';
136 l_control_rec OE_GLOBALS.Control_Rec_Type;
137 l_return_status VARCHAR2(1);
138 l_header_rec OE_Order_PUB.Header_Rec_Type;
139 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
140 l_Header_price_Att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type ;
141 l_Header_Adj_Att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type ;
142 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type ;
143 l_Header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
144 l_line_tbl OE_Order_PUB.Line_Tbl_Type;
145 l_Line_Adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
146 l_Line_price_Att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type ;
147 l_Line_Adj_Att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type ;
148 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type ;
149 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
150 l_Lot_Serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
151 l_action_request_tbl OE_Order_PUB.Request_Tbl_Type;
152
153
154 -- declaration for line and header level value record types
155 l_return_values varchar2(50);
156 l_header_val_rec OE_Order_PUB.Header_Val_Rec_Type;
157 l_header_adj_val_tbl OE_Order_PUB.Header_Adj_Val_Tbl_Type;
158 l_header_scredit_val_tbl OE_Order_PUB.Header_Scredit_Val_Tbl_Type;
159 l_line_val_tbl OE_Order_PUB.Line_Val_Tbl_Type;
160 l_line_adj_val_tbl OE_Order_PUB.Line_Adj_Val_Tbl_Type;
161 --Line Payments change
162 l_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
163 l_final_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
164 l_line_scredit_val_tbl OE_Order_PUB.Line_Scredit_Val_Tbl_Type;
165 l_lot_serial_val_tbl OE_Order_PUB.Lot_Serial_Val_Tbl_Type;
166 l_msg_count number;
167 l_msg_data varchar2(2000);
168 l_invoice_cust_account_id number;
169 l_ship_cust_account_id number;
170 l_end_cust_account_id number;
171 l_invoice_cust_account_site number;
172 l_ship_cust_account_site number;
173 l_quote_number NUMBER;
174 l_version_number NUMBER;
175
176 -- other variable and count declarations
177
178 found VARCHAR2(1) := FND_API.G_FALSE;
179 found_service VARCHAR2(1) := FND_API.G_FALSE;
180 found_tax VARCHAR2(1) := FND_API.G_FALSE;
181 i NUMBER := 1;
182 j NUMBER := 1;
183 k NUMBER := 1;
184 l NUMBER := 1;
185 option_item NUMBER;
186 parent NUMBER;
187 count1 NUMBER;
188 count2 NUMBER;
189 l_sort_order VARCHAR2(240);
190 l_component_sequence_id NUMBER;
191 l_org_id NUMBER;
192 l_cust_account_id NUMBER;
193 l_employee_person_id NUMBER;
194 l_org_party_id NUMBER;
195 l_header_party_id NUMBER;
196 l_quote_source VARCHAR2(240);
197
198 -- currency conversion
199
200 l_set_of_books_id NUMBER
201 := oe_profile.value('OE_SET_OF_BOOKS_ID', p_qte_rec.org_id);
202 --:= to_number(FND_PROFILE.value('OE_SET_OF_BOOKS_ID'));
203 l_conversion_type VARCHAR2(30)
204 := FND_PROFILE.Value('ASO_QUOTE_CONVERSION_TYPE');
205 l_conversion_rate NUMBER;
206
207 l_om_defaulting_prof VARCHAR2(2)
208 := NVL(FND_PROFILE.Value('ASO_OM_DEFAULTING'), 'N');
209 l_validate_salesrep_prof VARCHAR2(2)
210 := FND_PROFILE.VALUE('ASO_VALIDATE_SALESREP');
211 l_ret_reason_code_prof VARCHAR2(200)
212 := FND_PROFILE.value('ASO_RET_REASON_CODE');
213
214 -- Change START
215 -- Release 12 MOAC Changes : Bug 4500739
216 -- Changes Done by : Girish
217 -- Comments : Using HR EIT in place of org striped profile
218
219 -- l_default_person_id_prof VARCHAR2(300) := FND_PROFILE.Value('ASO_DEFAULT_PERSON_ID');
220 l_default_person_id_prof VARCHAR2(300) := ASO_UTILITY_PVT.GET_OU_ATTRIBUTE_VALUE(ASO_UTILITY_PVT.G_DEFAULT_SALESREP);
221
222 -- Change END
223
224
225 l_reservation_lvl_prof VARCHAR2(80)
226 := FND_PROFILE.Value('ASO_RESERVATION_LEVEL');
227
228 l_sysdate DATE := sysdate;
229 l_functional_currency VARCHAR2(15);
230
231 l_g_user_id number := fnd_global.user_id;
232 l_g_login_id number := fnd_global.conc_login_id;
233 l_org_contact_party_id NUMBER;
234 l_invoice_contact_party_id NUMBER;
235 l_ship_contact_party_id NUMBER;
236 l_org_contact NUMBER;
237
238 l_hd_inv_cust_acct_site NUMBER;
239 l_hd_shp_cust_acct_site NUMBER;
240 l_hd_end_cust_acct_site NUMBER;
241 l_ln_inv_cust_acct_site NUMBER;
242 l_ln_shp_cust_acct_site NUMBER;
243 l_ln_end_cust_acct_site NUMBER;
244
245 l_Line_Price_Adj_rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
246
247 l_Header_Payment_Tbl OE_ORDER_PUB.Header_Payment_Tbl_Type;
248
249 pay_count NUMBER := 1;
250
251 CURSOR salesrep( p_resource_id NUMBER) IS
252 select salesrep_id
253 /* from jtf_rs_srp_vl */ --Commented Code Yogeshwar (MOAC)
254 from jtf_rs_salesreps_mo_v --New Code Yogeshwar (MOAC)
255 where resource_id = p_resource_id ;
256 --Commented code Start Yogeshwar (MOAC)
257 /*
258 and NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
259 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(
260 USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
261 */
262 --Commented Code End Yogeshwar (MOAC)
263
264 CURSOR C_Get_Srep_From_Snumber( lc_srep_num VARCHAR2) IS
265 SELECT Salesrep_Id
266 FROM JTF_RS_SALESREPS_MO_V
267 WHERE Salesrep_Number = lc_srep_num
268 AND NVL(status,'A') = 'A'
269 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
270 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate) ;
271
272 l_ln_total_tax_amount NUMBER := 0;
273 l_line_adj_tbl_count NUMBER := 0;
274
275 CURSOR C_GET_TAX_REASONCODE(l_header_id NUMBER) IS -- cursor made for 6781917
276 SELECT TAX_EXEMPT_REASON_CODE
277 FROM aso_tax_details
278 WHERE quote_header_id = l_header_id
279 AND quote_line_id IS NULL;
280
281 BEGIN
282 IF aso_debug_pub.g_debug_flag = 'Y' THEN
283 aso_debug_pub.add('beginning of map_quote_to_order ', 1, 'Y');
284 END IF;
285
286 -- initialize OM record types
287
288 ASO_ORDER_INT.Initialize_OM_rec_types(
289 px_header_rec => l_header_rec,
290 px_line_tbl => l_line_tbl,
291 p_line_tbl_count => p_line_shipment_tbl.count
292 );
293 IF aso_debug_pub.g_debug_flag = 'Y' THEN
294 aso_debug_pub.add('initialized OM rec types ', 1, 'N');
295 END IF;
296
297 l_header_rec.accounting_rule_id := p_qte_rec.accounting_rule_id;
298 l_header_rec.agreement_id:= p_qte_rec.contract_id;
299 l_header_rec.context := p_qte_rec.attribute_category;
300 l_header_rec.attribute1 := p_qte_rec.attribute1;
301 l_header_rec.attribute2 := p_qte_rec.attribute2;
302 l_header_rec.attribute3 := p_qte_rec.attribute3;
303 l_header_rec.attribute4 := p_qte_rec.attribute4;
304 l_header_rec.attribute5 := p_qte_rec.attribute5;
305 l_header_rec.attribute6 := p_qte_rec.attribute6;
306 l_header_rec.attribute7 := p_qte_rec.attribute7;
307 l_header_rec.attribute8 := p_qte_rec.attribute8;
308 l_header_rec.attribute9 := p_qte_rec.attribute9;
309 l_header_rec.attribute10 := p_qte_rec.attribute10;
310 l_header_rec.attribute11 := p_qte_rec.attribute11;
311 l_header_rec.attribute12 := p_qte_rec.attribute12;
312 l_header_rec.attribute13 := p_qte_rec.attribute13;
313 l_header_rec.attribute14 := p_qte_rec.attribute14;
314 l_header_rec.attribute15 := p_qte_rec.attribute15;
315 -- for bug 7560676
316 l_header_rec.attribute16 := p_qte_rec.attribute16;
317 l_header_rec.attribute17 := p_qte_rec.attribute17;
318 l_header_rec.attribute18 := p_qte_rec.attribute18;
319 l_header_rec.attribute19 := p_qte_rec.attribute19;
320 l_header_rec.attribute20 := p_qte_rec.attribute20;
321
322 l_header_rec.order_category_code := p_qte_rec.quote_category_code;
323 l_header_rec.ordered_date := p_qte_rec.ordered_date;
324 IF (l_header_rec.ordered_date is NULL OR
325 l_header_rec.ordered_date = FND_API.G_MISS_DATE) AND
326 p_operation = 'CREATE' THEN
327 l_header_rec.ordered_date := sysdate;
328 END IF;
329 l_header_rec.marketing_source_code_id := p_qte_rec.marketing_source_code_id;
330
331 -- hyang new okc
332 l_header_rec.Customer_Signature := p_qte_rec.Customer_Name_And_Title;
333 l_header_rec.Customer_Signature_date := p_qte_rec.Customer_Signature_Date;
334 l_header_rec.Supplier_Signature := p_qte_rec.Supplier_Name_And_Title;
335 l_header_rec.Supplier_Signature_date := p_qte_rec.Supplier_Signature_Date;
336 l_header_rec.Contract_Template_Id := p_qte_rec.Contract_Template_ID;
337 l_header_rec.Contract_Source_Doc_Type_Code := 'QUOTE';
338 l_header_rec.Contract_Source_Document_Id := p_qte_rec.quote_header_id;
339
340 IF aso_debug_pub.g_debug_flag = 'Y' THEN
341 aso_debug_pub.add('Customer_Signature: '||l_header_rec.Customer_Signature,1,'Y');
342 aso_debug_pub.add('Customer_Signature_date: '||l_header_rec.Customer_Signature_date, 1, 'Y');
343 aso_debug_pub.add('Supplier_Signature: '||l_header_rec.Supplier_Signature, 1, 'Y');
344 aso_debug_pub.add('Supplier_Signature_date: '||l_header_rec.Supplier_Signature_date, 1, 'Y');
345 aso_debug_pub.add('Contract_Template_Id: '||l_header_rec.Contract_Template_Id, 1, 'Y');
346 aso_debug_pub.add('Contract_Source_Doc_Type_Code: '||l_header_rec.Contract_Source_Doc_Type_Code, 1, 'Y');
347 aso_debug_pub.add('Contract_Source_Document_Id: '||l_header_rec.Contract_Source_Document_Id, 1, 'Y');
348 END IF;
349
350 -- end of hyang new okc
351
352
353 --conversion rate depends on the profile value
354
355 l_header_rec.conversion_rate_date := p_qte_rec.exchange_rate_date ;
356 l_header_rec.conversion_type_code := p_qte_rec.exchange_type_code ;
357
358 IF aso_debug_pub.g_debug_flag = 'Y' THEN
359 aso_debug_pub.add('before functional currency'||p_qte_rec.currency_code,1,'N');
360 aso_debug_pub.add('set of books id prof: '||l_set_of_books_id, 1, 'N');
361 aso_debug_pub.add('conversion type prof: '||l_conversion_type, 1, 'N');
362 END IF;
363
364 -- vtariker: 3057860: overriding profile value if value is passed in hdr
365 IF l_header_rec.conversion_type_code IS NOT NULL OR
366 l_header_rec.conversion_type_code <> FND_API.G_MISS_CHAR THEN
367 l_conversion_type := l_header_rec.conversion_type_code;
368 END IF;
369 -- vtariker: 3057860
370
371 IF p_qte_rec.currency_code is not NULL
372 AND p_qte_rec.currency_code <> FND_API.G_MISS_CHAR THEN
373
374 IF (l_set_of_books_id IS NOT NULL) THEN
375
376 OPEN C_Functional_Currency(l_set_of_books_id);
377 FETCH C_Functional_Currency INTO l_functional_currency;
378 CLOSE C_Functional_Currency;
379
380 IF (l_functional_currency <> p_qte_rec.currency_code) THEN
381
382 IF (l_conversion_type is NULL) THEN
383 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
384 fnd_message.set_name('ASO', 'ASO_QTE_MISSING_CONV_TYPE');
385 FND_MSG_PUB.Add;
386 END IF;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389
390 IF l_conversion_type <> 'USER' THEN
391 l_header_rec.conversion_rate := FND_API.G_MISS_NUM;
392 l_header_rec.conversion_rate_date := FND_API.G_MISS_DATE;
393 ELSE
394 IF (p_qte_rec.exchange_rate is NULL OR
395 p_qte_rec.exchange_rate = FND_API.G_MISS_NUM) THEN
396 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
397 fnd_message.set_name('ASO', 'ASO_QTE_MISSING_CONV_RATE');
398 fnd_message.set_token('CURRENCY_CODE', p_qte_rec.currency_code, FALSE);
399 FND_MSG_PUB.Add;
400 END IF;
401 RAISE FND_API.G_EXC_ERROR;
402 END IF;
403 l_header_rec.conversion_rate := p_qte_rec.exchange_rate;
404 l_header_rec.conversion_rate_date := l_sysdate;
405 END IF;
406
407 l_header_rec.conversion_type_code := l_conversion_type;
408
409 -- update in quote tables if needed
410 IF upper(p_qte_rec.quote_source_code) = 'ORDER CAPTURE QUOTES' THEN
411 UPDATE ASO_QUOTE_HEADERS_ALL
412 SET Exchange_Type_Code = l_conversion_type,
413 Exchange_Rate_Date = l_sysdate,
414 Exchange_rate = p_qte_rec.exchange_rate
415 ,last_update_date = l_sysdate
416 ,last_updated_by = l_g_user_id
417 ,last_update_login = l_g_login_id
418
419 WHERE Quote_header_Id = p_qte_rec.quote_header_id;
420 END IF; -- update quote
421 END IF;
422
423 END IF; -- conversion
424 END IF; -- currency code not null
425
426 IF aso_debug_pub.g_debug_flag = 'Y' THEN
427 aso_debug_pub.add('l_header_rec.conversion_rate: '||l_header_rec.conversion_rate,1,'N');
428 aso_debug_pub.add('l_header_rec.conversion_rate_date: '||l_header_rec.conversion_rate_date,1,'N');
429 aso_debug_pub.add('l_header_rec.conversion_type_code: '||l_header_rec.conversion_type_code,1,'N');
430 aso_debug_pub.add('before customer account ', 1, 'Y' );
431 END IF;
432
433 IF p_qte_rec.cust_account_id is not NULL
434 AND p_qte_rec.cust_account_id <> FND_API.G_MISS_NUM THEN
435 l_header_rec.sold_to_org_id := p_qte_rec.cust_account_id;
436 l_cust_account_id := p_qte_rec.cust_account_id;
437 IF aso_debug_pub.g_debug_flag = 'Y' THEN
438 aso_debug_pub.add('validating cust account ' || l_cust_account_id || 'for party' || p_qte_rec.party_id, 1, 'N');
439 END IF;
440 ASO_PARTY_INT.Validate_CustAccount (
441 p_init_msg_list => FND_API.G_FALSE,
442 p_party_id => p_qte_rec.party_id,
443 p_cust_account_id=> l_cust_account_id,
444 x_return_status => l_return_status,
445 x_msg_count => l_msg_count,
446 x_msg_data => l_msg_data);
447 IF aso_debug_pub.g_debug_flag = 'Y' THEN
448 aso_debug_pub.add(' done validating customer.return status = ' || l_return_status ,1, 'Y');
449 END IF;
450 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
451 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
452 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
453 FND_MESSAGE.Set_Token('COLUMN', 'CUST_ACCOUNT_ID', FALSE);
454 FND_MSG_PUB.ADD;
455 END IF;
456 raise FND_API.G_EXC_ERROR;
457 END IF;
458 ELSE
459 IF p_operation = 'CREATE' THEN
460 IF p_qte_rec.party_id is not NULL
461 AND p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
462 IF aso_debug_pub.g_debug_flag = 'Y' THEN
463 aso_debug_pub.add('deriving cust account id:p_qte_rec.party_id: '||p_qte_rec.party_id, 1, 'N');
464 END IF;
465 ASO_CHECK_TCA_PVT.Customer_Account(
466 p_api_version => 1.0,
467 p_Party_Id => p_qte_rec.party_id,
468 p_calling_api_flag => 0,
469 x_Cust_Acct_Id => l_cust_account_id,
470 x_return_status => l_return_status,
471 x_msg_count => l_msg_count,
472 x_msg_data => l_msg_data);
473
474 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
475 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
476 THEN
477 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
478 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
479 FND_MSG_PUB.ADD;
480 END IF;
481 raise FND_API.G_EXC_ERROR;
482 END IF;
483 IF aso_debug_pub.g_debug_flag = 'Y' THEN
484 aso_debug_pub.add('Cust_Accnt_Id in'||l_cust_account_id, 1, 'N');
485 END IF;
486 -- update quote header if account is created
487 IF upper(p_qte_rec.quote_source_code) = 'ORDER CAPTURE QUOTES' THEN
488 UPDATE ASO_QUOTE_HEADERS_ALL
489 SET cust_account_id = l_cust_account_id
490 ,last_update_date = l_sysdate
491 ,last_updated_by = l_g_user_id
492 ,last_update_login = l_g_login_id
493
494
495 WHERE Quote_header_Id = p_qte_rec.quote_header_id;
496 END IF; -- update quote
497
498 l_header_rec.sold_to_org_id := l_cust_account_id;
499 END IF;
500 END IF;
501 IF p_operation = 'UPDATE' THEN
502 IF aso_debug_pub.g_debug_flag = 'Y' THEN
503 aso_debug_pub.add('before query = ' || p_qte_rec.order_id , 1, 'Y');
504 END IF;
505 OPEN get_cust_account_id(p_qte_rec.order_id);
506 FETCH get_cust_account_id INTO l_header_rec.sold_to_org_id;
507 IF aso_debug_pub.g_debug_flag = 'Y' THEN
508 aso_debug_pub.add('after query = ' || l_header_rec.sold_to_org_id, 1, 'Y');
509 END IF;
510 CLOSE get_cust_account_id;
511 IF l_header_rec.sold_to_org_id is NULL THEN
512 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
513 -- fnd_message.set_name('ASO', 'MISSING_CUST_ACCOUNT_ID');
514 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
515 FND_MESSAGE.Set_Token('COLUMN', 'CUST_ACCOUNT_ID', FALSE);
516 FND_MSG_PUB.Add;
517 END IF;
518 RAISE FND_API.G_EXC_ERROR;
519 END IF;
520 END IF;
521 END IF;
522
523 IF p_qte_rec.invoice_to_cust_account_id <> FND_API.G_MISS_NUM AND
524 p_qte_rec.invoice_to_cust_account_id IS NOT NULL AND
525 p_qte_rec.invoice_to_cust_account_id <> l_cust_account_id THEN
526 IF aso_debug_pub.g_debug_flag = 'Y' THEN
527 aso_debug_pub.add('before cust_acct_reltn:p_sold_to_cust_account: '||l_cust_account_id, 1, 'N');
528 aso_debug_pub.add('before cust_acct_reltn:p_related_cust_account: '||p_qte_rec.invoice_to_cust_account_id, 1, 'N');
529 END IF;
530 ASO_CHECK_TCA_PVT.Cust_acct_Relationship (
531 p_api_version => 1.0,
532 p_sold_to_cust_account => l_cust_account_id,
533 p_related_cust_account =>p_qte_rec.invoice_to_cust_account_id,
534 p_relationship_type => 'BILL_TO',
535 x_return_status => l_return_status,
536 x_msg_count => l_msg_count,
537 x_msg_data => l_msg_data
538 );
539 IF aso_debug_pub.g_debug_flag = 'Y' THEN
540 aso_debug_pub.add('after cust_acct_reltn:l_return_status: '||l_return_status, 1, 'N');
541 END IF;
542 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
543 RAISE FND_API.G_EXC_ERROR;
544 END IF;
545
546 END IF;
547
548 IF aso_debug_pub.g_debug_flag = 'Y' THEN
549 aso_debug_pub.add('before org contact', 1, 'N');
550 aso_debug_pub.add('p_qte_rec.org_contact_id: ' || p_qte_rec.org_contact_id,1, 'N');
551 END IF;
552 IF p_qte_rec.party_id is not null and
553 p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
554
555 IF p_qte_rec.org_contact_id is NULL OR
556 p_qte_rec.org_contact_id = FND_API.G_MISS_NUM THEN
557 get_org_contact( p_party_id => p_qte_rec.party_id,
558 x_org_contact => l_org_contact
559 );
560 ELSE
561 l_org_contact := p_qte_rec.org_contact_id;
562 END IF;
563
564 IF aso_debug_pub.g_debug_flag = 'Y' THEN
565 aso_debug_pub.add('l_org_contact: ' || l_org_contact,1, 'N');
566 END IF;
567 IF l_org_contact is not NULL AND
568 l_org_contact <> FND_API.G_MISS_NUM THEN
569 IF aso_debug_pub.g_debug_flag = 'Y' THEN
570 aso_debug_pub.add('deriving org_contact_role:l_header_rec.sold_to_org_id: ' || l_header_rec.sold_to_org_id,1, 'N');
571 END IF;
572 get_org_contact_role(
573 p_Org_Contact_Id => l_org_contact
574 ,p_Cust_account_id => l_header_rec.sold_to_org_id
575 ,x_return_status => l_return_status
576 ,x_party_id => l_org_contact_party_id
577 ,x_cust_account_role_id => l_header_rec.sold_to_contact_id
578 );
579 IF aso_debug_pub.g_debug_flag = 'Y' THEN
580 aso_debug_pub.add('after get org contact. sold_to_contact_id = ' || l_header_rec.sold_to_contact_id ,1, 'N');
581 END IF;
582 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
583 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
584 THEN
585 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_ORG_CON_ACT_CRS');
586 FND_MSG_PUB.ADD;
587 END IF;
588 raise FND_API.G_EXC_ERROR;
589 END IF;
590
591 IF l_header_rec.sold_to_contact_id is NULL OR
592 l_header_rec.sold_to_contact_id = FND_API.G_MISS_NUM THEN
593 IF aso_debug_pub.g_debug_flag = 'Y' THEN
594 aso_debug_pub.add('calling create contact role for org contact:l_org_contact_party_id: '||l_org_contact_party_id,1, 'N');
595 aso_debug_pub.add('calling create contact role for org contact:l_header_rec.sold_to_org_id: '||l_header_rec.sold_to_org_id,1, 'N');
596 END IF;
597 ASO_PARTY_INT.Create_Contact_Role (
598 p_api_version => 1.0
599 ,p_party_id =>l_org_contact_party_id
600 ,p_Cust_account_id => l_header_rec.sold_to_org_id
601 ,x_return_status => l_return_status
602 ,x_msg_count => l_msg_count
603 ,x_msg_data => l_msg_data
604 ,x_cust_account_role_id => l_header_rec.sold_to_contact_id
605 );
606 IF aso_debug_pub.g_debug_flag = 'Y' THEN
607 aso_debug_pub.add('after create contact role. sold_to_contact_id = ' || l_header_rec.sold_to_contact_id ,1, 'Y');
608 END IF;
609 IF L_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
610 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
611 THEN
612 FND_MESSAGE.Set_Name('ASO', 'ASO_ORG_CONTACT');
613 FND_MESSAGE.Set_Token('ID', to_char(p_qte_rec.org_contact_id), FALSE);
614 FND_MSG_PUB.ADD;
615 END IF;
616 raise FND_API.G_EXC_ERROR;
617 END IF;
618 END IF;
619 END IF;
620 END IF;
621
622 IF aso_debug_pub.g_debug_flag = 'Y' THEN
623 aso_debug_pub.add('invoice_to_cust_account = ' || p_qte_rec.invoice_to_cust_account_id,1,'N');
624 END IF;
625 IF p_qte_rec.invoice_to_cust_account_id is not NULL AND
626 p_qte_rec.invoice_to_cust_account_id <> FND_API.G_MISS_NUM THEN
627 l_invoice_cust_account_id := p_qte_rec.invoice_to_cust_account_id;
628 ELSE
629 l_invoice_cust_account_id := l_header_rec.sold_to_org_id;
630 END IF;
631
632 IF aso_debug_pub.g_debug_flag = 'Y' THEN
633 aso_debug_pub.add('l_invoice_cust_acccount_id = ' || l_invoice_cust_account_id,1,'N' );
634 aso_debug_pub.add('before invoice to party site id '|| p_qte_rec.invoice_to_party_site_id, 1, 'Y');
635 END IF;
636 IF p_qte_rec.invoice_to_party_site_id is not NULL
637 AND p_qte_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM THEN
638
639 ASO_CHECK_TCA_PVT.Customer_Account_Site
640 (
641 p_api_version => 1.0
642 ,p_party_site_id => p_qte_rec.invoice_to_party_site_id
643 ,p_acct_site_type => 'BILL_TO'
644 ,p_cust_account_id => l_invoice_cust_account_id
645 ,x_cust_acct_site_id => l_hd_inv_cust_acct_site
646 ,x_return_status => l_return_status
647 ,x_msg_count => l_msg_count
648 ,x_msg_data => l_msg_data
649 ,x_site_use_id => l_header_rec.invoice_to_org_id
650 );
651
652 IF l_header_rec.invoice_to_org_id IS NULL THEN
653 l_header_rec.invoice_to_org_id := FND_API.G_MISS_NUM;
654 END IF;
655 IF aso_debug_pub.g_debug_flag = 'Y' THEN
656 aso_debug_pub.add('site_use_id after deriving invoice = ' || l_header_rec.invoice_to_org_id,1,'Y');
657 aso_debug_pub.add('inv_acct_site after deriving invoice = ' || l_hd_inv_cust_acct_site,1,'Y');
658 END IF;
659 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
660 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
661 THEN
662 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
663 FND_MSG_PUB.ADD;
664 END IF;
665 raise FND_API.G_EXC_ERROR;
666 END IF;
667
668 END IF;
669
670 IF aso_debug_pub.g_debug_flag = 'Y' THEN
671 aso_debug_pub.add(' invoice_to_party = ' || p_qte_rec.invoice_to_party_id,1, 'N' );
672 aso_debug_pub.add(' invoice_to_party_Site = ' || p_qte_rec.invoice_to_party_site_id,1, 'N' );
673 aso_debug_pub.add('before Cust_Acct_Contact_Addr:l_invoice_cust_acccount = ' ||l_invoice_cust_account_id ,1,'Y');
674 END IF;
675
676 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
677 p_api_version => 1.0,
678 p_party_site_id => p_qte_rec.invoice_to_party_site_id,
679 p_role_type => 'BILL_TO',
680 p_cust_account_id => l_invoice_cust_account_id,
681 p_party_id => p_qte_rec.invoice_to_party_id,
682 p_cust_account_site => l_hd_inv_cust_acct_site,
683 x_return_status => l_return_status,
684 x_msg_count => l_msg_count,
685 x_msg_data => l_msg_data,
686 x_cust_account_role_id => l_header_rec.invoice_to_contact_id);
687
688 IF l_header_rec.invoice_to_contact_id IS NULL THEN
689 l_header_rec.invoice_to_contact_id := FND_API.G_MISS_NUM;
690 END IF;
691
692 IF aso_debug_pub.g_debug_flag = 'Y' THEN
693 aso_debug_pub.add('mapq hd: after Cust_Acct_Contact_Addr:l_header_rec.invoice_to_contact_id: '||l_header_rec.invoice_to_contact_id,1,'N');
694 END IF;
695
696 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
697 raise FND_API.G_EXC_ERROR;
698 END IF;
699
700 -- end_cust
701 IF aso_debug_pub.g_debug_flag = 'Y' THEN
702 aso_debug_pub.add('End_Customer_cust_account = ' || p_qte_rec.End_Customer_cust_account_id,1,'N');
703 END IF;
704 IF p_qte_rec.End_Customer_cust_account_id is not NULL AND
705 p_qte_rec.End_Customer_cust_account_id <> FND_API.G_MISS_NUM THEN
706 l_End_cust_account_id := p_qte_rec.End_Customer_cust_account_id;
707 ELSE
708 l_End_cust_account_id := l_header_rec.sold_to_org_id;
709 END IF;
710
711 IF aso_debug_pub.g_debug_flag = 'Y' THEN
712 aso_debug_pub.add('l_End_cust_acccount_id = ' || l_End_cust_account_id,1,'N' );
713 aso_debug_pub.add('before End_Customer party site id '|| p_qte_rec.End_Customer_party_site_id, 1, 'Y');
714 END IF;
715 IF p_qte_rec.End_Customer_party_site_id is not NULL
716 AND p_qte_rec.End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
717
718 ASO_CHECK_TCA_PVT.Customer_Account_Site
719 (
720 p_api_version => 1.0
721 ,p_party_site_id => p_qte_rec.End_Customer_party_site_id
722 ,p_acct_site_type => 'END_USER'
723 ,p_cust_account_id => l_End_cust_account_id
724 ,x_cust_acct_site_id => l_hd_end_cust_acct_site
725 ,x_return_status => l_return_status
726 ,x_msg_count => l_msg_count
727 ,x_msg_data => l_msg_data
728 ,x_site_use_id => l_header_rec.End_Customer_Site_Use_Id
729 );
730
731 IF l_header_rec.End_Customer_Site_Use_Id IS NULL THEN
732 l_header_rec.End_Customer_Site_Use_Id := FND_API.G_MISS_NUM;
733 END IF;
734 IF aso_debug_pub.g_debug_flag = 'Y' THEN
735 aso_debug_pub.add('site_use_id after deriving End_Customer = ' || l_header_rec.End_Customer_Site_Use_Id,1,'Y');
736 aso_debug_pub.add('inv_acct_site after deriving End_Customer = ' || l_hd_end_cust_acct_site,1,'Y');
737 END IF;
738 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
739 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
740 THEN
741 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_END_SITE_AC_CRS');
742 FND_MSG_PUB.ADD;
743 END IF;
744 raise FND_API.G_EXC_ERROR;
745 END IF;
746
747 l_header_rec.End_Customer_id := l_End_cust_account_id;
748
749 END IF;
750
751 IF aso_debug_pub.g_debug_flag = 'Y' THEN
752 aso_debug_pub.add(' End_Customer_party = ' || p_qte_rec.End_Customer_party_id,1, 'N' );
753 aso_debug_pub.add(' invoice_to_party_Site = ' || p_qte_rec.End_Customer_party_site_id,1, 'N' );
754 aso_debug_pub.add('before Cust_Acct_Contact_Addr:l_End_cust_acccount = ' ||l_End_cust_account_id ,1,'Y');
755 END IF;
756
757 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
758 p_api_version => 1.0,
759 p_party_site_id => p_qte_rec.End_Customer_party_site_id,
760 p_role_type => 'END_USER',
761 p_cust_account_id => l_End_cust_account_id,
762 p_party_id => p_qte_rec.End_Customer_party_id,
763 p_cust_account_site => l_hd_end_cust_acct_site,
764 x_return_status => l_return_status,
765 x_msg_count => l_msg_count,
766 x_msg_data => l_msg_data,
767 x_cust_account_role_id => l_header_rec.End_Customer_contact_id);
768
769 IF l_header_rec.End_Customer_contact_id IS NULL THEN
770 l_header_rec.End_Customer_contact_id := FND_API.G_MISS_NUM;
771 END IF;
772
773 IF aso_debug_pub.g_debug_flag = 'Y' THEN
774 aso_debug_pub.add('mapq hd: after Cust_Acct_Contact_Addr:l_header_rec.End_Customer_contact_id: '||l_header_rec.End_Customer_contact_id,1,'N');
775 aso_debug_pub.add('mapq hd: after Cust_Acct_Contact_Addr:l_header_rec.End_Customer_id: '||l_header_rec.End_Customer_id,1,'N');
776 END IF;
777
778 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
779 raise FND_API.G_EXC_ERROR;
780 END IF;
781
782 -- end_cust
783
784 IF aso_debug_pub.g_debug_flag = 'Y' THEN
785 aso_debug_pub.add('beginning of mapping for header shipping ', 1, 'N' );
786 END IF;
787
788 IF p_header_shipment_tbl.count > 0 THEN
789 -- OM takes in only one shipment at the header level
790
791 IF p_header_shipment_tbl(1).ship_to_cust_account_id <> FND_API.G_MISS_NUM AND
792 p_header_shipment_tbl(1).ship_to_cust_account_id IS NOT NULL AND
793 p_header_shipment_tbl(1).ship_to_cust_account_id <> l_cust_account_id THEN
794 IF aso_debug_pub.g_debug_flag = 'Y' THEN
795 aso_debug_pub.add('before cust_acct_relationship ', 1, 'N' );
796 END IF;
797 ASO_CHECK_TCA_PVT.Cust_acct_Relationship (
798 p_api_version => 1.0,
799 p_sold_to_cust_account => l_cust_account_id,
800 p_related_cust_account => p_header_shipment_tbl(1).ship_to_cust_account_id,
801 p_relationship_type => 'SHIP_TO',
802 x_return_status => l_return_status,
803 x_msg_count => l_msg_count,
804 x_msg_data => l_msg_data
805 );
806
807 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
808 RAISE FND_API.G_EXC_ERROR;
809 END IF;
810
811 END IF;
812
813 IF p_header_shipment_tbl(1).ship_to_cust_account_id is not NULL AND
814 p_header_shipment_tbl(1).ship_to_cust_account_id <> FND_API.G_MISS_NUM THEN
815 l_ship_cust_account_id := p_header_shipment_tbl(1).ship_to_cust_account_id;
816 ELSE
817 l_ship_cust_account_id := l_header_rec.sold_to_org_id;
818 END IF;
819
820 IF aso_debug_pub.g_debug_flag = 'Y' THEN
821 aso_debug_pub.add('ship cust acccount = ' || l_ship_cust_account_id ,1, 'N');
822 aso_debug_pub.add('ship to party site = ' || p_header_shipment_tbl(1).ship_to_party_site_id, 1, 'N');
823 END IF;
824 IF p_header_shipment_tbl(1).ship_to_party_site_id is not NULL
825 AND p_header_shipment_tbl(1).ship_to_party_site_id <> FND_API.G_MISS_NUM
826 THEN
827
828 ASO_CHECK_TCA_PVT.Customer_Account_Site
829 (
830 p_api_version => 1.0
831 ,p_party_site_id => p_header_shipment_tbl(1).ship_to_party_site_id
832 ,p_acct_site_type => 'SHIP_TO'
833 ,p_cust_account_id => l_ship_cust_account_id
834 ,x_cust_acct_site_id => l_hd_shp_cust_acct_site
835 ,x_return_status => l_return_status
836 ,x_msg_count => l_msg_count
837 ,x_msg_data => l_msg_data
838 ,x_site_use_id => l_header_rec.ship_to_org_id
839 );
840
841 IF l_header_rec.ship_to_org_id IS NULL THEN
842 l_header_rec.ship_to_org_id := FND_API.G_MISS_NUM;
843 END IF;
844
845 IF aso_debug_pub.g_debug_flag = 'Y' THEN
846 aso_debug_pub.add('ship to org after deriving = ' || l_header_rec.ship_to_org_id, 1, 'N');
847 aso_debug_pub.add('ship acct site after deriving = ' || l_hd_shp_cust_acct_site, 1, 'N');
848 END IF;
849 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
850 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
851 THEN
852 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_SHP_SITE_AC_CRS');
853 FND_MSG_PUB.ADD;
854 END IF;
855 raise FND_API.G_EXC_ERROR;
856 END IF;
857
858 END IF;
859
860 IF aso_debug_pub.g_debug_flag = 'Y' THEN
861 aso_debug_pub.add('beginning of map 2:ship_party_id: ' || p_header_shipment_tbl(1).ship_to_party_id,1,'N');
862 aso_debug_pub.add('before Cust_Acct_Contact_Addr:l_ship_cust_account_id: '||l_ship_cust_account_id,1,'N');
863 aso_debug_pub.add('before Cust_Acct_Contact_Addr:p_header_shipment_tbl(1).ship_to_party_site_id: '||p_header_shipment_tbl(1).ship_to_party_site_id,1,'N');
864 END IF;
865
866 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
867 p_api_version => 1.0,
868 p_party_site_id => p_header_shipment_tbl(1).ship_to_party_site_id,
869 p_role_type => 'SHIP_TO',
870 p_cust_account_id => l_ship_cust_account_id,
871 p_party_id => p_header_shipment_tbl(1).ship_to_party_id,
872 p_cust_account_site => l_hd_shp_cust_acct_site,
873 x_return_status => l_return_status,
874 x_msg_count => l_msg_count,
875 x_msg_data => l_msg_data,
876 x_cust_account_role_id => l_header_rec.ship_to_contact_id);
877
878 IF l_header_rec.ship_to_contact_id IS NULL THEN
879 l_header_rec.ship_to_contact_id := FND_API.G_MISS_NUM;
880 END IF;
881
882 IF aso_debug_pub.g_debug_flag = 'Y' THEN
883 aso_debug_pub.add('mapq hdr: after Cust_Acct_Contact_Addr:l_header_rec.ship_to_contact_id: '||l_header_rec.ship_to_contact_id,1,'N');
884 END IF;
885
886 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
887 raise FND_API.G_EXC_ERROR;
888 END IF;
889
890 END IF; -- shipment tbl count
891 IF aso_debug_pub.g_debug_flag = 'Y' THEN
892 aso_debug_pub.add('after map 2 for header',1,'N');
893 END IF;
894
895
896 IF p_operation = 'CREATE' THEN
897 l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
898 ELSIF p_operation = 'UPDATE' THEN
899 l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
900 l_header_rec.header_id := p_qte_rec.order_id;
901 END IF;
902
903 l_header_rec.order_number := p_qte_rec.order_number;
904 l_header_rec.invoicing_rule_id := p_qte_rec.invoicing_rule_id;
905 l_header_rec.order_type_id := p_qte_rec.order_type_id;
906 l_header_rec.org_id := p_qte_rec.org_id ;
907
908 l_header_rec.minisite_id := p_qte_rec.minisite_id;
909
910 -- employee person id is converted to salesrep
911 -- salesrep id is required for booking an order
912
913
914 -- if an employee person id is passed then it needs to be converted to a
915 -- salesrep id. no error is raised if the conversion is not possible
916
917 IF aso_debug_pub.g_debug_flag = 'Y' THEN
918 aso_debug_pub.add('l_validate_salesrep_prof =' ||l_validate_salesrep_prof,1,'N');
919 aso_debug_pub.add('l_om_defaulting_prof =' ||l_om_defaulting_prof,1,'N');
920 aso_debug_pub.add('p_qte_rec.employee_person_id = '|| p_qte_rec.employee_person_id,1,'N');
921 aso_debug_pub.add('p_qte_rec.resource_id = ' || p_qte_rec.resource_id,1,'N');
922 END IF;
923
924 IF (p_qte_rec.employee_person_id IS NOT NULL AND
925 p_qte_rec.employee_person_id <> FND_API.G_MISS_NUM) AND
926 (p_qte_rec.resource_id IS NULL OR
927 p_qte_rec.resource_id = FND_API.G_MISS_NUM) THEN
928
929 l_header_rec.salesrep_id := ASO_ORDER_INT.Salesrep_id(p_qte_rec.employee_person_id);
930 ELSE
931 IF (p_qte_rec.resource_id IS NOT NULL AND
932 p_qte_rec.resource_id <> FND_API.G_MISS_NUM) THEN
933
934 OPEN salesrep(p_qte_rec.resource_id);
935 FETCH salesrep into l_header_rec.salesrep_id;
936 CLOSE salesrep;
937 END IF;
938 END IF;
939
940 -- if the salesrep id is null and the validate flag is set to 'N' then
941 -- the sales rep id is defaulted. No warning is given if the salesrep is not
942 -- the same as the salesrep id
943 IF aso_debug_pub.g_debug_flag = 'Y' THEN
944 aso_debug_pub.add('salesrep id =' ||l_header_rec.salesrep_id,1,'N');
945 aso_debug_pub.add('default salesrep = '|| l_default_person_id_prof,1,'N');
946 aso_debug_pub.add('p_operation = '|| p_operation,1,'N');
947 END IF;
948
949 IF (l_header_rec.salesrep_id IS NULL OR
950 l_header_rec.salesrep_id = FND_API.G_MISS_NUM) AND
951 p_operation = 'UPDATE' THEN
952
953 l_header_rec.salesrep_id := FND_API.G_MISS_NUM;
954
955 ELSE
956
957 IF (l_header_rec.salesrep_id IS NULL OR
958 l_header_rec.salesrep_id = FND_API.G_MISS_NUM) AND
959 l_om_defaulting_prof = 'N' THEN
960
961 IF l_validate_salesrep_prof = 'N' THEN
962
963 OPEN C_Get_Srep_From_Snumber(l_default_person_id_prof);
964 FETCH C_Get_Srep_From_Snumber INTO l_header_rec.salesrep_id;
965 CLOSE C_Get_Srep_From_Snumber;
966
967 --l_header_rec.salesrep_id := l_default_person_id_prof;
968
969 ELSE
970 IF l_validate_salesrep_prof = 'Y' THEN
971 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
972 FND_MESSAGE.Set_Name('ASO', 'ASO_SALESREP');
973 FND_MSG_PUB.Add;
974 END IF;
975 END IF;
976 END IF;
977 END IF;
978
979 IF aso_debug_pub.g_debug_flag = 'Y' THEN
980 aso_debug_pub.add('salesrep id =' ||l_header_rec.salesrep_id,1,'N');
981 END IF;
982
983 -- if the salesrep is still null then an error is raised.
984
985 IF (l_header_rec.salesrep_id IS NULL OR
986 l_header_rec.salesrep_id = FND_API.G_MISS_NUM) AND
987 l_om_defaulting_prof = 'N' AND
988 l_validate_salesrep_prof = 'N' THEN
989 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
990
991 -- Created new message to display the error message more appropriately - Girish Bug 4654938
992 -- FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_PROFILE_VALUE');
993 -- FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_PERSON_ID', FALSE);
994 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_DEFAULT_VALUE');
995 FND_MESSAGE.Set_Token('PROFILE', 'ASO_DEFAULT_SALESREP', TRUE);
996
997 FND_MSG_PUB.Add;
998 END IF;
999 ELSE
1000 IF l_om_defaulting_prof = 'Y' AND
1001 (l_header_rec.salesrep_id IS NULL OR
1002 l_header_rec.salesrep_id = FND_API.G_MISS_NUM) THEN
1003 l_header_rec.salesrep_id := FND_API.G_MISS_NUM;
1004 END IF;
1005 END IF;
1006
1007 END IF; -- p_operation = UPDATE;
1008
1009 l_header_rec.price_list_id := p_qte_rec.price_list_id;
1010 l_header_rec.pricing_date := p_qte_rec.price_frozen_date;
1011 l_header_rec.sales_channel_code := p_qte_rec.sales_channel_code;
1012
1013 l_header_rec.transactional_curr_code := p_qte_rec.currency_code;
1014 l_header_rec.source_document_id := p_qte_rec.quote_header_id;
1015
1016 IF p_operation = 'CREATE' THEN
1017 IF (p_qte_rec.original_system_reference IS NOT NULL OR
1018 p_qte_rec.original_system_reference <> FND_API.G_MISS_CHAR) THEN
1019 l_header_rec.orig_sys_document_ref := p_qte_rec.original_system_reference;
1020 ELSE
1021 IF (p_qte_rec.quote_header_id IS NULL OR
1022 p_qte_rec.quote_header_id = FND_API.G_MISS_NUM) THEN
1023 l_quote_number := NULL;
1024 l_version_number := NULL;
1025 ELSE
1026
1027 OPEN C_get_quote_number(p_qte_rec.quote_header_id);
1028 FETCH C_get_quote_number INTO l_quote_number, l_version_number;
1029 CLOSE C_get_quote_number;
1030
1031 IF l_quote_number IS NOT NULL THEN
1032
1033 IF l_version_number IS NOT NULL THEN
1034
1035 l_header_rec.orig_sys_document_ref := to_char(l_quote_number)||':'||to_char(l_version_number);
1036 ELSE
1037 l_header_rec.orig_sys_document_ref := to_char(l_quote_number);
1038
1039 END IF;
1040
1041 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1042 aso_debug_pub.add('l_header_rec.orig_sys_document_ref: '||l_header_rec.orig_sys_document_ref,1,'N');
1043 END IF;
1044
1045 END IF;
1046
1047 END IF;
1048
1049 END IF; -- p_qte_rec.original_system_reference
1050 ELSIF p_operation = 'UPDATE' THEN
1051 l_header_rec.orig_sys_document_ref := p_qte_rec.original_system_reference;
1052 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1053 aso_debug_pub.add('p_operation = UPDATE ** l_header_rec.orig_sys_document_ref: '||l_header_rec.orig_sys_document_ref,1,'N');
1054 END IF;
1055
1056 END IF; -- CREATE
1057 -- default value if null
1058
1059 IF p_qte_rec.quote_source_code is NULL OR
1060 p_qte_rec.quote_source_code = FND_API.G_MISS_CHAR THEN
1061
1062 IF p_operation = 'CREATE' THEN
1063
1064 IF (l_om_defaulting_prof = 'N') THEN
1065 l_quote_source := 'CRM Apps';
1066 ELSE
1067 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1068 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
1069 FND_MESSAGE.Set_Token('COLUMN', 'QUOTE_SOURCE_CODE', FALSE);
1070 FND_MSG_PUB.ADD;
1071 END IF;
1072 END IF;
1073 -- p_qte_rec.quote_source_code := 'CRM Apps';
1074
1075 ELSIF p_operation = 'UPDATE' THEN
1076 l_quote_source := FND_API.G_MISS_CHAR;
1077 END IF;
1078
1079 ELSE
1080 l_quote_source := p_qte_rec.quote_source_code;
1081
1082 END IF;
1083
1084
1085 -- source document type id will determine the source of the document
1086 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1087 aso_debug_pub.add('in here for quote source',1,'N');
1088 END IF;
1089 IF l_quote_source is not NULL AND
1090 l_quote_source <> FND_API.G_MISS_CHAR THEN
1091
1092 OPEN C_order_source(l_quote_source);
1093 FETCH C_order_source INTO l_header_rec.source_document_type_id;
1094 IF (C_order_source%NOTFOUND) THEN
1095 null;
1096 END IF;
1097 CLOSE C_order_source;
1098 END IF;
1099 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1100 aso_debug_pub.add('in here after source code',1,'N');
1101 END IF;
1102 For i in 1..p_header_payment_tbl.count LOOP
1103
1104 IF (NVL(l_om_defaulting_prof, 'N') = 'Y') THEN
1105 IF p_header_payment_tbl(i).payment_term_id IS NULL THEN
1106 l_header_rec.payment_term_id := FND_API.G_MISS_NUM;
1107 ELSE -- 3465720
1108 l_header_rec.payment_term_id := p_header_payment_tbl(i).payment_term_id;
1109 END IF;
1110 ELSE
1111 l_header_rec.payment_term_id := p_header_payment_tbl(i).payment_term_id;
1112 END IF;
1113
1114 -- Bug 7253077
1115 IF p_header_payment_tbl(i).payment_type_code <> 'PO' THEN
1116 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1117 aso_debug_pub.add('p_header_payment_tbl(i).payment_type_code: '||p_header_payment_tbl(i).payment_type_code,1,'N');
1118 END IF;
1119 l_header_rec.payment_type_code := p_header_payment_tbl(i).payment_type_code;
1120 END IF;
1121 -- end bug 7253077
1122
1123 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1124 aso_debug_pub.add('p_header_payment_tbl(i).cust_po_number: '||p_header_payment_tbl(i).cust_po_number,1,'N');
1125 END IF;
1126
1127 l_header_rec.cust_po_number := p_header_payment_tbl(i).cust_po_number;
1128
1129 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1130 aso_debug_pub.add('p_header_payment_tbl(i).payment_type_code: '||p_header_payment_tbl(i).payment_type_code,1,'N');
1131 END IF;
1132 IF p_header_payment_tbl(i).payment_type_code IS NOT NULL THEN
1133 l_header_payment_tbl(pay_count).payment_type_code := p_header_payment_tbl(i).payment_type_code;
1134
1135 l_header_payment_tbl(pay_count).payment_amount := p_header_payment_tbl(i).payment_amount;
1136
1137 IF p_operation = 'CREATE' THEN
1138 l_header_payment_tbl(pay_count).operation := p_operation;
1139 ELSIF p_operation = 'UPDATE' THEN
1140 l_header_payment_tbl(pay_count).operation := p_header_payment_tbl(i).operation_code;
1141 END IF;
1142
1143 l_header_payment_tbl(pay_count).trxn_extension_id := p_header_payment_tbl(i).trxn_extension_id;
1144 l_header_payment_tbl(pay_count).payment_collection_event := 'INVOICE';
1145 l_header_payment_tbl(pay_count).payment_level_code := 'ORDER';
1146
1147 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1148 aso_debug_pub.add('l_header_payment_tbl(pay_count).operation: '||l_header_payment_tbl(pay_count).operation,1,'N');
1149 aso_debug_pub.add('l_header_payment_tbl(pay_count).trxn_extension_id: '||l_header_payment_tbl(pay_count).trxn_extension_id,1,'N');
1150 END IF;
1151
1152 IF p_header_payment_tbl(i).payment_type_code = 'CHECK' THEN
1153 l_header_payment_tbl(pay_count).check_number := p_header_payment_tbl(i).payment_ref_number;
1154 END IF;
1155
1156 END IF; -- payment_type_code is not null
1157
1158 -- bug 5613870
1159 IF p_header_payment_tbl(i).payment_type_code = 'CREDIT_CARD' THEN
1160 l_header_payment_tbl(pay_count).receipt_method_id := fnd_api.g_miss_num;
1161 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1162 aso_debug_pub.add('Passing receipt method id as g miss num' ,1,'N');
1163 END IF;
1164 END IF;
1165
1166 pay_count := pay_count + 1;
1167
1168 END LOOP;
1169
1170 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1171 aso_debug_pub.add('mapped header payment tbl',1,'N');
1172 END IF;
1173
1174
1175 For i in 1..p_header_shipment_tbl.count LOOP
1176 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1177 aso_debug_pub.add('mapped header shipment tbl:shipping_instructions: '|| p_header_shipment_tbl(i).shipping_instructions||'trimmed',1,'N');
1178 END IF;
1179 --bug 1921958
1180 IF (p_header_shipment_tbl(i).shipping_instructions IS NULL) AND
1181 (l_om_defaulting_prof = 'Y') THEN
1182 l_header_rec.shipping_instructions
1183 :=FND_API.G_MISS_CHAR;
1184 ELSE
1185 l_header_rec.shipping_instructions
1186 :=rtrim(p_header_shipment_tbl(i).shipping_instructions);
1187 END IF;
1188
1189 IF (p_header_shipment_tbl(i).packing_instructions IS NULL) AND
1190 (l_om_defaulting_prof = 'Y') THEN
1191 l_header_rec.packing_instructions
1192 :=FND_API.G_MISS_CHAR;
1193 ELSE
1194 l_header_rec.packing_instructions
1195 :=rtrim(p_header_shipment_tbl(i).packing_instructions);
1196 END IF;
1197 --bug 1921958
1198 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1199 aso_debug_pub.add('mapped header shipment tbl:shipping_instructions: '|| l_header_rec.shipping_instructions||'trimmed',1,'N');
1200 aso_debug_pub.add('mapped header shipment tbl:packing_instructions: '|| l_header_rec.packing_instructions||'trimmed',1,'N');
1201 END IF;
1202
1203 IF (NVL(l_om_defaulting_prof, 'N') = 'Y') THEN
1204 IF p_header_shipment_tbl(i).FOB_CODE is null then
1205 l_header_rec.fob_point_code := FND_API.G_MISS_CHAR;
1206 ELSE -- 3465720
1207 l_header_rec.fob_point_code := p_header_shipment_tbl(i).fob_code;
1208 end if;
1209 IF p_header_shipment_tbl(i).FREIGHT_TERMS_CODE is null then
1210 l_header_rec.FREIGHT_TERMS_CODE := FND_API.G_MISS_CHAR;
1211 ELSE -- 3465720
1212 l_header_rec.FREIGHT_TERMS_CODE := p_header_shipment_tbl(i).freight_terms_code;
1213 end if;
1214 IF p_header_shipment_tbl(i).SHIPMENT_PRIORITY_CODE is null then
1215 l_header_rec.SHIPMENT_PRIORITY_CODE := FND_API.G_MISS_CHAR;
1216 ELSE -- 3465720
1217 l_header_rec.SHIPMENT_PRIORITY_CODE := p_header_shipment_tbl(i).shipment_priority_code;
1218 end if;
1219 IF p_header_shipment_tbl(i).ship_method_code is null then
1220 l_header_rec.shipping_method_code := FND_API.G_MISS_CHAR;
1221 ELSE -- 3582285
1222 l_header_rec.shipping_method_code := p_header_shipment_tbl(i).ship_method_code;
1223 end if;
1224
1225 ELSE
1226 l_header_rec.fob_point_code := p_header_shipment_tbl(i).fob_code;
1227 l_header_rec.freight_terms_code
1228 := p_header_shipment_tbl(i).freight_terms_code;
1229 l_header_rec.shipment_priority_code
1230 := p_header_shipment_tbl(i).shipment_priority_code;
1231 l_header_rec.shipping_method_code
1232 := p_header_shipment_tbl(i).ship_method_code;
1233 END IF;
1234
1235 IF p_header_shipment_tbl(i).request_date IS NOT NULL THEN
1236 l_header_rec.request_date := p_header_shipment_tbl(i).request_date;
1237 END IF;
1238
1239 l_header_rec.freight_carrier_code
1240 := p_header_shipment_tbl(i).freight_carrier_code;
1241
1242 l_header_rec.partial_shipments_allowed
1243 :=p_header_shipment_tbl(i).ship_partial_flag;
1244
1245 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1246 aso_debug_pub.add('request date= ' ||l_header_rec.request_date,1,'N');
1247 aso_debug_pub.add('Map_Qte_To_Ord: p_header_shipment_tbl(i).Ship_From_Org_Id: '||p_header_shipment_tbl(i).Ship_From_Org_Id,1,'Y');
1248 END IF;
1249 IF p_header_shipment_tbl(i).Ship_From_Org_Id IS NOT NULL AND
1250 p_header_shipment_tbl(i).Ship_From_Org_Id <> FND_API.G_MISS_NUM THEN
1251
1252 l_header_rec.Ship_From_Org_Id
1253 :=p_header_shipment_tbl(i).Ship_From_Org_Id;
1254 END IF;
1255
1256 -- bug 4916969
1257 IF (p_header_shipment_tbl(i).demand_class_code is not null and p_header_shipment_tbl(i).demand_class_code <> fnd_api.g_miss_char) then
1258 l_header_rec.demand_class_code := p_header_shipment_tbl(i).demand_class_code;
1259
1260 end if;
1261
1262 END LOOP;
1263
1264
1265 For i in 1..p_header_tax_detail_tbl.count LOOP
1266
1267 l_header_rec.tax_exempt_flag := p_header_tax_detail_tbl(i).tax_exempt_flag;
1268 l_header_rec.tax_exempt_number
1269 := p_header_tax_detail_tbl(i).tax_exempt_number;
1270 l_header_rec.tax_exempt_reason_code
1271 := p_header_tax_detail_tbl(i).tax_exempt_reason_code;
1272 l_header_rec.tax_point_code := p_header_tax_detail_tbl(i).tax_code;
1273
1274 END LOOP;
1275
1276 -- reserve quantity
1277 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1278 aso_debug_pub.add('MapQ: l_reservation_lvl_prof: '||l_reservation_lvl_prof,1,'N');
1279 END IF;
1280
1281 IF (l_header_rec.order_type_id is not null AND
1282 l_header_rec.order_type_id <> FND_API.G_MISS_NUM) AND
1283 l_reservation_lvl_prof = 'AUTO_ORDER' THEN
1284 OPEN scheduling_level_cur(l_header_rec.order_type_id);
1285 FETCH scheduling_level_cur INTO l_order_scheduling_level;
1286 CLOSE scheduling_level_cur;
1287 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1288 aso_debug_pub.add('l_header_rec.order_type_id = ' ||
1289 l_header_rec.order_type_id, 1,'N');
1290 END IF;
1291 END IF;
1292
1293 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1294 aso_debug_pub.add('after mapping header tax detail ',1,'Y');
1295 END IF;
1296
1297 map_header_price_attr(
1298 p_header_price_attributes_tbl => p_header_price_attributes_tbl,
1299 p_qte_rec => p_qte_rec,
1300 p_operation => l_header_rec.operation,
1301 x_Header_price_Att_tbl => l_header_price_att_tbl
1302 );
1303 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1304 aso_debug_pub.add('after mapping header price attr:l_header_price_att_tbl.count: '||l_header_price_att_tbl.count,1,'N');
1305 END IF;
1306
1307 map_header_price_adj(
1308 p_header_price_adj_tbl => p_header_price_adj_tbl,
1309 p_qte_rec => p_qte_rec,
1310 p_operation => l_header_rec.operation,
1311 x_Header_adj_tbl => l_header_adj_tbl
1312 );
1313
1314 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1315 aso_debug_pub.add('after header price adjustments:l_header_adj_tbl.count: '||l_header_adj_tbl.count,1,'N');
1316 END IF;
1317
1318 map_header_price_adj_attr(
1319 p_header_price_adj_attr_tbl => p_header_price_adj_attr_tbl,
1320 p_operation => l_header_rec.operation,
1321 x_header_adj_att_tbl => l_header_adj_att_tbl
1322 );
1323
1324 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1325 aso_debug_pub.add('after header price adj attribute:l_header_adj_att_tbl.count: '||l_header_adj_att_tbl.count,1,'N');
1326 END IF;
1327
1328 map_header_price_adj_rltn(
1329 P_Header_Price_Adj_rltship_Tbl => P_Header_Price_Adj_rltship_Tbl,
1330 P_operation => l_header_rec.operation,
1331 x_Header_Adj_Assoc_tbl => l_Header_Adj_Assoc_tbl
1332 );
1333
1334 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1335 aso_debug_pub.add('before header sales credit:l_Header_Adj_Assoc_tbl.count: '||l_Header_Adj_Assoc_tbl.count,1,'N');
1336 END IF;
1337
1338 map_header_sales_credits(
1339 P_header_sales_credit_Tbl => P_header_sales_credit_Tbl,
1340 p_operation => p_operation,
1341 p_qte_rec => p_qte_rec,
1342 p_header_operation => l_header_rec.operation,
1343 x_Header_Scredit_tbl => l_Header_Scredit_tbl
1344 );
1345 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1346 aso_debug_pub.add('after header sales credit:l_Header_Scredit_tbl.count: '||l_Header_Scredit_tbl.count,1,'N');
1347 END IF;
1348
1349 -- mapping quote lines to order lines
1350 -- mapping is done based on the index and not on the line id for quotes
1351 -- the line ids should be used for orders
1352
1353 -- initializing all counts for lines
1354 i := 1;
1355 j := 1;
1356 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1357 aso_debug_pub.add('mapping quote lines',1,'N');
1358 END IF;
1359 FOR j in 1..P_line_Shipment_Tbl.count LOOP
1360 found := FND_API.G_FALSE;
1361 FOR k in 1..p_qte_line_tbl.count LOOP
1362
1363 -- kchervel the quantity check should be done before the loop.
1364
1365 IF p_line_shipment_tbl(j).qte_line_index = k THEN
1366
1367 -- check if the line is satisfied by fulfillment (not required any more 02/09
1368
1369 -- EDU
1370 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1371 aso_debug_pub.add('map_quote_to_order- p_qte_line_tbl(k).Commitment_Id: '||p_qte_line_tbl(k).Commitment_Id, 1, 'N');
1372 aso_debug_pub.add('map_quote_to_order- p_qte_line_tbl(k).Agreement_Id: '||p_qte_line_tbl(k).Agreement_Id,1, 'N');
1373 aso_debug_pub.add('map_quote_to_order- p_qte_line_tbl(k).minisite_id: '||p_qte_line_tbl(k).minisite_id,1, 'N');
1374 aso_debug_pub.add('map_quote_to_order- p_qte_rec.minisite_id: '||p_qte_rec.minisite_id,1, 'N');
1375 END IF;
1376 IF p_qte_line_tbl(k).Commitment_Id IS NOT NULL AND
1377 p_qte_line_tbl(k).Commitment_Id <> FND_API.G_MISS_NUM THEN
1378 l_line_tbl(i).Commitment_Id := p_qte_line_tbl(k).Commitment_Id;
1379 END IF;
1380
1381 IF p_qte_line_tbl(k).Agreement_Id IS NOT NULL AND
1382 p_qte_line_tbl(k).Agreement_Id <> FND_API.G_MISS_NUM THEN
1383 l_line_tbl(i).Agreement_Id := p_qte_line_tbl(k).Agreement_Id;
1384 END IF;
1385 -- EDU
1386
1387 IF p_qte_line_tbl(k).Item_Revision IS NOT NULL AND
1388 p_qte_line_tbl(k).Item_Revision <> FND_API.G_MISS_CHAR THEN
1389 l_line_tbl(i).Item_Revision := p_qte_line_tbl(k).Item_Revision;
1390 END IF;
1391
1392 l_line_tbl(i).minisite_id := p_qte_line_tbl(k).minisite_id;
1393
1394 l_line_tbl(i).accounting_rule_id
1395 := p_qte_line_tbl(k).accounting_rule_id;
1396 l_line_tbl(i).attribute1 := p_qte_line_tbl(k).attribute1;
1397 l_line_tbl(i).attribute10 := p_qte_line_tbl(k).attribute10;
1398 l_line_tbl(i).attribute11 := p_qte_line_tbl(k).attribute11;
1399 l_line_tbl(i).attribute12 := p_qte_line_tbl(k).attribute12;
1400 l_line_tbl(i).attribute13 := p_qte_line_tbl(k).attribute13;
1401 l_line_tbl(i).attribute14 := p_qte_line_tbl(k).attribute14;
1402 l_line_tbl(i).attribute15 := p_qte_line_tbl(k).attribute15;
1403 -- for bug 7560676
1404 l_line_tbl(i).attribute16 := p_qte_line_tbl(k).attribute16;
1405 l_line_tbl(i).attribute17 := p_qte_line_tbl(k).attribute17;
1406 l_line_tbl(i).attribute18 := p_qte_line_tbl(k).attribute18;
1407 l_line_tbl(i).attribute19 := p_qte_line_tbl(k).attribute19;
1408 l_line_tbl(i).attribute20 := p_qte_line_tbl(k).attribute20;
1409
1410
1411 l_line_tbl(i).attribute2 := p_qte_line_tbl(k).attribute2;
1412 l_line_tbl(i).attribute3 := p_qte_line_tbl(k).attribute3;
1413 l_line_tbl(i).attribute4 := p_qte_line_tbl(k).attribute4;
1414 l_line_tbl(i).attribute5 := p_qte_line_tbl(k).attribute5;
1415 l_line_tbl(i).attribute6 := p_qte_line_tbl(k).attribute6;
1416 l_line_tbl(i).attribute7 := p_qte_line_tbl(k).attribute7;
1417 l_line_tbl(i).attribute8 := p_qte_line_tbl(k).attribute8;
1418 l_line_tbl(i).attribute9 := p_qte_line_tbl(k).attribute9;
1419 l_line_tbl(i).context := p_qte_line_tbl(k).attribute_category;
1420 l_line_tbl(i).invoicing_rule_id
1421 := p_qte_line_tbl(k).invoicing_rule_id;
1422 l_line_tbl(i).marketing_source_code_id
1423 := p_qte_line_tbl(k).marketing_source_code_id;
1424 l_line_tbl(i).inventory_item_id := p_qte_line_tbl(k).inventory_item_id;
1425
1426 -- Recurring charge Change
1427 --l_line_tbl(i).charge_periodicity_code := p_qte_line_tbl(k).charge_periodicity_code;
1428
1429 IF p_qte_line_tbl(k).invoice_to_cust_account_id <> FND_API.G_MISS_NUM AND
1430 p_qte_line_tbl(k).invoice_to_cust_account_id IS NOT NULL AND
1431 p_qte_line_tbl(k).invoice_to_cust_account_id <> l_cust_account_id THEN
1432 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1433 aso_debug_pub.add('mapq line: before cust_acct_reltn:p_sold_to_cust_account: '||l_cust_account_id,1,'N');
1434 aso_debug_pub.add('mapq line: before cust_acct_reltn:p_related_cust_account: '||p_qte_line_tbl(k).invoice_to_cust_account_id,1,'N');
1435 END IF;
1436 ASO_CHECK_TCA_PVT.Cust_acct_Relationship (
1437 p_api_version => 1.0,
1438 p_sold_to_cust_account => l_cust_account_id,
1439 p_related_cust_account =>p_qte_line_tbl(k).invoice_to_cust_account_id,
1440 p_relationship_type => 'BILL_TO',
1441 x_return_status => l_return_status,
1442 x_msg_count => l_msg_count,
1443 x_msg_data => l_msg_data
1444 );
1445
1446 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1447 aso_debug_pub.add('mapq line: after cust_acct_reltn:l_return_status: '||l_return_status,1,'N');
1448 END IF;
1449 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1450 RAISE FND_API.G_EXC_ERROR;
1451 END IF;
1452
1453 END IF;
1454
1455 IF p_qte_line_tbl(k).invoice_to_cust_account_id is not NULL AND
1456 p_qte_line_tbl(k).invoice_to_cust_account_id <> FND_API.G_MISS_NUM THEN
1457 l_invoice_cust_account_id := p_qte_line_tbl(k).invoice_to_cust_account_id;
1458 ELSE
1459 l_invoice_cust_account_id := l_header_rec.sold_to_org_id;
1460 END IF;
1461
1462 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1463 aso_debug_pub.add('mapq line: before cust_acct_site:l_invoice_cust_account_id: '||l_invoice_cust_account_id,1,'N');
1464 aso_debug_pub.add('mapq line: before cust_acct_site:p_qte_line_tbl(k).invoice_to_party_site_id: '||p_qte_line_tbl(k).invoice_to_party_site_id,1,'N');
1465 END IF;
1466 IF p_qte_line_tbl(k).invoice_to_party_site_id is not NULL
1467 AND p_qte_line_tbl(k).invoice_to_party_site_id <> FND_API.G_MISS_NUM THEN
1468
1469 ASO_CHECK_TCA_PVT.Customer_Account_Site
1470 (
1471 p_api_version => 1.0
1472 ,p_party_site_id => p_qte_line_tbl(k).invoice_to_party_site_id
1473 ,p_acct_site_type => 'BILL_TO'
1474 ,p_cust_account_id => l_invoice_cust_account_id
1475 ,x_cust_acct_site_id => l_ln_inv_cust_acct_site
1476 ,x_return_status => l_return_status
1477 ,x_msg_count => l_msg_count
1478 ,x_msg_data => l_msg_data
1479 ,x_site_use_id => l_line_tbl(i).invoice_to_org_id
1480 );
1481
1482 IF l_line_tbl(i).invoice_to_org_id IS NULL THEN
1483 l_line_tbl(i).invoice_to_org_id := FND_API.G_MISS_NUM;
1484 END IF;
1485
1486 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1487 aso_debug_pub.add('mapq line: after cust_acct_site:l_line_tbl(i).invoice_to_org_id: '||l_line_tbl(i).invoice_to_org_id,1,'N');
1488 aso_debug_pub.add('mapq line: after cust_acct_site:l_ln_inv_cust_acct_site: '||l_ln_inv_cust_acct_site,1,'N');
1489 END IF;
1490 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1491 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1492 THEN
1493 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
1494 FND_MSG_PUB.ADD;
1495 END IF;
1496 raise FND_API.G_EXC_ERROR;
1497 END IF;
1498
1499 END IF;
1500
1501 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1502 aso_debug_pub.add('mapq line: before Cust_Acct_Contact_Addr:l_invoice_cust_account_id: '||l_invoice_cust_account_id,1,'N');
1503 END IF;
1504
1505 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
1506 p_api_version => 1.0,
1507 p_party_site_id => p_qte_line_tbl(k).invoice_to_party_site_id,
1508 p_role_type => 'BILL_TO',
1509 p_cust_account_id => l_invoice_cust_account_id,
1510 p_party_id => p_qte_line_tbl(k).invoice_to_party_id,
1511 p_cust_account_site => l_ln_inv_cust_acct_site,
1512 x_return_status => l_return_status,
1513 x_msg_count => l_msg_count,
1514 x_msg_data => l_msg_data,
1515 x_cust_account_role_id => l_line_tbl(i).invoice_to_contact_id);
1516
1517 IF l_line_tbl(i).invoice_to_contact_id IS NULL THEN
1518 l_line_tbl(i).invoice_to_contact_id := FND_API.G_MISS_NUM;
1519 END IF;
1520
1521 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1522 aso_debug_pub.add('mapq line: after Cust_Acct_Contact_Addr:l_line_tbl(i).invoice_to_contact_id: '||l_line_tbl(i).invoice_to_contact_id,1,'N');
1523 END IF;
1524
1525 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1526 raise FND_API.G_EXC_ERROR;
1527 END IF;
1528
1529 -- end_cust
1530 IF p_qte_line_tbl(k).End_Customer_cust_account_id is not NULL AND
1531 p_qte_line_tbl(k).End_Customer_cust_account_id <> FND_API.G_MISS_NUM THEN
1532 l_End_cust_account_id := p_qte_line_tbl(k).End_Customer_cust_account_id;
1533 ELSE
1534 l_End_cust_account_id := l_header_rec.sold_to_org_id;
1535 END IF;
1536
1537 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1538 aso_debug_pub.add('mapq line: before cust_acct_site:l_End_cust_account_id: '||l_End_cust_account_id,1,'N');
1539 aso_debug_pub.add('mapq line: before cust_acct_site:p_qte_line_tbl(k).End_Customer_party_site_id: '||p_qte_line_tbl(k).End_Customer_party_site_id,1,'N');
1540 END IF;
1541 IF p_qte_line_tbl(k).End_Customer_party_site_id is not NULL
1542 AND p_qte_line_tbl(k).End_Customer_party_site_id <> FND_API.G_MISS_NUM THEN
1543
1544 ASO_CHECK_TCA_PVT.Customer_Account_Site
1545 (
1546 p_api_version => 1.0
1547 ,p_party_site_id => p_qte_line_tbl(k).End_Customer_party_site_id
1548 ,p_acct_site_type => 'END_USER'
1549 ,p_cust_account_id => l_End_cust_account_id
1550 ,x_cust_acct_site_id => l_ln_end_cust_acct_site
1551 ,x_return_status => l_return_status
1552 ,x_msg_count => l_msg_count
1553 ,x_msg_data => l_msg_data
1554 ,x_site_use_id => l_line_tbl(i).End_Customer_site_use_id
1555 );
1556
1557 IF l_line_tbl(i).End_Customer_site_use_id IS NULL THEN
1558 l_line_tbl(i).End_Customer_site_use_id := FND_API.G_MISS_NUM;
1559 END IF;
1560
1561 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1562 aso_debug_pub.add('mapq line: after cust_acct_site:l_line_tbl(i).End_Customer_site_use_id: '||l_line_tbl(i).End_Customer_site_use_id,1,'N');
1563 aso_debug_pub.add('mapq line: after cust_acct_site:l_ln_end_cust_acct_site: '||l_ln_end_cust_acct_site,1,'N');
1564 END IF;
1565 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1566 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1567 THEN
1568 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_END_SITE_AC_CRS');
1569 FND_MSG_PUB.ADD;
1570 END IF;
1571 raise FND_API.G_EXC_ERROR;
1572 END IF;
1573
1574 l_line_tbl(i).End_Customer_Id := l_End_cust_account_id;
1575
1576 END IF;
1577
1578 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1579 aso_debug_pub.add('mapq line: before Cust_Acct_Contact_Addr:l_End_cust_account_id: '||l_End_cust_account_id,1,'N');
1580 END IF;
1581
1582 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
1583 p_api_version => 1.0,
1584 p_party_site_id => p_qte_line_tbl(k).End_Customer_party_site_id,
1585 p_role_type => 'END_USER',
1586 p_cust_account_id => l_End_cust_account_id,
1587 p_party_id => p_qte_line_tbl(k).End_Customer_party_id,
1588 p_cust_account_site => l_ln_end_cust_acct_site,
1589 x_return_status => l_return_status,
1590 x_msg_count => l_msg_count,
1591 x_msg_data => l_msg_data,
1592 x_cust_account_role_id => l_line_tbl(i).End_Customer_contact_id);
1593
1594 IF l_line_tbl(i).End_Customer_contact_id IS NULL THEN
1595 l_line_tbl(i).End_Customer_contact_id := FND_API.G_MISS_NUM;
1596 END IF;
1597
1598 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1599 aso_debug_pub.add('mapq line: after Cust_Acct_Contact_Addr:l_line_tbl(i).End_Customer_contact_id: '||l_line_tbl(i).End_Customer_contact_id,1,'N');
1600 aso_debug_pub.add('mapq line: after Cust_Acct_Contact_Addr:l_line_tbl(i).End_Customer_id: '||l_line_tbl(i).End_Customer_id,1,'N');
1601 END IF;
1602
1603 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1604 raise FND_API.G_EXC_ERROR;
1605 END IF;
1606 -- end_cust
1607
1608 -- item type code in OC is different from OM and this will be defaulted by OM
1609 -- l_line_tbl(i).item_type_code := p_qte_line_tbl(k).item_type_code;
1610 --l_line_tbl(i).line_type_id := p_qte_line_tbl(k).quote_line_type_code;
1611 -- l_line_tbl(i).calculate_price_flag := 'N';
1612
1613 l_line_tbl(i).line_type_id := p_qte_line_tbl(k).order_line_type_id;
1614
1615 -- this piece of code should be in submit_quote. this is done because OM
1616 -- does its defaulting only if it is g_miss and not if it is null
1617
1618 IF l_line_tbl(i).line_type_id is NULL THEN
1619 l_line_tbl(i).line_type_id := FND_API.G_MISS_NUM;
1620 END IF;
1621
1622 l_line_tbl(i).line_category_code := p_qte_line_tbl(k).line_category_code;
1623 l_line_tbl(i).org_id := l_header_rec.org_id;
1624
1625 IF p_qte_line_tbl(k).priced_price_list_id IS NOT NULL AND
1626 p_qte_line_tbl(k).priced_price_list_id <> FND_API.G_MISS_NUM THEN
1627 l_line_tbl(i).price_list_id := p_qte_line_tbl(k).priced_price_list_id;
1628 ELSE
1629 l_line_tbl(i).price_list_id := p_qte_line_tbl(k).price_list_id;
1630 END IF;
1631
1632 IF l_line_tbl(i).price_list_id IS NULL THEN
1633 l_line_tbl(i).price_list_id := FND_API.G_MISS_NUM;
1634 END IF;
1635
1636 l_line_tbl(i).unit_list_price
1637 := p_qte_line_tbl(k).line_list_price;
1638 l_line_tbl(i).unit_selling_price
1639 := p_qte_line_tbl(k).line_quote_price ;
1640
1641 -- l_line_tbl(i).ship_from_org_id := p_qte_line_tbl(k).organization_id;
1642
1643 -- item identifier is set to internal. OC does not use item numbers so it does
1644 -- not matter what type we use.since we always use mtl_system_items i am using
1645 -- 'INT'
1646
1647 l_line_tbl(i).item_identifier_type := 'INT';
1648 l_line_tbl(i).ordered_item_id := l_line_tbl(i).inventory_item_id;
1649
1650 -- the operation code of the line is the same as shipment for create, delete
1651 -- however, for update it is obtained from line directly.
1652 -- for eg: if there is a payment line added then operation at shipment level
1653 -- will be null however it will be update for the line
1654 IF l_header_rec.operation <> OE_GLOBALS.G_OPR_CREATE THEN
1655 l_line_tbl(i).header_id := p_qte_rec.order_id;
1656 END IF;
1657
1658 IF p_operation = 'CREATE' THEN
1659 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
1660 ELSIF p_line_shipment_tbl(j).operation_code = 'CREATE' THEN
1661 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
1662 ELSIF p_line_shipment_tbl(j).operation_code = 'DELETE' THEN
1663 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_DELETE;
1664 l_line_tbl(i).line_id := p_line_shipment_tbl(j).order_line_id;
1665 ELSIF p_qte_line_tbl(k).operation_code = 'UPDATE' THEN
1666 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;
1667 l_line_tbl(i).line_id := p_line_shipment_tbl(j).order_line_id;
1668 ELSIF p_qte_line_tbl(k).operation_code = 'CREATE' THEN
1669 l_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;
1670 END IF;
1671
1672 FOR l in 1..p_qte_line_dtl_tbl.count LOOP
1673
1674
1675 IF (p_qte_line_dtl_tbl(l).qte_line_index = k) THEN
1676
1677 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1678 aso_debug_pub.add('in quote line details',1,'N');
1679 END IF;
1680 l_line_tbl(i).service_duration:=p_qte_line_dtl_tbl(l).service_duration;
1681 l_line_tbl(i).service_period := p_qte_line_dtl_tbl(l).service_period;
1682 l_line_tbl(i).service_start_date:= p_qte_line_tbl(k).start_date_active;
1683 l_line_tbl(i).service_end_date := p_qte_line_tbl(k).end_date_active;
1684 l_line_tbl(i).service_coterminate_flag
1685 := p_qte_line_dtl_tbl(l).service_coterminate_flag;
1686 l_line_tbl(i).unit_list_percent
1687 := p_qte_line_dtl_tbl(l).service_unit_list_percent;
1688 l_line_tbl(i).unit_selling_percent
1689 := p_qte_line_dtl_tbl(l).service_unit_selling_percent;
1690 l_line_tbl(i).unit_percent_base_price
1691 := p_qte_line_dtl_tbl(l).unit_percent_base_price;
1692 l_line_tbl(i).service_reference_type_code
1693 := p_qte_line_dtl_tbl(l).service_ref_type_code;
1694 l_line_tbl(i).service_reference_line_id
1695 := p_qte_line_dtl_tbl(l).service_ref_line_id;
1696 l_line_tbl(i).service_reference_system_id
1697 := p_qte_line_dtl_tbl(l).service_ref_system_id;
1698 l_line_tbl(i).service_ref_order_number
1699 := p_qte_line_dtl_tbl(l).service_ref_order_number;
1700 l_line_tbl(i).service_ref_line_number
1701 := p_qte_line_dtl_tbl(l).service_ref_line_number;
1702 l_line_tbl(i).service_ref_shipment_number
1703 := p_qte_line_dtl_tbl(l).service_ref_shipment_numb;
1704 l_line_tbl(i).service_ref_option_number
1705 := p_qte_line_dtl_tbl(l).service_ref_option_numb;
1706 IF p_qte_line_dtl_tbl(l).service_ref_type_code = 'QUOTE' THEN
1707 l_line_tbl(i).service_reference_type_code := 'ORDER';
1708
1709 -- line_id should be G_MISS_NUM. bug 1399679
1710 l_line_tbl(i).service_reference_line_id := FND_API.G_MISS_NUM;
1711 l_line_tbl(i).service_line_index
1712 := ASO_ORDER_INT.Service_Index (
1713 p_qte_line_dtl_tbl(l).quote_line_id ,
1714 p_qte_line_dtl_tbl(l).qte_line_index ,
1715 P_Line_Rltship_Tbl,
1716 p_line_shipment_tbl);
1717 END IF;
1718
1719 IF p_qte_line_dtl_tbl(l).service_ref_type_code = 'ORDER' THEN
1720 IF p_qte_line_dtl_tbl(l).service_ref_line_id IS NULL OR
1721 p_qte_line_dtl_tbl(l).service_ref_line_id = FND_API.G_MISS_NUM THEN
1722 l_line_tbl(i).service_line_index := p_qte_line_dtl_tbl(l).service_ref_qte_line_index;
1723 END IF;
1724 END IF;
1725
1726
1727 l_line_tbl(i).reference_header_id
1728 := p_qte_line_dtl_tbl(l).return_ref_header_id;
1729 l_line_tbl(i).reference_line_id
1730 := p_qte_line_dtl_tbl(l).return_ref_line_id;
1731 l_line_tbl(i).reference_type
1732 := p_qte_line_dtl_tbl(l).return_ref_type;
1733
1734 IF (p_qte_line_dtl_tbl(l).return_attribute1 is null or
1735 p_qte_line_dtl_tbl(l).return_attribute1 = FND_API.G_MISS_CHAR) AND
1736 (p_qte_line_dtl_tbl(l).return_ref_header_id is not null AND
1737 p_qte_line_dtl_tbl(l).return_ref_header_id <> FND_API.G_MISS_NUM) Then
1738
1739 l_line_tbl(i).return_attribute1
1740 := p_qte_line_dtl_tbl(l).return_ref_header_id;
1741 ELSE
1742 l_line_tbl(i).return_attribute1
1743 := p_qte_line_dtl_tbl(l).return_attribute1;
1744 END IF;
1745
1746 l_line_tbl(i).return_attribute10
1747 := p_qte_line_dtl_tbl(l).return_attribute10;
1748 l_line_tbl(i).return_attribute11
1749 := p_qte_line_dtl_tbl(l).return_attribute11;
1750 l_line_tbl(i).return_attribute12
1751 := p_qte_line_dtl_tbl(l).return_attribute12;
1752 l_line_tbl(i).return_attribute13
1753 := p_qte_line_dtl_tbl(l).return_attribute13;
1754 l_line_tbl(i).return_attribute14
1755 := p_qte_line_dtl_tbl(l).return_attribute14;
1756 l_line_tbl(i).return_attribute15
1757 := p_qte_line_dtl_tbl(l).return_attribute15;
1758
1759 IF (p_qte_line_dtl_tbl(l).return_attribute2 is null or
1760 p_qte_line_dtl_tbl(l).return_attribute2 = FND_API.G_MISS_CHAR) AND
1761 (p_qte_line_dtl_tbl(l).return_ref_line_id is not null AND
1762 p_qte_line_dtl_tbl(l).return_ref_line_id <> FND_API.G_MISS_NUM) Then
1763
1764 l_line_tbl(i).return_attribute2
1765 := p_qte_line_dtl_tbl(l).return_ref_line_id;
1766 ELSE
1767 l_line_tbl(i).return_attribute2
1768 := p_qte_line_dtl_tbl(l).return_attribute2;
1769 END IF;
1770
1771 l_line_tbl(i).return_attribute3
1772 := p_qte_line_dtl_tbl(l).return_attribute3;
1773 l_line_tbl(i).return_attribute4
1774 := p_qte_line_dtl_tbl(l).return_attribute4;
1775 l_line_tbl(i).return_attribute5
1776 := p_qte_line_dtl_tbl(l).return_attribute5;
1777 l_line_tbl(i).return_attribute6
1778 := p_qte_line_dtl_tbl(l).return_attribute6;
1779 l_line_tbl(i).return_attribute7
1780 := p_qte_line_dtl_tbl(l).return_attribute7;
1781 l_line_tbl(i).return_attribute8
1782 := p_qte_line_dtl_tbl(l).return_attribute8;
1783 l_line_tbl(i).return_attribute9
1784 := p_qte_line_dtl_tbl(l).return_attribute9;
1785
1786 IF upper(p_qte_line_dtl_tbl(l).return_ref_type) = 'SALES ORDER' OR
1787 upper(p_qte_line_dtl_tbl(l).return_ref_type) = 'ORDER' THEN
1788 l_line_tbl(i).return_context
1789 := 'ORDER';
1790 ELSE
1791 l_line_tbl(i).return_context
1792 := p_qte_line_dtl_tbl(l).return_attribute_category;
1793 END IF;
1794
1795 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1796 aso_debug_pub.add('l_ret_reason_code_prof: '||l_ret_reason_code_prof,1,'N');
1797 END IF;
1798
1799 IF p_qte_line_tbl(k).line_category_code = 'RETURN' THEN
1800
1801 -- Default return reason code from profile
1802 IF (p_qte_line_dtl_tbl(l).return_reason_code IS NULL OR
1803 p_qte_line_dtl_tbl(l).return_reason_code = FND_API.G_MISS_CHAR) THEN
1804 IF(l_ret_reason_code_prof IS NULL) THEN
1805 l_return_status := FND_API.G_RET_STS_ERROR;
1806 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1807 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
1808 FND_MESSAGE.Set_Token('COLUMN', 'RETURN_REASON_CODE', FALSE);
1809 FND_MSG_PUB.ADD;
1810 RAISE FND_API.G_EXC_ERROR;
1811 END IF;
1812 ELSE
1813 l_line_tbl(i).return_reason_code := l_ret_reason_code_prof;
1814 END IF;
1815 ELSE
1816 l_line_tbl(i).return_reason_code
1817 := p_qte_line_dtl_tbl(l).return_reason_code;
1818
1819 END IF;
1820
1821 END IF;
1822
1823 -- for configuration fields
1824
1825 l_line_tbl(i).component_code
1826 := p_qte_line_dtl_tbl(l).component_code;
1827 /* FOR BUG 6737851 , added OR condition( p_qte_line_tbl(k).item_type_code = 'ATO' IN THE FOLLOWING IF CLAUSE) */
1828 IF p_qte_line_tbl(k).item_type_code = 'MDL' OR p_qte_line_tbl(k).item_type_code = 'ATO' THEN
1829 l_line_tbl(i).top_model_line_index := i ; -- the index is same as the line index for a model item 1;
1830 l_line_tbl(i).link_to_line_index := null;
1831 l_line_tbl(i).config_header_id
1832 := p_qte_line_dtl_tbl(l).config_header_id;
1833 l_line_tbl(i).config_rev_nbr
1834 := p_qte_line_dtl_tbl(l).config_revision_num;
1835 l_line_tbl(i).configuration_id
1836 := p_qte_line_dtl_tbl(l).config_item_id;
1837 l_line_tbl(i).sort_order
1838 := p_qte_line_dtl_tbl(l).bom_sort_order;
1839
1840 -- Recurring charge Change
1841 l_line_tbl(i).charge_periodicity_code := null;
1842
1843
1844 END IF;
1845
1846 IF p_qte_line_tbl(k).item_type_code = 'CFG' THEN
1847
1848 -- Recurring charge Change
1849 l_line_tbl(i).charge_periodicity_code := p_qte_line_tbl(k).charge_periodicity_code;
1850
1851
1852 l_line_tbl(i).config_header_id
1853 := p_qte_line_dtl_tbl(l).config_header_id;
1854 l_line_tbl(i).config_rev_nbr
1855 := p_qte_line_dtl_tbl(l).config_revision_num;
1856 l_line_tbl(i).configuration_id
1857 := p_qte_line_dtl_tbl(l).config_item_id;
1858 l_line_tbl(i).sort_order
1859 := p_qte_line_dtl_tbl(l).bom_sort_order;
1860
1861 option_item := k;
1862 FOR count_1 in 1..p_Line_Rltship_TBL.count LOOP
1863 FOR count_2 in 1..p_Line_Rltship_TBL.count LOOP
1864 if ( p_Line_Rltship_TBL(count_2).related_qte_line_index = option_item) then
1865 parent := p_Line_Rltship_TBL(count_2).qte_line_index;
1866 EXIT;
1867 end if;
1868 END LOOP;
1869 if parent = option_item then
1870 exit;
1871 else
1872 option_item := parent;
1873 end if;
1874 END LOOP;
1875
1876 -- figure OUT NOCOPY /* file.sql.39 change */ the shipment line index for the corresponding qte line index.
1877 -- this will be the index to the order line.
1878 FOR count_2 in 1..p_line_shipment_tbl.count LOOP
1879 if p_line_shipment_tbl(count_2).qte_line_index = parent THEN
1880 l_line_tbl(i).top_model_line_index := count_2;
1881 exit;
1882 end if;
1883 END LOOP;
1884
1885 option_item := k;
1886 parent := option_item;
1887
1888 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1889 aso_debug_pub.add('p_Line_Rltship_TBL.count is '||p_Line_Rltship_TBL.count,1 ,'N');
1890 END IF;
1891
1892 FOR count_2 in 1..p_Line_Rltship_TBL.count LOOP
1893 if ( p_Line_Rltship_TBL(count_2).related_qte_line_index
1894 = option_item and p_Line_Rltship_TBL(count_2).relationship_type_code = 'CONFIG') then
1895 parent := p_Line_Rltship_TBL(count_2).qte_line_index;
1896 EXIT;
1897 end if;
1898 END LOOP;
1899
1900 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1901 aso_debug_pub.add('parent item is '||parent,1,'N');
1902 END IF;
1903
1904 IF (parent <> option_item) THEN
1905 -- figure OUT NOCOPY /* file.sql.39 change */ the shipment line index for the corresponding qte line index.
1906 -- this will be the index to the order line.
1907 FOR count_2 in 1..p_line_shipment_tbl.count LOOP
1908 if p_line_shipment_tbl(count_2).qte_line_index = parent THEN
1909 l_line_tbl(i).link_to_line_index := count_2;
1910 exit;
1911 end if;
1912 END LOOP;
1913 END IF;
1914
1915 END IF; -- configuration fields
1916
1917 END IF;
1918 END LOOP; -- line details
1919
1920 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1921 aso_debug_pub.add('l_ret_reason_code_prof: '||l_ret_reason_code_prof,1,'N');
1922 END IF;
1923
1924 IF p_qte_line_tbl(k).line_category_code = 'RETURN' THEN
1925 -- Default return reason code from profile
1926 IF (l_line_tbl(i).return_reason_code IS NULL OR
1927 l_line_tbl(i).return_reason_code = FND_API.G_MISS_CHAR) THEN
1928 IF(l_ret_reason_code_prof IS NULL) THEN
1929 l_return_status := FND_API.G_RET_STS_ERROR;
1930 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1931 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
1932 FND_MESSAGE.Set_Token('COLUMN', 'RETURN_REASON_CODE', FALSE);
1933 FND_MSG_PUB.ADD;
1934 RAISE FND_API.G_EXC_ERROR;
1935 END IF;
1936 ELSE
1937 l_line_tbl(i).return_reason_code := l_ret_reason_code_prof;
1938 END IF;
1939
1940 END IF;
1941
1942 END IF;
1943
1944 IF (NVL(l_om_defaulting_prof, 'N') = 'Y') THEN
1945 IF p_line_shipment_tbl(j).fob_code IS NULL THEN
1946 l_line_tbl(i).fob_point_code := FND_API.G_MISS_CHAR;
1947 ELSE -- 3465720
1948 l_line_tbl(i).fob_point_code := p_line_shipment_tbl(j).fob_code;
1949 END IF;
1950 IF p_line_shipment_tbl(j).freight_terms_code IS NULL THEN
1951 l_line_tbl(i).freight_terms_code := FND_API.G_MISS_CHAR;
1952 ELSE -- 3465720
1953 l_line_tbl(i).freight_terms_code := p_line_shipment_tbl(j).freight_terms_code;
1954 END IF;
1955 IF p_line_shipment_tbl(j).shipment_priority_code IS NULL THEN
1956 l_line_tbl(i).shipment_priority_code := FND_API.G_MISS_CHAR;
1957 ELSE -- 3465720
1958 l_line_tbl(i).shipment_priority_code := p_line_shipment_tbl(j).shipment_priority_code;
1959 END IF;
1960 IF p_line_shipment_tbl(j).ship_method_code IS NULL THEN
1961 l_line_tbl(i).shipping_method_code := FND_API.G_MISS_CHAR;
1962 ELSE -- 3582285
1963 l_line_tbl(i).shipping_method_code := p_line_shipment_tbl(j).ship_method_code;
1964 END IF;
1965 ELSE
1966 l_line_tbl(i).fob_point_code := p_line_shipment_tbl(j).fob_code;
1967 l_line_tbl(i).freight_terms_code
1968 := p_line_shipment_tbl(j).freight_terms_code;
1969 l_line_tbl(i).shipment_priority_code
1970 := p_line_shipment_tbl(j).shipment_priority_code;
1971 l_line_tbl(i).shipping_method_code
1972 := p_line_shipment_tbl(j).ship_method_code;
1973 END IF;
1974
1975 l_line_tbl(i).freight_carrier_code
1976 := p_line_shipment_tbl(j).freight_carrier_code;
1977 --bug 1921958
1978 IF (p_line_shipment_tbl(j).shipping_instructions IS NULL) AND
1979 (l_om_defaulting_prof = 'Y') THEN
1980 l_line_tbl(i).shipping_instructions
1981 := FND_API.G_MISS_CHAR;
1982 ELSE
1983 l_line_tbl(i).shipping_instructions
1984 := rtrim(p_line_shipment_tbl(j).shipping_instructions);
1985 END IF;
1986
1987 IF (p_line_shipment_tbl(j).packing_instructions IS NULL) AND
1988 (l_om_defaulting_prof = 'Y') THEN
1989 l_line_tbl(i).packing_instructions
1990 := FND_API.G_MISS_CHAR;
1991 ELSE
1992 l_line_tbl(i).packing_instructions
1993 := rtrim(p_line_shipment_tbl(j).packing_instructions);
1994 END IF;
1995 --bug 1921958
1996 IF p_line_shipment_tbl(j).schedule_ship_date IS NOT NULL THEN
1997 l_line_tbl(i).schedule_ship_date
1998 := p_line_shipment_tbl(j).schedule_ship_date;
1999 END IF;
2000 IF p_line_shipment_tbl(j).request_date IS NOT NULL THEN
2001 l_line_tbl(i).request_date
2002 := p_line_shipment_tbl(j).request_date;
2003 END IF;
2004 -- bug 1783862 hyang
2005 IF p_line_shipment_tbl(j).promise_date IS NOT NULL THEN
2006 l_line_tbl(i).promise_date
2007 := p_line_shipment_tbl(j).promise_date;
2008 END IF;
2009 -- bug 1783862 hyang
2010 l_line_tbl(i).ordered_quantity := p_line_shipment_tbl(j).quantity;
2011 l_line_tbl(i).order_quantity_uom := p_qte_line_tbl(k).uom_code;
2012
2013 -- bug 4916969
2014 IF (p_line_shipment_tbl(j).demand_class_code is not null and p_line_shipment_tbl(j).demand_class_code <> fnd_api.g_miss_char) then
2015 l_line_tbl(i).demand_class_code := p_line_shipment_tbl(j).demand_class_code;
2016 end if;
2017
2018 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2019 aso_debug_pub.add('p_line_shipment_tbl(j).Ship_From_Org_Id: '||p_line_shipment_tbl(j).Ship_From_Org_Id,1,'N');
2020 END IF;
2021
2022 IF p_line_shipment_tbl(j).Ship_From_Org_Id IS NOT NULL AND
2023 p_line_shipment_tbl(j).Ship_From_Org_Id <> FND_API.G_MISS_NUM THEN
2024 l_line_tbl(i).ship_from_org_id := p_line_shipment_tbl(j).Ship_From_Org_Id;
2025 END IF;
2026
2027 IF p_line_shipment_tbl(j).ship_to_cust_account_id <> FND_API.G_MISS_NUM AND
2028 p_line_shipment_tbl(j).ship_to_cust_account_id IS NOT NULL AND
2029 p_line_shipment_tbl(j).ship_to_cust_account_id <> l_cust_account_id THEN
2030 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2031 aso_debug_pub.add('before cust_acct_reltn:p_sold_to_cust_account: '||l_cust_account_id,1,'N');
2032 aso_debug_pub.add('before cust_acct_reltn:p_related_cust_account: '||p_line_shipment_tbl(j).ship_to_cust_account_id,1,'N');
2033 END IF;
2034 ASO_CHECK_TCA_PVT.Cust_acct_Relationship (
2035 p_api_version => 1.0,
2036 p_sold_to_cust_account => l_cust_account_id,
2037 p_related_cust_account =>p_line_shipment_tbl(j).ship_to_cust_account_id,
2038 p_relationship_type => 'SHIP_TO',
2039 x_return_status => l_return_status,
2040 x_msg_count => l_msg_count,
2041 x_msg_data => l_msg_data
2042 );
2043 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2044 aso_debug_pub.add('after cust_acct_reltn:l_return_status: '||l_return_status,1,'N');
2045 END IF;
2046
2047 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2048 RAISE FND_API.G_EXC_ERROR;
2049 END IF;
2050
2051 END IF;
2052
2053 IF p_line_shipment_tbl(j).ship_to_cust_account_id is not NULL AND
2054 p_line_shipment_tbl(j).ship_to_cust_account_id <> FND_API.G_MISS_NUM THEN
2055 l_ship_cust_account_id := p_line_shipment_tbl(j).ship_to_cust_account_id;
2056 ELSE
2057 l_ship_cust_account_id := l_header_rec.sold_to_org_id;
2058 END IF;
2059
2060 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2061 aso_debug_pub.add('mapq line: before cust_acct_site:l_ship_cust_account_id: '||l_ship_cust_account_id,1,'N');
2062 aso_debug_pub.add('p_line_shipment_tbl(j).ship_to_party_site_id: '||p_line_shipment_tbl(j).ship_to_party_site_id,1,'N');
2063 END IF;
2064 IF p_line_shipment_tbl(j).ship_to_party_site_id is not NULL AND
2065 p_line_shipment_tbl(j).ship_to_party_site_id <> FND_API.G_MISS_NUM THEN
2066
2067 ASO_CHECK_TCA_PVT.Customer_Account_Site
2068 (
2069 p_api_version => 1.0
2070 ,p_party_site_id => p_line_shipment_tbl(j).ship_to_party_site_id
2071 ,p_acct_site_type => 'SHIP_TO'
2072 ,p_cust_account_id => l_ship_cust_account_id
2073 ,x_cust_acct_site_id => l_ln_shp_cust_acct_site
2074 ,x_return_status => l_return_status
2075 ,x_msg_count => l_msg_count
2076 ,x_msg_data => l_msg_data
2077 ,x_site_use_id => l_line_tbl(i).ship_to_org_id
2078 );
2079
2080 IF l_line_tbl(i).ship_to_org_id IS NULL THEN
2081 l_line_tbl(i).ship_to_org_id := FND_API.G_MISS_NUM;
2082 END IF;
2083
2084 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2085 aso_debug_pub.add('mapq line: after cust_acct_site:l_line_tbl(i).ship_to_org_id: '||l_line_tbl(i).ship_to_org_id,1,'N');
2086 aso_debug_pub.add('mapq line: after cust_acct_site:l_ln_shp_cust_acct_site: '||l_ln_shp_cust_acct_site,1,'N');
2087 END IF;
2088 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2089 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2090 THEN
2091 FND_MESSAGE.Set_Name('ASO', 'ASO_VALIDATE_INV_SITE_AC_CRS');
2092 FND_MSG_PUB.ADD;
2093 END IF;
2094 raise FND_API.G_EXC_ERROR;
2095 END IF;
2096
2097 END IF;
2098
2099 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2100 aso_debug_pub.add('p_line_shipment_tbl(j).ship_to_party_id: '||p_line_shipment_tbl(j).ship_to_party_id,1,'N');
2101 aso_debug_pub.add('p_line_shipment_tbl(j).ship_to_party_site_id: '||p_line_shipment_tbl(j).ship_to_party_site_id,1,'N');
2102 aso_debug_pub.add('mapq line: before Cust_Acct_Contact_Addr:l_ship_cust_account_id: '||l_ship_cust_account_id,1,'N');
2103 END IF;
2104
2105 ASO_CHECK_TCA_PVT.Cust_Acct_Contact_Addr(
2106 p_api_version => 1.0,
2107 p_party_site_id => p_line_shipment_tbl(j).ship_to_party_site_id,
2108 p_role_type => 'SHIP_TO',
2109 p_cust_account_id => l_ship_cust_account_id,
2110 p_party_id => p_line_shipment_tbl(j).ship_to_party_id,
2111 p_cust_account_site => l_ln_shp_cust_acct_site,
2112 x_return_status => l_return_status,
2113 x_msg_count => l_msg_count,
2114 x_msg_data => l_msg_data,
2115 x_cust_account_role_id => l_line_tbl(i).ship_to_contact_id);
2116
2117 IF l_line_tbl(i).ship_to_contact_id IS NULL THEN
2118 l_line_tbl(i).ship_to_contact_id := FND_API.G_MISS_NUM;
2119 END IF;
2120
2121 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2122 aso_debug_pub.add('mapq line: after Cust_Acct_Contact_Addr:l_line_tbl(i).ship_to_contact_id: '||l_line_tbl(i).ship_to_contact_id,1,'N');
2123 END IF;
2124
2125 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2126 raise FND_API.G_EXC_ERROR;
2127 END IF;
2128
2129 l_line_tbl(i).source_document_id := p_line_shipment_tbl(j).quote_header_id;
2130 l_line_tbl(i).source_document_line_id
2131 := p_line_shipment_tbl(j).shipment_id;
2132 l_line_tbl(i).source_document_type_id
2133 := l_header_rec.source_document_type_id;
2134 l_line_tbl(i).orig_sys_document_ref := l_header_rec.orig_sys_document_ref;
2135
2136 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2137 aso_debug_pub.add('l_line_tbl(i).orig_sys_document_ref: '||l_line_tbl(i).orig_sys_document_ref,1,'N');
2138 END IF;
2139
2140 IF p_qte_line_tbl(k).ui_line_number IS NOT NULL AND
2141 p_qte_line_tbl(k).ui_line_number <> FND_API.G_MISS_CHAR THEN
2142 l_line_tbl(i).orig_sys_line_ref := p_qte_line_tbl(k).ui_line_number;
2143
2144 ELSIF p_qte_line_tbl(k).line_number IS NOT NULL
2145 AND p_qte_line_tbl(k).line_number <> FND_API.G_MISS_NUM THEN
2146
2147 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2148 aso_debug_pub.add('p_qte_line_tbl(k).line_number: '||p_qte_line_tbl(k).line_number,1,'N');
2149 END IF;
2150
2151 l_line_tbl(i).orig_sys_line_ref := to_char(p_qte_line_tbl(k).line_number);
2152
2153 END IF;
2154
2155 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2156 aso_debug_pub.add('l_line_tbl(i).orig_sys_line_ref: '||l_line_tbl(i).orig_sys_line_ref,1,'N');
2157 END IF;
2158
2159 -- must have both the index values for create
2160
2161 l_ln_total_tax_amount := 0;
2162
2163 FOR l in 1..p_line_tax_detail_tbl.count LOOP
2164
2165 IF (p_line_tax_detail_tbl(l).qte_line_index = k AND p_line_tax_detail_tbl(l).shipment_index = j) then
2166
2167 l_line_tbl(i).tax_exempt_flag := p_line_tax_detail_tbl(l).tax_exempt_flag;
2168
2169 /*IF nvl(p_line_tax_detail_tbl(l).tax_exempt_flag,'S') = 'E' THEN clause is created for bug#6781917*/
2170 IF nvl(p_line_tax_detail_tbl(l).tax_exempt_flag,'S') = 'E' THEN
2171
2172 l_line_tbl(i).tax_exempt_number := p_line_tax_detail_tbl(l).tax_exempt_number;
2173 --l_line_tbl(i).tax_exempt_reason_code := p_line_tax_detail_tbl(l).tax_exempt_reason_code;
2174
2175 IF l_line_tbl(i).tax_exempt_reason_code IS NULL THEN
2176 OPEN C_GET_TAX_REASONCODE(p_line_tax_detail_tbl(l).quote_header_id);
2177 FETCH C_GET_TAX_REASONCODE INTO l_line_tbl(i).tax_exempt_reason_code;
2178 CLOSE C_GET_TAX_REASONCODE;
2179 END IF;
2180 END IF;
2181
2182 --l_line_tbl(i).tax_point_code := p_line_tax_detail_tbl(l).tax_group_code;
2183 l_line_tbl(i).tax_date := p_line_tax_detail_tbl(l).tax_date;
2184 l_line_tbl(i).tax_rate := p_line_tax_detail_tbl(l).tax_rate;
2185 --l_line_tbl(i).tax_value := p_line_tax_detail_tbl(l).tax_amount;
2186 --l_line_tbl(i).tax_rate_id := p_line_tax_detail_tbl(l).tax_rate_id;
2187
2188 if nvl(p_line_tax_detail_tbl(l).tax_inclusive_flag, 'N') <> 'Y' then
2189 l_ln_total_tax_amount := l_ln_total_tax_amount + nvl( p_line_tax_detail_tbl(l).tax_amount,0);
2190 end if;
2191
2192 --Pass the tax records in the OM price adjustment table
2193 l_line_adj_tbl_count := l_Line_Adj_tbl.count;
2194
2195 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).price_adjustment_id := FND_API.G_MISS_NUM;
2196 --l_Line_Adj_tbl(l_line_adj_tbl_count + 1).header_id := FND_API.G_MISS_NUM;
2197 --l_Line_Adj_tbl(l_line_adj_tbl_count + 1).line_id := FND_API.G_MISS_NUM;
2198 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).line_index := i;
2199 --l_Line_adj_rec.tax_code := l_tax_rate_code; --This is for 11i
2200 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).tax_rate_id := p_line_tax_detail_tbl(l).tax_rate_id; -- This is for R12
2201
2202 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).operand := p_line_tax_detail_tbl(l).tax_rate;
2203 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).adjusted_amount := p_line_tax_detail_tbl(l).tax_amount;
2204 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).automatic_flag := 'N';
2205 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).list_line_type_code := 'TAX';
2206 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).arithmetic_operator := 'AMT';
2207 l_Line_Adj_tbl(l_line_adj_tbl_count + 1).operation := OE_GLOBALS.g_opr_create;
2208
2209 if aso_debug_pub.g_debug_flag = 'Y' then
2210
2211 aso_debug_pub.add('l_line_tbl('||i||').tax_exempt_flag: '||l_line_tbl(i).tax_exempt_flag,1,'N');
2212 aso_debug_pub.add('l_line_tbl('||i||').tax_exempt_number: '||l_line_tbl(i).tax_exempt_number,1,'N');
2213 aso_debug_pub.add('l_line_tbl('||i||').tax_exempt_reason_code: '||l_line_tbl(i).tax_exempt_reason_code,1,'N');
2214 aso_debug_pub.add('l_line_tbl('||i||').tax_date: ' ||l_line_tbl(i).tax_date,1,'N');
2215 aso_debug_pub.add('l_line_tbl('||i||').tax_rate: ' ||l_line_tbl(i).tax_rate,1,'N');
2216 aso_debug_pub.add('l_line_tbl('||i||').tax_value: ' ||l_line_tbl(i).tax_value,1,'N');
2217
2218 end if;
2219
2220 /*
2221 if aso_debug_pub.g_debug_flag = 'Y' then
2222
2223 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').price_adjustment_id: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).price_adjustment_id,1,'N');
2224 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').line_index: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).line_index,1,'N');
2225 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').tax_rate_id: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).tax_rate_id,1,'N');
2226 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').operand: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).operand,1,'N');
2227 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').adjusted_amount: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).adjusted_amount,1,'N');
2228 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').automatic_flag: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).automatic_flag,1,'N');
2229 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').list_line_type_code: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).list_line_type_code,1,'N');
2230 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').arithmetic_operator: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).arithmetic_operator,1,'N');
2231 aso_debug_pub.add('l_Line_Adj_tbl('||l_line_adj_tbl_count + 1||').operation: '||l_Line_Adj_tbl(l_line_adj_tbl_count + 1).operation,1,'N');
2232
2233 end if;
2234
2235 */
2236 END IF;
2237
2238 END LOOP;
2239
2240 l_line_tbl(i).tax_value := l_ln_total_tax_amount;
2241
2242 if aso_debug_pub.g_debug_flag = 'Y' then
2243
2244 aso_debug_pub.add('l_line_tbl('||i||').tax_exempt_flag: '||l_line_tbl(i).tax_exempt_flag,1,'N');
2245 aso_debug_pub.add('l_line_tbl('||i||').tax_exempt_number: '||l_line_tbl(i).tax_exempt_number,1,'N');
2246 aso_debug_pub.add('l_line_tbl('||i||').tax_exempt_reason_code: '||l_line_tbl(i).tax_exempt_reason_code,1,'N');
2247 aso_debug_pub.add('l_line_tbl('||i||').tax_date: ' ||l_line_tbl(i).tax_date,1,'N');
2248 aso_debug_pub.add('l_line_tbl('||i||').tax_rate: ' ||l_line_tbl(i).tax_rate,1,'N');
2249 aso_debug_pub.add('l_line_tbl('||i||').tax_value: ' ||l_line_tbl(i).tax_value,1,'N');
2250
2251
2252 for p in 1..l_Line_Adj_tbl.count loop
2253
2254 aso_debug_pub.add('l_Line_Adj_tbl('||p||').price_adjustment_id: '||l_Line_Adj_tbl(p).price_adjustment_id,1,'N');
2255 aso_debug_pub.add('l_Line_Adj_tbl('||p||').line_index: '||l_Line_Adj_tbl(p).line_index,1,'N');
2256 aso_debug_pub.add('l_Line_Adj_tbl('||p||').tax_rate_id: '||l_Line_Adj_tbl(p).tax_rate_id,1,'N');
2257 aso_debug_pub.add('l_Line_Adj_tbl('||p||').operand: '||l_Line_Adj_tbl(p).operand,1,'N');
2258 aso_debug_pub.add('l_Line_Adj_tbl('||p||').adjusted_amount: '||l_Line_Adj_tbl(p).adjusted_amount,1,'N');
2259 aso_debug_pub.add('l_Line_Adj_tbl('||p||').automatic_flag: '||l_Line_Adj_tbl(p).automatic_flag,1,'N');
2260 aso_debug_pub.add('l_Line_Adj_tbl('||p||').list_line_type_code: '||l_Line_Adj_tbl(p).list_line_type_code,1,'N');
2261 aso_debug_pub.add('l_Line_Adj_tbl('||p||').arithmetic_operator: '||l_Line_Adj_tbl(p).arithmetic_operator,1,'N');
2262 aso_debug_pub.add('l_Line_Adj_tbl('||p||').operation: '||l_Line_Adj_tbl(p).operation,1,'N');
2263
2264 end loop;
2265
2266 end if;
2267 -- tax
2268
2269 IF (l_line_tbl(i).top_model_line_index is NULL OR
2270 l_line_tbl(i).top_model_line_index = FND_API.G_MISS_NUM) AND
2271 l_reservation_lvl_prof = 'AUTO_ORDER' THEN
2272
2273 -- initialize for each line
2274 l_line_scheduling_level := ' ';
2275 IF l_line_tbl(i).line_type_id is not null AND
2276 l_line_tbl(i).line_type_id <> FND_API.G_MISS_NUM then
2277 OPEN scheduling_level_cur(l_line_tbl(i).line_type_id);
2278 FETCH scheduling_level_cur INTO l_line_scheduling_level;
2279 CLOSE scheduling_level_cur;
2280 END IF;
2281 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2282 aso_debug_pub.add('l_line_tbl(i).line_type_id = ' ||
2283 l_line_tbl(i).line_type_id,1,'N');
2284
2285 aso_debug_pub.add('l_order_scheduling_level = ' ||
2286 l_order_scheduling_level,1,'N');
2287 aso_debug_pub.add('l_line_scheduling_level = ' ||
2288 l_line_scheduling_level,1,'N');
2289 END IF;
2290
2291 -- a value of 'TWO' for scheduling level means that the transaction type
2292 -- doesn't allow reservations.
2293 IF l_order_scheduling_level <> 'TWO' AND
2294 l_line_scheduling_level <> 'TWO' then
2295 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2296 aso_debug_pub.add('setting schedule action to reserve',1,'N');
2297 END IF;
2298 l_line_tbl(i).schedule_action_code
2299 := OE_ORDER_SCH_UTIL.OESCH_ACT_RESERVE;
2300 END IF;
2301 END IF; -- reservation
2302
2303 IF p_calculate_price_flag = FND_API.G_FALSE THEN
2304 l_line_tbl(i).calculate_price_flag := 'N';
2305 l_line_tbl(i).pricing_quantity_uom := l_line_tbl(i).order_quantity_uom;
2306 l_line_tbl(i).pricing_quantity := l_line_tbl(i).ordered_quantity;
2307 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2308 aso_debug_pub.add('l_line_tbl(i).pricing_quantity_uom: '||l_line_tbl(i).pricing_quantity_uom,1,'N');
2309 aso_debug_pub.add('l_line_tbl(i).pricing_quantity: '||l_line_tbl(i).pricing_quantity,1,'N');
2310 END IF;
2311 ELSE
2312 l_line_tbl(i).calculate_price_flag := 'Y';
2313 END IF; -- pricing
2314
2315 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2316 aso_debug_pub.add('before line price att',1,'Y');
2317 END IF;
2318
2319 -- pricing attributes
2320
2321 map_line_price_att(
2322 p_line_price_attributes_tbl => p_line_price_attributes_tbl,
2323 p_line_index => i,
2324 p_qte_line_index => k,
2325 p_operation => l_line_tbl(i).operation,
2326 x_line_price_att_tbl => l_line_price_att_tbl
2327 );
2328
2329 l_line_price_adj_rltship_tbl := P_Line_Price_Adj_rltship_Tbl;
2330
2331 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2332 aso_debug_pub.add('after line price att:l_line_price_att_tbl.count: '||l_line_price_att_tbl.count,1,'N');
2333 aso_debug_pub.add('before map_ln_adj:l_Line_Price_Adj_rltship_Tbl.count: '||l_Line_Price_Adj_rltship_Tbl.count,1,'N');
2334 END IF;
2335
2336 map_line_price_adj(
2337 p_line_price_adj_tbl => p_line_price_adj_tbl,
2338 p_line_price_adj_attr_tbl => p_line_price_adj_attr_tbl,
2339 p_line_index => i,
2340 p_qte_line_index => k,
2341 p_operation => l_line_tbl(i).operation,
2342 x_line_adj_tbl => l_line_adj_tbl,
2343 x_line_adj_att_tbl => l_line_adj_att_tbl,
2344 lx_Line_Price_Adj_rltship_Tbl => l_line_price_adj_rltship_tbl
2345 );
2346
2347 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2348 aso_debug_pub.add('after line price adjustments:l_line_adj_att_tbl.count: '||l_line_adj_att_tbl.count,1,'N');
2349 aso_debug_pub.add('after line price adjustments:l_line_price_adj_rltship_tbl.count: '||l_line_price_adj_rltship_tbl.count,1,'N');
2350 END IF;
2351
2352 map_line_price_adj_rltn(
2353 P_Line_Price_Adj_rltship_Tbl => l_Line_Price_Adj_rltship_Tbl,
2354 p_line_index => i,
2355 p_qte_line_index => k,
2356 p_operation => l_line_tbl(i).operation,
2357 x_Line_Adj_Assoc_tbl => l_Line_Adj_Assoc_tbl
2358 );
2359
2360 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2361 aso_debug_pub.add('before line sales credit:l_Line_Adj_Assoc_tbl.count: '||l_Line_Adj_Assoc_tbl.count,1,'Y');
2362 END IF;
2363
2364 map_line_sales_credit(
2365 P_line_sales_credit_Tbl => P_line_sales_credit_Tbl,
2366 p_line_index => i,
2367 p_qte_line_index => k,
2368 p_line_operation => l_line_tbl(i).operation,
2369 p_operation => p_operation,
2370 x_Line_Scredit_tbl => l_Line_Scredit_tbl
2371 );
2372 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2373 aso_debug_pub.add('after line sales credit:l_Line_Scredit_tbl.count: '||l_Line_Scredit_tbl.count,1,'Y');
2374 END IF;
2375
2376 --Line Payments change
2377
2378 map_line_payments(
2379 P_line_payment_Tbl => P_line_payment_Tbl,
2380 p_line_index => i,
2381 p_qte_line_index => k,
2382 p_line_operation => l_line_tbl(i).operation,
2383 p_operation => p_operation,
2384 x_Line_tbl => l_line_tbl,
2385 x_Line_Payment_tbl => l_line_payment_tbl
2386 );
2387 IF l_line_payment_tbl.count > 0 THEN
2388 l_final_payment_tbl(l_final_payment_tbl.count + 1) := l_line_payment_tbl(1);
2389 END IF;
2390
2391 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2392 aso_debug_pub.add('after line payments:l_line_payment_tbl.count: '||l_line_payment_tbl.count,1,'Y');
2393 aso_debug_pub.add('after line payments:l_final_payment_tbl.count: '||l_final_payment_tbl.count,1,'Y');
2394 END IF;
2395
2396 map_lot_serial(
2397 P_lot_serial_tbl => P_lot_serial_tbl,
2398 p_operation => l_line_tbl(i).operation,
2399 p_line_index => i,
2400 p_qte_line_index => k,
2401 x_lot_serial_tbl => l_lot_serial_tbl
2402 );
2403 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2404 aso_debug_pub.add('after lot serial:l_lot_serial_tbl.count: '||l_lot_serial_tbl.count,1,'Y');
2405 END IF;
2406
2407 found := FND_API.G_TRUE;
2408 END IF;
2409 END LOOP; -- lines
2410
2411 i:= i+1;
2412 END LOOP; -- for shipment
2413
2414 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2415 aso_debug_pub.add('l_final_payment_tbl.count: '|| l_final_payment_tbl.count,1,'Y');
2416 END IF;
2417 IF l_final_payment_tbl.count > 0 THEN
2418 l_line_payment_tbl := l_final_payment_tbl;
2419 END IF;
2420
2421
2422
2423 -- mapping operation code
2424
2425 x_header_rec := l_header_rec ;
2426 x_header_val_rec := l_header_val_rec;
2427 x_Header_Adj_tbl := l_header_adj_tbl;
2428 x_Header_Adj_val_tbl := l_header_adj_val_tbl;
2429 x_Header_price_Att_tbl := l_header_price_att_tbl;
2430 x_Header_Adj_Att_tbl := l_header_adj_att_tbl;
2431 x_Header_Adj_Assoc_tbl := l_header_adj_assoc_tbl;
2432 x_Header_Scredit_tbl := l_header_scredit_tbl;
2433 x_Header_Scredit_val_tbl := l_header_scredit_val_tbl;
2434 x_Header_Payment_tbl := l_header_payment_tbl;
2435 x_line_tbl := l_line_tbl;
2436 x_line_val_tbl := l_line_val_tbl;
2437 x_Line_Adj_tbl := l_line_adj_tbl;
2438 x_Line_Adj_val_tbl := l_line_adj_val_tbl;
2439 x_Line_price_Att_tbl := l_line_price_att_tbl;
2440 x_Line_Adj_Att_tbl := l_Line_Adj_Att_tbl;
2441 x_Line_Adj_Assoc_tbl := l_line_adj_assoc_tbl;
2442 x_Line_Scredit_tbl := l_line_scredit_tbl;
2443 x_Line_Scredit_val_tbl := l_line_scredit_val_tbl;
2444 x_Lot_Serial_tbl := l_lot_serial_tbl;
2445 x_Lot_Serial_val_tbl := l_lot_serial_val_tbl;
2446 x_Line_Payment_tbl := l_line_payment_tbl;
2447
2448 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2449 aso_debug_pub.add('end of map quote to order ',1,'Y');
2450 END IF;
2451
2452 END Map_quote_to_order;
2453
2454
2455 PROCEDURE get_org_contact(
2456 p_party_id IN NUMBER,
2457 x_org_contact OUT NOCOPY /* file.sql.39 change */ number )
2458 IS
2459 CURSOR party_cur IS
2460 SELECT party_type
2461 from
2462 hz_parties
2463 where party_id = p_party_id
2464 and status = 'A';
2465 CURSOR org_contact IS
2466 select a.org_contact_id
2467 from
2468 hz_org_contacts a, hz_relationships b
2469 where b.party_id = p_party_id
2470 and a.party_relationship_id = b.relationship_id
2471 and b.STATUS = 'A' AND trunc(b.START_DATE) <= trunc(sysdate)
2472 AND trunc(nvl(b.END_DATE, sysdate)) >= trunc(sysdate);
2473 --and a.status = 'A'; /* status column in hz_org_contacts is obseleted */
2474 l_party_type varchar2(30);
2475 begin
2476
2477 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2478 aso_debug_pub.add('entering get_org_contact ',1,'N');
2479 aso_debug_pub.add('party_id = ' || p_party_id,1,'N');
2480 END IF;
2481
2482 OPEN party_cur;
2483 FETCH party_cur INTO l_party_type;
2484 CLOSE party_cur;
2485
2486 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2487 aso_debug_pub.add('party_type = '|| l_party_type,1,'N');
2488 END IF;
2489
2490 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2491 OPEN org_contact;
2492 FETCH org_contact INTO x_org_contact;
2493 CLOSE org_contact;
2494 END IF;
2495
2496 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2497 aso_debug_pub.add('org_contact = ' || x_org_contact,1,'N');
2498 END IF;
2499
2500 END;
2501
2502 PROCEDURE get_acct_site_uses
2503 (
2504 p_party_site_id IN NUMBER,
2505 p_acct_site_type IN VARCHAR2,
2506 p_cust_account_id IN NUMBER,
2507 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2508 x_site_use_id OUT NOCOPY /* file.sql.39 change */ number
2509 )
2510 IS
2511 CURSOR party_cur IS
2512 SELECT a.party_type, a.party_id
2513 from
2514 HZ_PARTIES a, HZ_PARTY_SITES b
2515 where
2516 a.status = 'A'
2517 AND b.status = 'A'
2518 and b.party_site_id = p_party_site_id
2519 and b.party_id = a.party_id;
2520 CURSOR site_use_cur IS
2521 select a.site_use_id, b.status,a.status
2522 from
2523 hz_cust_site_uses a, hz_cust_acct_sites b
2524 where
2525 b.cust_account_id = p_cust_account_id
2526 and b.party_site_id = p_party_site_id
2527 and a.cust_acct_site_id = b.cust_acct_site_id
2528 and a.site_use_code = p_acct_site_type;
2529
2530 l_party_id number;
2531 cur_party_id number;
2532 l_cust_account_id number;
2533 cust_acct_site_status varchar2(1);
2534 cust_site_use_status varchar2(1);
2535
2536 CURSOR relationship_cur IS
2537 select a.object_id
2538 from
2539 hz_relationships a, hz_cust_accounts b
2540 where a.party_id = l_party_id
2541 and a.object_id = b.party_id
2542 and b.cust_account_id = p_cust_account_id
2543 and a.status = 'A'
2544 and (sysdate between nvl(a.start_date, sysdate)
2545 and nvl(a.end_date, sysdate))
2546 AND b.status = 'A'
2547 AND (sysdate BETWEEN NVL(b.account_activation_date, sysdate) AND
2548 NVL(b.account_termination_date, sysdate));
2549
2550 l_party_type VARCHAR2(30);
2551 l_site_use_id number;
2552 begin
2553
2554 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
2555
2556 x_return_status := FND_API.G_RET_STS_SUCCESS;
2557
2558 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2559 aso_debug_pub.add('site use in get_acct_site_uses = ' || p_acct_site_type,1,'N');
2560 END IF;
2561 OPEN party_cur;
2562 FETCH party_cur INTO l_party_type, l_party_id;
2563 IF (party_cur%NOTFOUND) THEN
2564 l_party_type := NULL;
2565 x_return_status := FND_API.G_RET_STS_ERROR;
2566 END IF;
2567 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2568 aso_debug_pub.add('party_type in get_acct_site_uses = ' || l_party_type,1,'N');
2569 END IF;
2570 CLOSE party_cur;
2571
2572 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2573 OPEN relationship_cur;
2574 FETCH relationship_cur INTO cur_party_id;
2575 IF (relationship_cur%NOTFOUND) THEN
2576 cur_party_id := NULL;
2577 x_return_status := FND_API.G_RET_STS_ERROR;
2578 END IF;
2579 CLOSE relationship_cur;
2580 ELSE
2581 cur_party_id := l_party_id;
2582 END IF;
2583 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2584 aso_debug_pub.add('cur_party_id = ' || cur_party_id,1,'N');
2585 END IF;
2586
2587 OPEN site_use_cur;
2588 FETCH site_use_cur
2589 INTO l_site_use_id, cust_acct_site_status, cust_site_use_status;
2590 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2591 aso_debug_pub.add('site use id = ' || l_site_use_id, 1, 'N');
2592 aso_debug_pub.add('account site status = ' || cust_acct_site_status, 1,'N');
2593 aso_debug_pub.add('account site use status = ' || cust_site_use_status, 1,'N');
2594 END IF;
2595 IF (site_use_cur%NOTFOUND) THEN
2596 l_site_use_id := NULL;
2597 -- x_return_status := FND_API.G_RET_STS_ERROR;
2598 ELSE
2599 IF cust_acct_site_status <> 'A' OR cust_site_use_status <> 'A' THEN
2600 l_site_use_id := NULL;
2601 x_return_status := FND_API.G_RET_STS_ERROR;
2602 IF p_acct_site_type = 'BILL_TO' THEN
2603 FND_MESSAGE.Set_Name('ASO','ASO_INV_CUST_SITE_INACTIVE');
2604 ELSE
2605 FND_MESSAGE.Set_Name('ASO','ASO_SHIP_CUST_SITE_INACTIVE');
2606 END IF;
2607 FND_MSG_PUB.ADD;
2608 END IF;
2609 END IF;
2610 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2611 aso_debug_pub.add('site_use_id in get_acct_site_uses = ' || l_site_use_id,1,'N');
2612 END IF;
2613 CLOSE site_use_cur;
2614 x_site_use_id := l_site_use_id;
2615 END get_acct_site_uses;
2616
2617
2618 PROCEDURE get_cust_acct_roles
2619 (
2620 p_party_id IN NUMBER,
2621 p_party_site_id IN NUMBER,
2622 p_acct_site_type IN VARCHAR2,
2623 p_cust_account_id IN NUMBER,
2624 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2625 x_party_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
2626 x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */ number
2627 )
2628 IS
2629 CURSOR party_cur IS
2630 SELECT party_type
2631 from
2632 HZ_PARTIES
2633 where party_id = p_party_id and status ='A';
2634
2635 CURSOR cust_cur IS
2636 SELECT party_id
2637 from hz_cust_accounts
2638 where cust_account_id = p_cust_account_id
2639 AND status = 'A'
2640 AND (sysdate BETWEEN NVL(account_activation_date, sysdate) AND
2641 NVL(account_termination_date, sysdate));
2642
2643 CURSOR person_relationship_cur IS
2644 SELECT b.party_id
2645 from hz_org_contacts a, hz_relationships b, hz_cust_accounts c
2646 where
2647 --a.status = 'A' and -- status column obseleted
2648 a.party_relationship_id = b.relationship_id
2649 and b.subject_id = p_party_id
2650 and b.object_id = c.party_id
2651 and c.cust_account_id = p_cust_account_id
2652 AND c.status = 'A'
2653 and b.status = 'A'
2654 and (sysdate between nvl(b.start_date, sysdate) and nvl(b.end_date, sysdate))
2655 AND (sysdate BETWEEN NVL(c.account_activation_date, sysdate) AND
2656 NVL(c.account_termination_date, sysdate));
2657
2658 CURSOR org_contact IS
2659 select a.org_contact_id
2660 from
2661 hz_org_contacts a, hz_relationships b
2662 where
2663 b.party_id = p_party_id
2664 /* and a.status = 'A' vtariker: Status for hz_org_contacts is now obsolete */
2665 and b.relationship_id = a.party_relationship_id
2666 and b.status = 'A'
2667 and (sysdate between nvl(b.start_date, sysdate) and nvl(b.end_date, sysdate));
2668
2669 CURSOR cust_role(l_party_id number) IS
2670 select a.cust_account_role_id, a.status
2671 from
2672 hz_cust_account_roles a, hz_cust_acct_sites c
2673 --,hz_role_responsibility d
2674 where
2675 a.role_type = 'CONTACT'
2676 and a.party_id = l_party_id
2677 and a.cust_account_id = p_cust_account_id
2678 and a.cust_acct_site_id = c.cust_acct_site_id
2679 and a.cust_account_id = c.cust_account_id
2680 and c.party_site_id = p_party_site_id
2681 --and a.cust_account_role_id = d.cust_account_role_id
2682 --and d.responsibility_type = p_acct_site_type
2683 and c.status = 'A'
2684 and a.status = 'A';
2685
2686 CURSOR cust_role_exists(l_party_id number) IS
2687 select 'Y'
2688 from
2689 hz_cust_account_roles a, hz_cust_acct_sites c
2690 where
2691 a.role_type = 'CONTACT'
2692 and a.party_id = l_party_id
2693 and a.cust_account_id = p_cust_account_id
2694 and a.cust_acct_site_id = c.cust_acct_site_id
2695 and a.cust_account_id = c.cust_account_id
2696 and c.party_site_id = p_party_site_id
2697 and c.status = 'A';
2698
2699 l_exists VARCHAR2(1) := 'N';
2700 cust_account_role_status varchar2(1);
2701 l_org_contact_id number;
2702 l_party_type VARCHAR2(30);
2703 l_cust_account_role_id number;
2704 l_party_id number;
2705 l_relationship_party_id number := NULL;
2706 l_multiple_flag VARCHAR2(1) := FND_API.G_FALSE;
2707 l_count_relationship number := 0;
2708 l_acct_site_type VARCHAR2(50);
2709 begin
2710
2711 x_return_status := FND_API.G_RET_STS_SUCCESS;
2712 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2713 aso_debug_pub.add('site use in get_cust_account_roles = ' || p_acct_site_type,1,'N');
2714 aso_debug_pub.add('p_party_id = ' || p_party_id,1,'N');
2715 END IF;
2716
2717 IF p_acct_site_type = 'END_USER' THEN
2718 l_acct_site_type := 'SHIP_TO';
2719 END IF;
2720
2721 OPEN party_cur;
2722 FETCH party_cur INTO l_party_type;
2723 IF (party_cur%NOTFOUND) THEN
2724 l_party_type := NULL;
2725 x_return_status := FND_API.G_RET_STS_ERROR;
2726 END IF;
2727 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2728 aso_debug_pub.add('party_type in get_cust_account_roles = ' || l_party_type,1,'N');
2729 END IF;
2730 CLOSE party_cur;
2731
2732 IF l_party_type = 'PERSON' THEN
2733 OPEN cust_cur;
2734 FETCH cust_cur INTO l_party_id;
2735 IF cust_cur%NOTFOUND THEN
2736 l_party_id := NULL;
2737 x_return_status := FND_API.G_RET_STS_ERROR;
2738 END IF;
2739 CLOSE cust_cur;
2740
2741 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2742 aso_debug_pub.add('l_party_id = ' || l_party_id, 1,'N');
2743 END IF;
2744 IF l_party_id IS NOT NULL AND l_party_id = p_party_id THEN
2745 -- p_party_id is owner of account, hence not a contact
2746 x_party_id := FND_API.G_MISS_NUM;
2747 x_cust_account_role_id := FND_API.G_MISS_NUM;
2748 return;
2749 END IF;
2750
2751 OPEN person_relationship_cur;
2752 LOOP
2753 FETCH person_relationship_cur INTO l_relationship_party_id;
2754 EXIT WHEN person_relationship_cur%NOTFOUND OR x_return_status = FND_API.G_RET_STS_ERROR;
2755 l_count_relationship := l_count_relationship + 1;
2756
2757 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2758 aso_debug_pub.add('relationship_party_id = ' || l_relationship_party_id,1,'N');
2759 aso_debug_pub.add('x_return_status = ' || x_return_status,1,'N');
2760 aso_debug_pub.add('opening cust_role cursor',1,'N');
2761 END IF;
2762
2763 OPEN cust_role(l_relationship_party_id);
2764 LOOP
2765 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2766 aso_debug_pub.add('looping in cust_role',1,'N');
2767 END IF;
2768 FETCH cust_role INTO l_cust_account_role_id, cust_account_role_status;
2769 EXIT WHEN cust_role%NOTFOUND OR x_return_status = FND_API.G_RET_STS_ERROR;
2770 IF (cust_role%ROWCOUNT) > 1 THEN
2771 x_return_status := FND_API.G_RET_STS_ERROR;
2772 END IF;
2773 END LOOP;
2774 CLOSE cust_role;
2775
2776 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2777 aso_debug_pub.add(' l_cust_account_role_id = '|| l_cust_account_role_id,1,'N' );
2778 aso_debug_pub.add('rowcount = ' || person_relationship_cur%ROWCOUNT,1,'N');
2779 aso_debug_pub.add('l_multiple_flag = ' || l_multiple_flag,1,'N');
2780 END IF;
2781
2782 IF person_relationship_cur%ROWCOUNT > 1 AND l_multiple_flag = FND_API.G_TRUE
2783 THEN
2784 x_return_status := FND_API.G_RET_STS_ERROR;
2785 END IF;
2786 IF l_cust_account_role_id IS NOT NULL THEN
2787 l_multiple_flag := FND_API.G_TRUE;
2788 END IF;
2789 END LOOP;
2790 IF l_count_relationship = 0 THEN
2791 x_return_status := FND_API.G_RET_STS_ERROR;
2792 END IF;
2793 CLOSE person_relationship_cur;
2794 x_party_id := l_relationship_party_id;
2795 END IF; --end person
2796 IF l_party_type = 'PARTY_RELATIONSHIP' THEN
2797 OPEN org_contact;
2798 FETCH org_contact INTO l_org_contact_id;
2799 IF (org_contact%NOTFOUND) THEN
2800 l_org_contact_id := NULL;
2801 x_return_status := FND_API.G_RET_STS_ERROR;
2802 END IF;
2803 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2804 aso_debug_pub.add('org_contact_id in get_cust_acct_roles = '|| l_org_contact_id,1,'N');
2805 END IF;
2806 CLOSE org_contact;
2807
2808 OPEN cust_role_exists(p_party_id);
2809 FETCH cust_role_exists INTO l_exists;
2810 CLOSE cust_role_exists;
2811
2812 IF NVL(l_exists,'N') <> 'Y' THEN
2813 l_cust_account_role_id := NULL;
2814 -- x_return_status := FND_API.G_RET_STS_ERROR;
2815 ELSE
2816 OPEN cust_role(p_party_id);
2817 FETCH cust_role INTO l_cust_account_role_id, cust_account_role_status;
2818
2819 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2820 aso_debug_pub.add('cust account role id = ' || l_cust_account_role_id, 1, 'N');
2821 aso_debug_pub.add('cust account role status = ' || cust_account_role_status, 1 , 'N');
2822 END IF;
2823
2824 IF (cust_role%NOTFOUND) THEN
2825 l_cust_account_role_id := NULL;
2826 x_return_status := FND_API.G_RET_STS_ERROR;
2827 IF p_acct_site_type = 'BILL_TO' THEN
2828 FND_MESSAGE.Set_Name('ASO','ASO_INV_CUST_ROLE_INACTIVE');
2829 ELSIF p_acct_site_type = 'END_USER' THEN
2830 FND_MESSAGE.Set_Name('ASO','ASO_END_CUST_ROLE_INACTIVE');
2831 ELSIF p_acct_site_type = 'SHIP_TO' THEN
2832 FND_MESSAGE.Set_Name('ASO','ASO_SHP_CUST_ROLE_INACTIVE');
2833 END IF;
2834 FND_MSG_PUB.ADD;
2835 END IF;
2836 CLOSE cust_role;
2837 END IF;
2838 IF aso_debug_pub.g_debug_flag = 'Y' THEN
2839 aso_debug_pub.add('l_cust_account_role_id in get_cust_acct_roles = '|| l_cust_account_role_id,1,'N');
2840 END IF;
2841 x_party_id := p_party_id;
2842
2843 END IF; -- end party relationship
2844
2845 x_cust_account_role_id := l_cust_account_role_id;
2846 END get_cust_acct_roles;
2847
2848 PROCEDURE Get_Cust_Accnt_Id(
2849 P_Qte_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type
2850 := ASO_QUOTE_PUB.G_MISS_QTE_HEADER_REC,
2851 p_Party_Id IN NUMBER,
2852 p_Cust_Acct_Id OUT NOCOPY /* file.sql.39 change */ NUMBER,
2853 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
2854 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
2855 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
2856 IS
2857
2858 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
2859 SELECT cust_account_id
2860 FROM hz_cust_accounts
2861 WHERE party_id = l_Party_Id
2862 and status = 'A'
2863 AND (sysdate BETWEEN NVL(account_activation_date, sysdate) AND
2864 NVL(account_termination_date, sysdate));
2865
2866 CURSOR party_rec IS
2867 select party_type
2868 from hz_parties
2869 where party_id = p_party_id
2870 AND status = 'A';
2871
2872 CURSOR account_user_cur IS
2873 select a.cust_account_id
2874 from hz_cust_accounts a, hz_cust_account_roles b
2875 where a.cust_account_id = b.cust_account_id
2876 and b.party_id = p_party_id
2877 and b.role_type = 'ACCOUNT_USER'
2878 AND b.status = 'A'
2879 AND a.status = 'A'
2880 AND (sysdate BETWEEN NVL(a.account_activation_date, sysdate) AND
2881 NVL(a.account_termination_date, sysdate));
2882
2883 count NUMBER := 0;
2884 x_cust_id NUMBER := NULL;
2885 lx_cust_id NUMBER := NULL;
2886 l_msg_count number;
2887 l_msg_data varchar2(200);
2888 cust_account_id NUMBER;
2889 l_return_status VARCHAR2(1);
2890 l_party_type VARCHAR2(30);
2891
2892
2893 BEGIN
2894
2895 OPEN party_rec;
2896 FETCH party_rec INTO l_party_type;
2897 CLOSE party_rec;
2898
2899 IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
2900
2901 OPEN C_get_cust_id_from_party_id(p_Party_Id);
2902
2903 LOOP
2904 FETCH C_get_cust_id_from_party_id INTO lx_cust_id;
2905 IF C_get_cust_id_from_party_id%ROWCOUNT > 1 THEN
2906 x_return_status := FND_API.G_RET_STS_ERROR;
2907 EXIT;
2908 END IF;
2909 EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
2910 END LOOP;
2911
2912 CLOSE C_get_cust_id_from_party_id;
2913
2914 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2915 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
2916 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
2917 FND_MSG_PUB.ADD;
2918 raise FND_API.G_EXC_ERROR;
2919 END IF;
2920
2921 ELSIF l_party_type = 'PARTY_RELATIONSHIP' THEN
2922 OPEN account_user_cur;
2923 LOOP
2924 FETCH account_user_cur INTO lx_cust_id;
2925 IF account_user_cur%ROWCOUNT > 1 THEN
2926 x_return_status := FND_API.G_RET_STS_ERROR;
2927 EXIT;
2928 END IF;
2929 EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
2930 END LOOP;
2931 CLOSE account_user_cur;
2932 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2933 FND_MESSAGE.Set_Name('ASO', 'ASO_MULTIPLE_CUST_ACCOUNT');
2934 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
2935 FND_MSG_PUB.ADD;
2936 raise FND_API.G_EXC_ERROR;
2937 END IF;
2938 END IF;
2939
2940 IF lx_cust_id IS NULL OR lx_cust_id = FND_API.G_MISS_NUM THEN
2941 IF p_qte_rec.party_id is not NULL
2942 AND p_qte_rec.party_id <> FND_API.G_MISS_NUM THEN
2943 ASO_PARTY_INT.Create_Customer_Account(
2944 p_api_version => 1.0
2945 ,P_Qte_REC => p_qte_rec
2946 ,x_return_status => l_return_status
2947 ,x_msg_count => l_msg_count
2948 ,x_msg_data => l_msg_data
2949 ,x_acct_id => cust_account_id
2950 );
2951
2952 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS THEN
2953 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2954 THEN
2955 FND_MESSAGE.Set_Name('ASO', 'ASO_CUST_ACCOUNT');
2956 FND_MESSAGE.Set_Token('ID', to_char( p_qte_rec.party_id), FALSE);
2957 FND_MSG_PUB.ADD;
2958 END IF;
2959 raise FND_API.G_EXC_ERROR;
2960 END IF;
2961
2962 p_Cust_Acct_Id := cust_account_id;
2963 END IF;
2964
2965 ELSE
2966 p_Cust_Acct_Id := lx_cust_id;
2967
2968 END IF;
2969
2970 END Get_Cust_Accnt_Id;
2971
2972 PROCEDURE get_org_contact_role(
2973 p_Org_Contact_Id IN NUMBER
2974 ,p_Cust_account_id IN NUMBER
2975 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2976 ,x_party_id OUT NOCOPY /* file.sql.39 change */ NUMBER
2977 ,x_cust_account_role_id OUT NOCOPY /* file.sql.39 change */ NUMBER
2978 )
2979 IS
2980 CURSOR C_active_contact (p_party_id NUMBER) IS
2981 SELECT cust_account_role_id, status
2982 FROM hz_cust_account_roles
2983 WHERE party_id = p_party_id
2984 AND cust_account_id = p_cust_account_id
2985 AND role_type ='CONTACT'
2986 AND cust_acct_site_id is null
2987 and status = 'A';
2988
2989 CURSOR C_inactive_contact (p_party_id NUMBER) IS
2990 SELECT cust_account_role_id, status
2991 FROM hz_cust_account_roles
2992 WHERE party_id = p_party_id
2993 AND cust_account_id = p_cust_account_id
2994 AND role_type ='CONTACT'
2995 AND cust_acct_site_id is null
2996 and status <> 'A';
2997
2998 cust_account_role_status varchar2(1);
2999
3000
3001 CURSOR C_party IS
3002 SELECT par.party_id
3003 FROM hz_relationships par,
3004 hz_org_contacts org ,
3005 hz_cust_accounts acc
3006 WHERE org.party_relationship_id = par.relationship_id
3007 AND org.org_contact_id = p_org_contact_id
3008 -- AND org.status = 'A' -- status column obseleted
3009 and par.status = 'A'
3010 and (sysdate between nvl(par.start_date, sysdate) and nvl(par.end_date, sysdate))
3011 AND acc.cust_account_id = p_cust_account_id
3012 AND par.object_id = acc.party_id
3013 AND acc.status = 'A'
3014 AND (sysdate BETWEEN NVL(acc.account_activation_date, sysdate) AND
3015 NVL(acc.account_termination_date, sysdate));
3016
3017
3018 l_party_id NUMBER;
3019 BEGIN
3020
3021 x_return_status := FND_API.G_RET_STS_SUCCESS;
3022
3023
3024 OPEN C_party;
3025 FETCH C_party INTO l_party_id;
3026 IF (C_party%NOTFOUND) THEN
3027 x_return_status := FND_API.G_RET_STS_ERROR;
3028 END IF;
3029 CLOSE C_party;
3030
3031 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3032 aso_debug_pub.add('x_return_status = ' || x_return_status,1,'N');
3033 aso_debug_pub.add('l_party_id is = ' || l_party_id,1,'N');
3034 END IF;
3035
3036 OPEN C_active_contact (l_party_id);
3037 FETCH C_active_contact INTO x_cust_account_role_id, cust_account_role_status;
3038 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3039 aso_debug_pub.add('cust account role id = ' || x_cust_account_role_id, 1, 'N');
3040 aso_debug_pub.add('cust_account_role_status = '|| cust_account_role_status, 1, 'N');
3041 END IF;
3042 IF (C_active_contact%NOTFOUND) THEN
3043 -- this means there are no active contacts
3044 OPEN C_inactive_contact(l_party_id);
3045 FETCH C_inactive_contact INTO x_cust_account_role_id, cust_account_role_status;
3046 IF C_inactive_contact%FOUND THEN
3047 -- this means there are ONLY inactive contacts
3048 x_cust_account_role_id := NULL;
3049 x_return_status := FND_API.G_RET_STS_ERROR;
3050 FND_MESSAGE.Set_Name('ASO','ASO_SOLD_CUST_ROLE_INACTIVE');
3051 FND_MSG_PUB.ADD;
3052 ELSE
3053 -- this means there are no contacts
3054 x_cust_account_role_id := NULL;
3055 END IF;
3056 CLOSE C_inactive_contact;
3057 END IF;
3058 CLOSE C_active_contact;
3059
3060 x_party_id := l_party_id;
3061 IF aso_debug_pub.g_debug_flag = 'Y' THEN
3062 aso_debug_pub.add('cust acct role '||x_cust_account_role_id,1,'N');
3063 END IF;
3064 END get_org_contact_role;
3065
3066
3067 PROCEDURE map_header_price_attr(
3068 p_header_price_attributes_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type,
3069 p_qte_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
3070 p_operation IN VARCHAR2,
3071 x_Header_price_Att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Price_Att_Tbl_Type
3072 )
3073 IS
3074 i NUMBER;
3075 p_att_count NUMBER := 1;
3076 l_header_price_att_tbl OE_Order_PUB.Header_Price_Att_Tbl_Type;
3077 BEGIN
3078 FOR i in 1..p_header_price_attributes_tbl.count LOOP
3079 l_header_price_att_tbl(p_att_count).pricing_context
3080 := p_header_price_attributes_tbl(i).pricing_context;
3081 l_header_price_att_tbl(p_att_count).pricing_attribute1
3082 := p_header_price_attributes_tbl(i).pricing_attribute1;
3083 l_header_price_att_tbl(p_att_count).pricing_attribute2
3084 := p_header_price_attributes_tbl(i).pricing_attribute2;
3085 l_header_price_att_tbl(p_att_count).pricing_attribute3
3086 := p_header_price_attributes_tbl(i).pricing_attribute3;
3087 l_header_price_att_tbl(p_att_count).pricing_attribute4
3088 := p_header_price_attributes_tbl(i).pricing_attribute4;
3089 l_header_price_att_tbl(p_att_count).pricing_attribute5
3090 := p_header_price_attributes_tbl(i).pricing_attribute5;
3091 l_header_price_att_tbl(p_att_count).pricing_attribute6
3092 := p_header_price_attributes_tbl(i).pricing_attribute6;
3093 l_header_price_att_tbl(p_att_count).pricing_attribute7
3094 := p_header_price_attributes_tbl(i).pricing_attribute7;
3095 l_header_price_att_tbl(p_att_count).pricing_attribute8
3096 := p_header_price_attributes_tbl(i).pricing_attribute8;
3097 l_header_price_att_tbl(p_att_count).pricing_attribute9
3098 := p_header_price_attributes_tbl(i).pricing_attribute9;
3099 l_header_price_att_tbl(p_att_count).pricing_attribute10
3100 := p_header_price_attributes_tbl(i).pricing_attribute10;
3101 l_header_price_att_tbl(p_att_count).pricing_attribute11
3102 := p_header_price_attributes_tbl(i).pricing_attribute11;
3103 l_header_price_att_tbl(p_att_count).pricing_attribute12
3104 := p_header_price_attributes_tbl(i).pricing_attribute12;
3105 l_header_price_att_tbl(p_att_count).pricing_attribute13
3106 := p_header_price_attributes_tbl(i).pricing_attribute13;
3107 l_header_price_att_tbl(p_att_count).pricing_attribute14
3108 := p_header_price_attributes_tbl(i).pricing_attribute14;
3109 l_header_price_att_tbl(p_att_count).pricing_attribute15
3110 := p_header_price_attributes_tbl(i).pricing_attribute15;
3111 l_header_price_att_tbl(p_att_count).pricing_attribute16
3112 := p_header_price_attributes_tbl(i).pricing_attribute16;
3113 l_header_price_att_tbl(p_att_count).pricing_attribute17
3114 := p_header_price_attributes_tbl(i).pricing_attribute17;
3115 l_header_price_att_tbl(p_att_count).pricing_attribute18
3116 := p_header_price_attributes_tbl(i).pricing_attribute18;
3117 l_header_price_att_tbl(p_att_count).pricing_attribute19
3118 := p_header_price_attributes_tbl(i).pricing_attribute19;
3119 l_header_price_att_tbl(p_att_count).pricing_attribute20
3120 := p_header_price_attributes_tbl(i).pricing_attribute20;
3121 l_header_price_att_tbl(p_att_count).pricing_attribute21
3122 := p_header_price_attributes_tbl(i).pricing_attribute21;
3123 l_header_price_att_tbl(p_att_count).pricing_attribute22
3124 := p_header_price_attributes_tbl(i).pricing_attribute22;
3125 l_header_price_att_tbl(p_att_count).pricing_attribute23
3126 := p_header_price_attributes_tbl(i).pricing_attribute23;
3127 l_header_price_att_tbl(p_att_count).pricing_attribute24
3128 := p_header_price_attributes_tbl(i).pricing_attribute24;
3129 l_header_price_att_tbl(p_att_count).pricing_attribute25
3130 := p_header_price_attributes_tbl(i).pricing_attribute25;
3131 l_header_price_att_tbl(p_att_count).pricing_attribute26
3132 := p_header_price_attributes_tbl(i).pricing_attribute26;
3133 l_header_price_att_tbl(p_att_count).pricing_attribute27
3134 := p_header_price_attributes_tbl(i).pricing_attribute27;
3135 l_header_price_att_tbl(p_att_count).pricing_attribute28
3136 := p_header_price_attributes_tbl(i).pricing_attribute28;
3137 l_header_price_att_tbl(p_att_count).pricing_attribute29
3138 := p_header_price_attributes_tbl(i).pricing_attribute29;
3139 l_header_price_att_tbl(p_att_count).pricing_attribute30
3140 := p_header_price_attributes_tbl(i).pricing_attribute30;
3141 l_header_price_att_tbl(p_att_count).pricing_attribute31
3142 := p_header_price_attributes_tbl(i).pricing_attribute31;
3143 l_header_price_att_tbl(p_att_count).pricing_attribute32
3144 := p_header_price_attributes_tbl(i).pricing_attribute32;
3145 l_header_price_att_tbl(p_att_count).pricing_attribute33
3146 := p_header_price_attributes_tbl(i).pricing_attribute33;
3147 l_header_price_att_tbl(p_att_count).pricing_attribute34
3148 := p_header_price_attributes_tbl(i).pricing_attribute34;
3149 l_header_price_att_tbl(p_att_count).pricing_attribute35
3150 := p_header_price_attributes_tbl(i).pricing_attribute35;
3151 l_header_price_att_tbl(p_att_count).pricing_attribute36
3152 := p_header_price_attributes_tbl(i).pricing_attribute36;
3153 l_header_price_att_tbl(p_att_count).pricing_attribute37
3154 := p_header_price_attributes_tbl(i).pricing_attribute37;
3155 l_header_price_att_tbl(p_att_count).pricing_attribute38
3156 := p_header_price_attributes_tbl(i).pricing_attribute38;
3157 l_header_price_att_tbl(p_att_count).pricing_attribute39
3158 := p_header_price_attributes_tbl(i).pricing_attribute39;
3159 l_header_price_att_tbl(p_att_count).pricing_attribute40
3160 := p_header_price_attributes_tbl(i).pricing_attribute40;
3161 l_header_price_att_tbl(p_att_count).pricing_attribute41
3162 := p_header_price_attributes_tbl(i).pricing_attribute41;
3163 l_header_price_att_tbl(p_att_count).pricing_attribute42
3164 := p_header_price_attributes_tbl(i).pricing_attribute42;
3165 l_header_price_att_tbl(p_att_count).pricing_attribute43
3166 := p_header_price_attributes_tbl(i).pricing_attribute43;
3167 l_header_price_att_tbl(p_att_count).pricing_attribute44
3168 := p_header_price_attributes_tbl(i).pricing_attribute44;
3169 l_header_price_att_tbl(p_att_count).pricing_attribute45
3170 := p_header_price_attributes_tbl(i).pricing_attribute45;
3171 l_header_price_att_tbl(p_att_count).pricing_attribute46
3172 := p_header_price_attributes_tbl(i).pricing_attribute46;
3173 l_header_price_att_tbl(p_att_count).pricing_attribute47
3174 := p_header_price_attributes_tbl(i).pricing_attribute47;
3175 l_header_price_att_tbl(p_att_count).pricing_attribute48
3176 := p_header_price_attributes_tbl(i).pricing_attribute48;
3177 l_header_price_att_tbl(p_att_count).pricing_attribute49
3178 := p_header_price_attributes_tbl(i).pricing_attribute49;
3179 l_header_price_att_tbl(p_att_count).pricing_attribute50
3180 := p_header_price_attributes_tbl(i).pricing_attribute50;
3181 l_header_price_att_tbl(p_att_count).pricing_attribute51
3182 := p_header_price_attributes_tbl(i).pricing_attribute51;
3183 l_header_price_att_tbl(p_att_count).pricing_attribute52
3184 := p_header_price_attributes_tbl(i).pricing_attribute52;
3185 l_header_price_att_tbl(p_att_count).pricing_attribute53
3186 := p_header_price_attributes_tbl(i).pricing_attribute53;
3187 l_header_price_att_tbl(p_att_count).pricing_attribute54
3188 := p_header_price_attributes_tbl(i).pricing_attribute54;
3189 l_header_price_att_tbl(p_att_count).pricing_attribute55
3190 := p_header_price_attributes_tbl(i).pricing_attribute55;
3191 l_header_price_att_tbl(p_att_count).pricing_attribute56
3192 := p_header_price_attributes_tbl(i).pricing_attribute56;
3193 l_header_price_att_tbl(p_att_count).pricing_attribute57
3194 := p_header_price_attributes_tbl(i).pricing_attribute57;
3195 l_header_price_att_tbl(p_att_count).pricing_attribute58
3196 := p_header_price_attributes_tbl(i).pricing_attribute58;
3197 l_header_price_att_tbl(p_att_count).pricing_attribute59
3198 := p_header_price_attributes_tbl(i).pricing_attribute59;
3199 l_header_price_att_tbl(p_att_count).pricing_attribute60
3200 := p_header_price_attributes_tbl(i).pricing_attribute60;
3201 l_header_price_att_tbl(p_att_count).pricing_attribute61
3202 := p_header_price_attributes_tbl(i).pricing_attribute61;
3203 l_header_price_att_tbl(p_att_count).pricing_attribute62
3204 := p_header_price_attributes_tbl(i).pricing_attribute62;
3205 l_header_price_att_tbl(p_att_count).pricing_attribute63
3206 := p_header_price_attributes_tbl(i).pricing_attribute63;
3207 l_header_price_att_tbl(p_att_count).pricing_attribute64
3208 := p_header_price_attributes_tbl(i).pricing_attribute64;
3209 l_header_price_att_tbl(p_att_count).pricing_attribute65
3210 := p_header_price_attributes_tbl(i).pricing_attribute65;
3211 l_header_price_att_tbl(p_att_count).pricing_attribute66
3212 := p_header_price_attributes_tbl(i).pricing_attribute66;
3213 l_header_price_att_tbl(p_att_count).pricing_attribute67
3214 := p_header_price_attributes_tbl(i).pricing_attribute67;
3215 l_header_price_att_tbl(p_att_count).pricing_attribute68
3216 := p_header_price_attributes_tbl(i).pricing_attribute68;
3217 l_header_price_att_tbl(p_att_count).pricing_attribute69
3218 := p_header_price_attributes_tbl(i).pricing_attribute69;
3219 l_header_price_att_tbl(p_att_count).pricing_attribute70
3220 := p_header_price_attributes_tbl(i).pricing_attribute70;
3221 l_header_price_att_tbl(p_att_count).pricing_attribute71
3222 := p_header_price_attributes_tbl(i).pricing_attribute71;
3223 l_header_price_att_tbl(p_att_count).pricing_attribute72
3224 := p_header_price_attributes_tbl(i).pricing_attribute72;
3225 l_header_price_att_tbl(p_att_count).pricing_attribute73
3226 := p_header_price_attributes_tbl(i).pricing_attribute73;
3227 l_header_price_att_tbl(p_att_count).pricing_attribute74
3228 := p_header_price_attributes_tbl(i).pricing_attribute74;
3229 l_header_price_att_tbl(p_att_count).pricing_attribute75
3230 := p_header_price_attributes_tbl(i).pricing_attribute75;
3231 l_header_price_att_tbl(p_att_count).pricing_attribute76
3232 := p_header_price_attributes_tbl(i).pricing_attribute76;
3233 l_header_price_att_tbl(p_att_count).pricing_attribute77
3234 := p_header_price_attributes_tbl(i).pricing_attribute77;
3235 l_header_price_att_tbl(p_att_count).pricing_attribute78
3236 := p_header_price_attributes_tbl(i).pricing_attribute78;
3237 l_header_price_att_tbl(p_att_count).pricing_attribute79
3238 := p_header_price_attributes_tbl(i).pricing_attribute79;
3239 l_header_price_att_tbl(p_att_count).pricing_attribute80
3240 := p_header_price_attributes_tbl(i).pricing_attribute80;
3241 l_header_price_att_tbl(p_att_count).pricing_attribute81
3242 := p_header_price_attributes_tbl(i).pricing_attribute81;
3243 l_header_price_att_tbl(p_att_count).pricing_attribute82
3244 := p_header_price_attributes_tbl(i).pricing_attribute82;
3245 l_header_price_att_tbl(p_att_count).pricing_attribute83
3246 := p_header_price_attributes_tbl(i).pricing_attribute83;
3247 l_header_price_att_tbl(p_att_count).pricing_attribute84
3248 := p_header_price_attributes_tbl(i).pricing_attribute84;
3249 l_header_price_att_tbl(p_att_count).pricing_attribute85
3250 := p_header_price_attributes_tbl(i).pricing_attribute85;
3251 l_header_price_att_tbl(p_att_count).pricing_attribute86
3252 := p_header_price_attributes_tbl(i).pricing_attribute86;
3253 l_header_price_att_tbl(p_att_count).pricing_attribute87
3254 := p_header_price_attributes_tbl(i).pricing_attribute87;
3255 l_header_price_att_tbl(p_att_count).pricing_attribute88
3256 := p_header_price_attributes_tbl(i).pricing_attribute88;
3257 l_header_price_att_tbl(p_att_count).pricing_attribute89
3258 := p_header_price_attributes_tbl(i).pricing_attribute89;
3259 l_header_price_att_tbl(p_att_count).pricing_attribute90
3260 := p_header_price_attributes_tbl(i).pricing_attribute90;
3261 l_header_price_att_tbl(p_att_count).pricing_attribute91
3262 := p_header_price_attributes_tbl(i).pricing_attribute91;
3263 l_header_price_att_tbl(p_att_count).pricing_attribute92
3264 := p_header_price_attributes_tbl(i).pricing_attribute92;
3265 l_header_price_att_tbl(p_att_count).pricing_attribute93
3266 := p_header_price_attributes_tbl(i).pricing_attribute93;
3267 l_header_price_att_tbl(p_att_count).pricing_attribute94
3268 := p_header_price_attributes_tbl(i).pricing_attribute94;
3269 l_header_price_att_tbl(p_att_count).pricing_attribute95
3270 := p_header_price_attributes_tbl(i).pricing_attribute95;
3271 l_header_price_att_tbl(p_att_count).pricing_attribute96
3272 := p_header_price_attributes_tbl(i).pricing_attribute96;
3273 l_header_price_att_tbl(p_att_count).pricing_attribute97
3274 := p_header_price_attributes_tbl(i).pricing_attribute97;
3275 l_header_price_att_tbl(p_att_count).pricing_attribute98
3276 := p_header_price_attributes_tbl(i).pricing_attribute98;
3277 l_header_price_att_tbl(p_att_count).pricing_attribute99
3278 := p_header_price_attributes_tbl(i).pricing_attribute99;
3279 l_header_price_att_tbl(p_att_count).pricing_attribute100
3280 := p_header_price_attributes_tbl(i).pricing_attribute100;
3281 l_header_price_att_tbl(p_att_count).context
3282 := p_header_price_attributes_tbl(i).context;
3283 l_header_price_att_tbl(p_att_count).attribute1
3284 := p_header_price_attributes_tbl(i).attribute1;
3285 l_header_price_att_tbl(p_att_count).attribute2
3286 := p_header_price_attributes_tbl(i).attribute2;
3287 l_header_price_att_tbl(p_att_count).attribute3
3288 := p_header_price_attributes_tbl(i).attribute3;
3289 l_header_price_att_tbl(p_att_count).attribute4
3290 := p_header_price_attributes_tbl(i).attribute4;
3291 l_header_price_att_tbl(p_att_count).attribute5
3292 := p_header_price_attributes_tbl(i).attribute5;
3293 l_header_price_att_tbl(p_att_count).attribute6
3294 := p_header_price_attributes_tbl(i).attribute6;
3295 l_header_price_att_tbl(p_att_count).attribute7
3296 := p_header_price_attributes_tbl(i).attribute7;
3297 l_header_price_att_tbl(p_att_count).attribute8
3298 := p_header_price_attributes_tbl(i).attribute8;
3299 l_header_price_att_tbl(p_att_count).attribute9
3300 := p_header_price_attributes_tbl(i).attribute9;
3301 l_header_price_att_tbl(p_att_count).attribute10
3302 := p_header_price_attributes_tbl(i).attribute10;
3303 l_header_price_att_tbl(p_att_count).attribute11
3304 := p_header_price_attributes_tbl(i).attribute11;
3305 l_header_price_att_tbl(p_att_count).attribute12
3306 := p_header_price_attributes_tbl(i).attribute12;
3307 l_header_price_att_tbl(p_att_count).attribute13
3308 := p_header_price_attributes_tbl(i).attribute13;
3309 l_header_price_att_tbl(p_att_count).attribute14
3310 := p_header_price_attributes_tbl(i).attribute14;
3311 l_header_price_att_tbl(p_att_count).attribute15
3312 := p_header_price_attributes_tbl(i).attribute15;
3313
3314 -- bug# 2020930
3315 l_header_price_att_tbl(p_att_count).flex_title
3316 := p_header_price_attributes_tbl(i).flex_title;
3317 -- bug# 2020930 end
3318
3319 -- this is need if header is update but the operation here is create
3320 IF p_operation <> OE_GLOBALS.G_OPR_CREATE THEN
3321 l_header_price_att_tbl(p_att_count).header_id
3322 := p_qte_rec.quote_header_id;
3323 l_header_price_att_tbl(p_att_count).order_price_attrib_id
3324 := p_header_price_attributes_tbl(i).price_attribute_id;
3325 ELSIF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
3326 l_header_price_att_tbl(p_att_count).order_price_attrib_id
3327 := FND_API.G_MISS_NUM;
3328 END IF;
3329
3330 l_header_price_att_tbl(p_att_count).operation := p_operation;
3331
3332 p_att_count := p_att_count + 1;
3333 END LOOP;
3334
3335 x_header_price_att_tbl := l_header_price_att_tbl;
3336 END map_header_price_attr;
3337
3338 PROCEDURE map_header_price_adj(
3339 p_header_price_adj_tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
3340 p_qte_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
3341 p_operation IN VARCHAR2 ,
3342 x_Header_Adj_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Tbl_Type
3343 )
3344 IS
3345 i NUMBER;
3346 adj_count NUMBER :=1;
3347 l_Header_Adj_tbl OE_Order_PUB.Header_Adj_Tbl_Type
3348 := OE_ORDER_PUB.G_MISS_HEADER_ADJ_TBL;
3349 BEGIN
3350
3351 FOR i in 1..p_header_price_adj_tbl.count LOOP
3352 l_header_adj_tbl(adj_count).attribute1
3353 := p_header_price_adj_tbl(i).attribute1;
3354 l_header_adj_tbl(adj_count).attribute10
3355 := p_header_price_adj_tbl(i).attribute10;
3356 l_header_adj_tbl(adj_count).attribute11
3357 := p_header_price_adj_tbl(i).attribute11;
3358 l_header_adj_tbl(adj_count).attribute12
3359 := p_header_price_adj_tbl(i).attribute12;
3360 l_header_adj_tbl(adj_count).attribute13
3361 := p_header_price_adj_tbl(i).attribute13;
3362 l_header_adj_tbl(adj_count).attribute14
3363 := p_header_price_adj_tbl(i).attribute14;
3364 l_header_adj_tbl(adj_count).attribute15
3365 := p_header_price_adj_tbl(i).attribute15;
3366 l_header_adj_tbl(adj_count).attribute2
3367 := p_header_price_adj_tbl(i).attribute2;
3368 l_header_adj_tbl(adj_count).attribute3
3369 := p_header_price_adj_tbl(i).attribute3;
3370 l_header_adj_tbl(adj_count).attribute4
3371 := p_header_price_adj_tbl(i).attribute4;
3372 l_header_adj_tbl(adj_count).attribute5
3373 := p_header_price_adj_tbl(i).attribute5;
3374 l_header_adj_tbl(adj_count).attribute6
3375 := p_header_price_adj_tbl(i).attribute6;
3376 l_header_adj_tbl(adj_count).attribute7
3377 := p_header_price_adj_tbl(i).attribute7;
3378 l_header_adj_tbl(adj_count).attribute8
3379 := p_header_price_adj_tbl(i).attribute8;
3380 l_header_adj_tbl(adj_count).attribute9
3381 := p_header_price_adj_tbl(i).attribute9;
3382 l_header_adj_tbl(adj_count).automatic_flag
3383 := p_header_price_adj_tbl(i).automatic_flag;
3384 l_header_adj_tbl(adj_count).context
3385 := p_header_price_adj_tbl(i).attribute_category;
3386
3387 l_header_adj_tbl(adj_count).orig_sys_discount_ref
3388 := p_header_price_adj_tbl(i).orig_sys_discount_ref;
3389 l_header_adj_tbl(adj_count).change_reason_code
3390 := p_header_price_adj_tbl(i).change_reason_code;
3391 l_header_adj_tbl(adj_count).change_reason_text
3392 := p_header_price_adj_tbl(i).change_reason_text;
3393 l_header_adj_tbl(adj_count).cost_id
3394 := p_header_price_adj_tbl(i).cost_id;
3395 l_header_adj_tbl(adj_count).tax_code
3396 := p_header_price_adj_tbl(i).tax_code;
3397 l_header_adj_tbl(adj_count).tax_exempt_flag
3398 := p_header_price_adj_tbl(i).tax_exempt_flag;
3399 l_header_adj_tbl(adj_count).tax_exempt_number
3400 := p_header_price_adj_tbl(i).tax_exempt_number;
3401 l_header_adj_tbl(adj_count).tax_exempt_reason_code
3402 := p_header_price_adj_tbl(i).tax_exempt_reason_code;
3403 l_header_adj_tbl(adj_count).parent_adjustment_id
3404 := p_header_price_adj_tbl(i).parent_adjustment_id;
3405 l_header_adj_tbl(adj_count).invoiced_flag
3406 := p_header_price_adj_tbl(i).invoiced_flag;
3407 l_header_adj_tbl(adj_count).estimated_flag
3408 := p_header_price_adj_tbl(i).estimated_flag;
3409 l_header_adj_tbl(adj_count).inc_in_sales_performance
3410 := p_header_price_adj_tbl(i).inc_in_sales_performance;
3411 l_header_adj_tbl(adj_count).split_action_code
3412 := p_header_price_adj_tbl(i).split_action_code;
3413 l_header_adj_tbl(adj_count).adjusted_amount
3414 := p_header_price_adj_tbl(i).adjusted_amount;
3415 l_header_adj_tbl(adj_count).charge_type_code
3416 := p_header_price_adj_tbl(i).charge_type_code;
3417 l_header_adj_tbl(adj_count).charge_subtype_code
3418 := p_header_price_adj_tbl(i).charge_subtype_code;
3419 l_header_adj_tbl(adj_count).RANGE_BREAK_QUANTITY
3420 := p_header_price_adj_tbl(i).RANGE_BREAK_QUANTITY;
3421 l_header_adj_tbl(adj_count).ACCRUAL_CONVERSION_RATE
3422 := p_header_price_adj_tbl(i).ACCRUAL_CONVERSION_RATE;
3423 l_header_adj_tbl(adj_count).PRICING_GROUP_SEQUENCE
3424 := p_header_price_adj_tbl(i).PRICING_GROUP_SEQUENCE;
3425 l_header_adj_tbl(adj_count).ACCRUAL_FLAG
3426 := p_header_price_adj_tbl(i).ACCRUAL_FLAG;
3427 l_header_adj_tbl(adj_count).LIST_LINE_NO
3428 := p_header_price_adj_tbl(i).LIST_LINE_NO;
3429 l_header_adj_tbl(adj_count).SOURCE_SYSTEM_CODE
3430 := p_header_price_adj_tbl(i).SOURCE_SYSTEM_CODE;
3431 l_header_adj_tbl(adj_count).BENEFIT_QTY
3432 := p_header_price_adj_tbl(i).BENEFIT_QTY;
3433 l_header_adj_tbl(adj_count).BENEFIT_UOM_CODE
3434 := p_header_price_adj_tbl(i).BENEFIT_UOM_CODE;
3435 l_header_adj_tbl(adj_count).PRINT_ON_INVOICE_FLAG
3436 := p_header_price_adj_tbl(i).PRINT_ON_INVOICE_FLAG;
3437 l_header_adj_tbl(adj_count).EXPIRATION_DATE
3438 := p_header_price_adj_tbl(i).EXPIRATION_DATE;
3439 l_header_adj_tbl(adj_count).REBATE_TRANSACTION_TYPE_CODE
3440 := p_header_price_adj_tbl(i).REBATE_TRANSACTION_TYPE_CODE;
3441 l_header_adj_tbl(adj_count).REBATE_TRANSACTION_REFERENCE
3442 := p_header_price_adj_tbl(i).REBATE_TRANSACTION_REFERENCE;
3443 l_header_adj_tbl(adj_count).REBATE_PAYMENT_SYSTEM_CODE
3444 := p_header_price_adj_tbl(i).REBATE_PAYMENT_SYSTEM_CODE;
3445 l_header_adj_tbl(adj_count).REDEEMED_DATE
3446 := p_header_price_adj_tbl(i).REDEEMED_DATE;
3447 l_header_adj_tbl(adj_count).REDEEMED_FLAG
3448 := p_header_price_adj_tbl(i).REDEEMED_FLAG;
3449 l_header_adj_tbl(adj_count).MODIFIER_LEVEL_CODE
3450 := p_header_price_adj_tbl(i).MODIFIER_LEVEL_CODE;
3451 l_header_adj_tbl(adj_count).PRICE_BREAK_TYPE_CODE
3452 := p_header_price_adj_tbl(i).PRICE_BREAK_TYPE_CODE;
3453 l_header_adj_tbl(adj_count).SUBSTITUTION_ATTRIBUTE
3454 := p_header_price_adj_tbl(i).SUBSTITUTION_ATTRIBUTE;
3455 l_header_adj_tbl(adj_count).PRORATION_TYPE_CODE
3456 := p_header_price_adj_tbl(i).PRORATION_TYPE_CODE;
3457 l_header_adj_tbl(adj_count).INCLUDE_ON_RETURNS_FLAG
3458 := p_header_price_adj_tbl(i).INCLUDE_ON_RETURNS_FLAG;
3459 l_header_adj_tbl(adj_count).CREDIT_OR_CHARGE_FLAG
3460 := p_header_price_adj_tbl(i).CREDIT_OR_CHARGE_FLAG;
3461
3462 IF p_operation <> OE_GLOBALS.G_OPR_CREATE THEN
3463 l_header_adj_tbl(adj_count).header_id
3464 := p_qte_rec.quote_header_id;
3465 l_header_adj_tbl(adj_count).price_adjustment_id
3466 := p_header_price_adj_tbl(i).price_adjustment_id;
3467 ELSIF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
3468 l_header_adj_tbl(adj_count).price_adjustment_id := FND_API.G_MISS_NUM;
3469 END IF;
3470
3471 l_header_adj_tbl(adj_count).operation := p_operation;
3472
3473 l_header_adj_tbl(adj_count).list_header_id
3474 := p_header_price_adj_tbl(i).modifier_header_id;
3475 l_header_adj_tbl(adj_count).list_line_id
3476 := p_header_price_adj_tbl(i).modifier_line_id;
3477 l_header_adj_tbl(adj_count).list_line_type_code
3478 := p_header_price_adj_tbl(i).modifier_line_type_code;
3479 l_header_adj_tbl(adj_count).modifier_mechanism_type_code
3480 := p_header_price_adj_tbl(i).modifier_mechanism_type_code;
3481 l_header_adj_tbl(adj_count).modified_from
3482 := p_header_price_adj_tbl(i).modified_from;
3483 l_header_adj_tbl(adj_count).modified_to
3484 := p_header_price_adj_tbl(i).modified_to;
3485 l_header_adj_tbl(adj_count).updated_flag
3486 := p_header_price_adj_tbl(i).updated_flag;
3487 l_header_adj_tbl(adj_count).operand
3488 := p_header_price_adj_tbl(i).operand;
3489 l_header_adj_tbl(adj_count).arithmetic_operator
3490 := p_header_price_adj_tbl(i).arithmetic_operator;
3491 l_header_adj_tbl(adj_count).applied_flag
3492 := p_header_price_adj_tbl(i).applied_flag;
3493 l_header_adj_tbl(adj_count).pricing_phase_id
3494 := p_header_price_adj_tbl(i).pricing_phase_id;
3495 l_header_adj_tbl(adj_count).update_allowed
3496 := p_header_price_adj_tbl(i).update_allowed;
3497
3498 adj_count := adj_count + 1;
3499 END LOOP;
3500 x_header_adj_tbl := l_header_adj_tbl;
3501 END map_header_price_adj;
3502
3503
3504 PROCEDURE map_header_price_adj_attr(
3505 p_header_price_adj_attr_tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type,
3506 p_operation IN VARCHAR2,
3507 x_header_adj_att_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Att_Tbl_Type
3508 )
3509 IS
3510
3511 j NUMBER;
3512 attr_count NUMBER := 1;
3513 l_header_adj_att_tbl OE_Order_PUB.Header_Adj_Att_Tbl_Type;
3514
3515 BEGIN
3516
3517 FOR j in 1..p_header_price_adj_attr_tbl.count LOOP
3518
3519 l_header_adj_att_tbl(attr_count).price_adj_attrib_id
3520 := p_header_price_adj_attr_tbl(j).price_adj_attrib_id;
3521 l_header_adj_att_tbl(attr_count).price_adjustment_id
3522 := p_header_price_adj_attr_tbl(j).price_adjustment_id;
3523 l_header_adj_att_tbl(attr_count).Adj_index
3524 := p_header_price_adj_attr_tbl(j).price_adj_index;
3525 l_header_adj_att_tbl(attr_count).flex_title
3526 := p_header_price_adj_attr_tbl(j).flex_title;
3527 l_header_adj_att_tbl(attr_count).pricing_context
3528 := p_header_price_adj_attr_tbl(j).pricing_context;
3529 l_header_adj_att_tbl(attr_count).pricing_attribute
3530 := p_header_price_adj_attr_tbl(j).pricing_attribute;
3531 l_header_adj_att_tbl(attr_count).pricing_attr_value_from
3532 := p_header_price_adj_attr_tbl(j).pricing_attr_value_from;
3533 l_header_adj_att_tbl(attr_count).pricing_attr_value_to
3534 := p_header_price_adj_attr_tbl(j).pricing_attr_value_to;
3535 l_header_adj_att_tbl(attr_count).comparison_operator
3536 := p_header_price_adj_attr_tbl(j).comparison_operator;
3537
3538
3539 IF p_operation <> OE_GLOBALS.G_OPR_CREATE THEN
3540 l_header_adj_att_tbl(attr_count).price_adjustment_id
3541 := p_header_price_adj_attr_tbl(j).price_adjustment_id;
3542 l_header_adj_att_tbl(attr_count).price_adj_attrib_id
3543 := p_header_price_adj_attr_tbl(j).price_adj_attrib_id;
3544 ELSIF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
3545 l_header_adj_att_tbl(attr_count).price_adj_attrib_id
3546 := FND_API.G_MISS_NUM;
3547 END IF;
3548
3549 l_header_adj_att_tbl(attr_count).operation := p_operation;
3550
3551 attr_count := attr_count+1;
3552
3553 END LOOP;
3554
3555 x_header_adj_att_tbl := l_header_adj_att_tbl;
3556 END map_header_price_adj_attr;
3557
3558
3559 PROCEDURE map_header_price_adj_rltn(
3560 P_Header_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type,
3561 P_operation IN VARCHAR2,
3562 x_Header_Adj_Assoc_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Adj_Assoc_Tbl_Type
3563 )
3564 IS
3565 j NUMBER;
3566 i NUMBER := 1;
3567 l_Header_Adj_Assoc_tbl OE_Order_PUB.Header_Adj_Assoc_Tbl_Type;
3568
3569 BEGIN
3570
3571 FOR j in 1..P_header_Price_Adj_rltship_Tbl.count LOOP
3572
3573
3574 IF P_operation = OE_GLOBALS.G_OPR_CREATE THEN
3575 l_Header_Adj_Assoc_tbl(i).price_adj_assoc_id := FND_API.G_MISS_NUM;
3576 l_Header_Adj_Assoc_tbl(i).Adj_index
3577 := P_Header_Price_Adj_rltship_Tbl(j).PRICE_ADJ_INDEX ;
3578 l_Header_Adj_Assoc_tbl(i).Rltd_Adj_Index
3579 := P_Header_Price_Adj_rltship_Tbl(j).RLTD_PRICE_ADJ_INDEX;
3580 ELSE
3581 l_Header_Adj_Assoc_tbl(i).price_adj_assoc_id
3582 := P_Header_Price_Adj_rltship_Tbl(j).ADJ_RELATIONSHIP_ID;
3583 l_Header_Adj_Assoc_tbl(i).price_adjustment_id
3584 := P_Header_Price_Adj_rltship_Tbl(j).PRICE_ADJUSTMENT_ID ;
3585 l_Header_Adj_Assoc_tbl(i).rltd_Price_Adj_Id
3586 := P_Header_Price_Adj_rltship_Tbl(j).RLTD_PRICE_ADJ_ID;
3587 END IF;
3588
3589 l_Header_Adj_Assoc_tbl(i).operation := P_Operation;
3590
3591 i := i + 1;
3592 END LOOP;
3593
3594 x_Header_Adj_Assoc_tbl := l_Header_Adj_Assoc_tbl;
3595
3596 END map_header_price_adj_rltn;
3597
3598 PROCEDURE map_header_sales_credits(
3599 P_header_sales_credit_Tbl IN ASO_QUOTE_PUB.Sales_credit_tbl_type,
3600 p_operation IN VARCHAR2,
3601 p_qte_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
3602 p_header_operation IN VARCHAR2,
3603 x_Header_Scredit_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Header_Scredit_Tbl_Type
3604 )
3605 IS
3606
3607 l_header_Scredit_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
3608 j NUMBER;
3609 sc_count NUMBER := 1;
3610
3611 CURSOR salesrep( p_resource_id NUMBER) IS
3612 select salesrep_id
3613 /* from jtf_rs_srp_vl */ --Commented Code Yogeshwar (MOAC)
3614 from jtf_rs_salesreps_mo_v --New Code Yogeshwar (MOAC)
3615 where resource_id = p_resource_id ;
3616 /*
3617 and NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
3618 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(
3619 USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
3620 */
3621
3622 BEGIN
3623
3624 FOR j in 1..P_header_sales_credit_Tbl.count LOOP
3625
3626 l_Header_Scredit_tbl(sc_count).attribute1
3627 := P_header_sales_credit_Tbl(j).attribute1 ;
3628 l_Header_Scredit_tbl(sc_count).attribute10
3629 := P_header_sales_credit_Tbl(j).attribute10;
3630 l_Header_Scredit_tbl(sc_count).attribute11
3631 := P_header_sales_credit_Tbl(j).attribute11;
3632 l_Header_Scredit_tbl(sc_count).attribute12
3633 := P_header_sales_credit_Tbl(j).attribute12;
3634 l_Header_Scredit_tbl(sc_count).attribute13
3635 := P_header_sales_credit_Tbl(j).attribute13;
3636 l_Header_Scredit_tbl(sc_count).attribute14
3637 := P_header_sales_credit_Tbl(j).attribute14;
3638 l_Header_Scredit_tbl(sc_count).attribute15
3639 := P_header_sales_credit_Tbl(j).attribute15;
3640 l_Header_Scredit_tbl(sc_count).attribute2
3641 := P_header_sales_credit_Tbl(j).attribute2;
3642 l_Header_Scredit_tbl(sc_count).attribute3
3643 := P_header_sales_credit_Tbl(j).attribute3;
3644 l_Header_Scredit_tbl(sc_count).attribute4
3645 := P_header_sales_credit_Tbl(j).attribute4;
3646 l_Header_Scredit_tbl(sc_count).attribute5
3647 := P_header_sales_credit_Tbl(j).attribute5;
3648 l_Header_Scredit_tbl(sc_count).attribute6
3649 := P_header_sales_credit_Tbl(j).attribute6;
3650 l_Header_Scredit_tbl(sc_count).attribute7
3651 := P_header_sales_credit_Tbl(j).attribute7;
3652 l_Header_Scredit_tbl(sc_count).attribute8
3653 := P_header_sales_credit_Tbl(j).attribute8;
3654 l_Header_Scredit_tbl(sc_count).attribute9
3655 := P_header_sales_credit_Tbl(j).attribute9;
3656 l_Header_Scredit_tbl(sc_count).context
3657 := P_header_sales_credit_Tbl(j).attribute_category_code;
3658
3659 l_Header_Scredit_tbl(sc_count).percent
3660 := P_header_sales_credit_Tbl(j).percent;
3661
3662 OPEN salesrep( P_header_sales_credit_Tbl(j).resource_id);
3663 FETCH salesrep INTO l_Header_Scredit_tbl(sc_count).salesrep_id;
3664 CLOSE salesrep;
3665
3666 l_Header_Scredit_tbl(sc_count).SALES_CREDIT_TYPE_ID
3667 := P_header_sales_credit_Tbl(j).SALES_CREDIT_TYPE_ID;
3668
3669 IF p_header_operation <> OE_GLOBALS.G_OPR_CREATE THEN
3670 l_Header_Scredit_tbl(sc_count).header_id
3671 := P_qte_rec.quote_header_id;
3672 l_Header_Scredit_tbl(sc_count).sales_credit_id
3673 := P_header_sales_credit_Tbl(j).sales_credit_id;
3674 ELSIF p_header_operation = OE_GLOBALS.G_OPR_CREATE THEN
3675 l_Header_Scredit_tbl(sc_count).sales_credit_id := FND_API.G_MISS_NUM;
3676 END IF;
3677
3678 l_Header_Scredit_tbl(sc_count).operation := p_header_operation;
3679
3680 sc_count := sc_count +1;
3681 END LOOP;
3682 x_Header_Scredit_tbl := l_Header_Scredit_tbl;
3683
3684 END map_header_sales_credits;
3685
3686
3687 PROCEDURE map_line_price_att(
3688 p_line_price_attributes_tbl IN ASO_QUOTE_PUB.Price_Attributes_Tbl_Type,
3689 p_line_index IN NUMBER,
3690 p_qte_line_index IN NUMBER,
3691 p_operation IN VARCHAR2,
3692 x_line_price_att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Price_Att_Tbl_Type
3693 )
3694 IS
3695
3696 l NUMBER;
3697 i NUMBER;
3698 l_line_price_att_tbl OE_Order_PUB.Line_Price_Att_Tbl_Type;
3699 p_att_count NUMBER := 1;
3700
3701 BEGIN
3702
3703 FOR l in 1..p_line_price_attributes_tbl.count LOOP
3704 IF ( p_line_price_attributes_tbl(l).qte_line_index = p_qte_line_index) THEN
3705
3706 l_line_price_att_tbl(p_att_count).line_index := p_line_index;
3707 l_line_price_att_tbl(p_att_count).pricing_context
3708 := p_line_price_attributes_tbl(l).pricing_context;
3709 l_line_price_att_tbl(p_att_count).pricing_attribute1
3710 := p_line_price_attributes_tbl(l).pricing_attribute1;
3711 l_line_price_att_tbl(p_att_count).pricing_attribute2
3712 := p_line_price_attributes_tbl(l).pricing_attribute2;
3713 l_line_price_att_tbl(p_att_count).pricing_attribute3
3714 := p_line_price_attributes_tbl(l).pricing_attribute3;
3715 l_line_price_att_tbl(p_att_count).pricing_attribute4
3716 := p_line_price_attributes_tbl(l).pricing_attribute4;
3717 l_line_price_att_tbl(p_att_count).pricing_attribute5
3718 := p_line_price_attributes_tbl(l).pricing_attribute5;
3719 l_line_price_att_tbl(p_att_count).pricing_attribute6
3720 := p_line_price_attributes_tbl(l).pricing_attribute6;
3721 l_line_price_att_tbl(p_att_count).pricing_attribute7
3722 := p_line_price_attributes_tbl(l).pricing_attribute7;
3723 l_line_price_att_tbl(p_att_count).pricing_attribute8
3724 := p_line_price_attributes_tbl(l).pricing_attribute8;
3725 l_line_price_att_tbl(p_att_count).pricing_attribute9
3726 := p_line_price_attributes_tbl(l).pricing_attribute9;
3727 l_line_price_att_tbl(p_att_count).pricing_attribute10
3728 := p_line_price_attributes_tbl(l).pricing_attribute10;
3729 l_line_price_att_tbl(p_att_count).pricing_attribute11
3730 := p_line_price_attributes_tbl(l).pricing_attribute11;
3731 l_line_price_att_tbl(p_att_count).pricing_attribute12
3732 := p_line_price_attributes_tbl(l).pricing_attribute12;
3733 l_line_price_att_tbl(p_att_count).pricing_attribute13
3734 := p_line_price_attributes_tbl(l).pricing_attribute13;
3735 l_line_price_att_tbl(p_att_count).pricing_attribute14
3736 := p_line_price_attributes_tbl(l).pricing_attribute14;
3737 l_line_price_att_tbl(p_att_count).pricing_attribute15
3738 := p_line_price_attributes_tbl(l).pricing_attribute15;
3739 l_line_price_att_tbl(p_att_count).pricing_attribute16
3740 := p_line_price_attributes_tbl(l).pricing_attribute16;
3741 l_line_price_att_tbl(p_att_count).pricing_attribute17
3742 := p_line_price_attributes_tbl(l).pricing_attribute17;
3743 l_line_price_att_tbl(p_att_count).pricing_attribute18
3744 := p_line_price_attributes_tbl(l).pricing_attribute18;
3745 l_line_price_att_tbl(p_att_count).pricing_attribute19
3746 := p_line_price_attributes_tbl(l).pricing_attribute19;
3747 l_line_price_att_tbl(p_att_count).pricing_attribute20
3748 := p_line_price_attributes_tbl(l).pricing_attribute20;
3749 l_line_price_att_tbl(p_att_count).pricing_attribute21
3750 := p_line_price_attributes_tbl(l).pricing_attribute21;
3751 l_line_price_att_tbl(p_att_count).pricing_attribute22
3752 := p_line_price_attributes_tbl(l).pricing_attribute22;
3753 l_line_price_att_tbl(p_att_count).pricing_attribute23
3754 := p_line_price_attributes_tbl(l).pricing_attribute23;
3755 l_line_price_att_tbl(p_att_count).pricing_attribute24
3756 := p_line_price_attributes_tbl(l).pricing_attribute24;
3757 l_line_price_att_tbl(p_att_count).pricing_attribute25
3758 := p_line_price_attributes_tbl(l).pricing_attribute25;
3759 l_line_price_att_tbl(p_att_count).pricing_attribute26
3760 := p_line_price_attributes_tbl(l).pricing_attribute26;
3761 l_line_price_att_tbl(p_att_count).pricing_attribute27
3762 := p_line_price_attributes_tbl(l).pricing_attribute27;
3763 l_line_price_att_tbl(p_att_count).pricing_attribute28
3764 := p_line_price_attributes_tbl(l).pricing_attribute28;
3765 l_line_price_att_tbl(p_att_count).pricing_attribute29
3766 := p_line_price_attributes_tbl(l).pricing_attribute29;
3767 l_line_price_att_tbl(p_att_count).pricing_attribute30
3768 := p_line_price_attributes_tbl(l).pricing_attribute30;
3769 l_line_price_att_tbl(p_att_count).pricing_attribute31
3770 := p_line_price_attributes_tbl(l).pricing_attribute31;
3771 l_line_price_att_tbl(p_att_count).pricing_attribute32
3772 := p_line_price_attributes_tbl(l).pricing_attribute32;
3773 l_line_price_att_tbl(p_att_count).pricing_attribute33
3774 := p_line_price_attributes_tbl(l).pricing_attribute33;
3775 l_line_price_att_tbl(p_att_count).pricing_attribute34
3776 := p_line_price_attributes_tbl(l).pricing_attribute34;
3777 l_line_price_att_tbl(p_att_count).pricing_attribute35
3778 := p_line_price_attributes_tbl(l).pricing_attribute35;
3779 l_line_price_att_tbl(p_att_count).pricing_attribute36
3780 := p_line_price_attributes_tbl(l).pricing_attribute36;
3781 l_line_price_att_tbl(p_att_count).pricing_attribute37
3782 := p_line_price_attributes_tbl(l).pricing_attribute37;
3783 l_line_price_att_tbl(p_att_count).pricing_attribute38
3784 := p_line_price_attributes_tbl(l).pricing_attribute38;
3785 l_line_price_att_tbl(p_att_count).pricing_attribute39
3786 := p_line_price_attributes_tbl(l).pricing_attribute39;
3787 l_line_price_att_tbl(p_att_count).pricing_attribute40
3788 := p_line_price_attributes_tbl(l).pricing_attribute40;
3789 l_line_price_att_tbl(p_att_count).pricing_attribute41
3790 := p_line_price_attributes_tbl(l).pricing_attribute41;
3791 l_line_price_att_tbl(p_att_count).pricing_attribute42
3792 := p_line_price_attributes_tbl(l).pricing_attribute42;
3793 l_line_price_att_tbl(p_att_count).pricing_attribute43
3794 := p_line_price_attributes_tbl(l).pricing_attribute43;
3795 l_line_price_att_tbl(p_att_count).pricing_attribute44
3796 := p_line_price_attributes_tbl(l).pricing_attribute44;
3797 l_line_price_att_tbl(p_att_count).pricing_attribute45
3798 := p_line_price_attributes_tbl(l).pricing_attribute45;
3799 l_line_price_att_tbl(p_att_count).pricing_attribute46
3800 := p_line_price_attributes_tbl(l).pricing_attribute46;
3801 l_line_price_att_tbl(p_att_count).pricing_attribute47
3802 := p_line_price_attributes_tbl(l).pricing_attribute47;
3803 l_line_price_att_tbl(p_att_count).pricing_attribute48
3804 := p_line_price_attributes_tbl(l).pricing_attribute48;
3805 l_line_price_att_tbl(p_att_count).pricing_attribute49
3806 := p_line_price_attributes_tbl(l).pricing_attribute49;
3807 l_line_price_att_tbl(p_att_count).pricing_attribute50
3808 := p_line_price_attributes_tbl(l).pricing_attribute50;
3809 l_line_price_att_tbl(p_att_count).pricing_attribute51
3810 := p_line_price_attributes_tbl(l).pricing_attribute51;
3811 l_line_price_att_tbl(p_att_count).pricing_attribute52
3812 := p_line_price_attributes_tbl(l).pricing_attribute52;
3813 l_line_price_att_tbl(p_att_count).pricing_attribute53
3814 := p_line_price_attributes_tbl(l).pricing_attribute53;
3815 l_line_price_att_tbl(p_att_count).pricing_attribute54
3816 := p_line_price_attributes_tbl(l).pricing_attribute54;
3817 l_line_price_att_tbl(p_att_count).pricing_attribute55
3818 := p_line_price_attributes_tbl(l).pricing_attribute55;
3819 l_line_price_att_tbl(p_att_count).pricing_attribute56
3820 := p_line_price_attributes_tbl(l).pricing_attribute56;
3821 l_line_price_att_tbl(p_att_count).pricing_attribute57
3822 := p_line_price_attributes_tbl(l).pricing_attribute57;
3823 l_line_price_att_tbl(p_att_count).pricing_attribute58
3824 := p_line_price_attributes_tbl(l).pricing_attribute58;
3825 l_line_price_att_tbl(p_att_count).pricing_attribute59
3826 := p_line_price_attributes_tbl(l).pricing_attribute59;
3827 l_line_price_att_tbl(p_att_count).pricing_attribute60
3828 := p_line_price_attributes_tbl(l).pricing_attribute60;
3829 l_line_price_att_tbl(p_att_count).pricing_attribute61
3830 := p_line_price_attributes_tbl(l).pricing_attribute61;
3831 l_line_price_att_tbl(p_att_count).pricing_attribute62
3832 := p_line_price_attributes_tbl(l).pricing_attribute62;
3833 l_line_price_att_tbl(p_att_count).pricing_attribute63
3834 := p_line_price_attributes_tbl(l).pricing_attribute63;
3835 l_line_price_att_tbl(p_att_count).pricing_attribute64
3836 := p_line_price_attributes_tbl(l).pricing_attribute64;
3837 l_line_price_att_tbl(p_att_count).pricing_attribute65
3838 := p_line_price_attributes_tbl(l).pricing_attribute65;
3839 l_line_price_att_tbl(p_att_count).pricing_attribute66
3840 := p_line_price_attributes_tbl(l).pricing_attribute66;
3841 l_line_price_att_tbl(p_att_count).pricing_attribute67
3842 := p_line_price_attributes_tbl(l).pricing_attribute67;
3843 l_line_price_att_tbl(p_att_count).pricing_attribute68
3844 := p_line_price_attributes_tbl(l).pricing_attribute68;
3845 l_line_price_att_tbl(p_att_count).pricing_attribute69
3846 := p_line_price_attributes_tbl(l).pricing_attribute69;
3847 l_line_price_att_tbl(p_att_count).pricing_attribute70
3848 := p_line_price_attributes_tbl(l).pricing_attribute70;
3849 l_line_price_att_tbl(p_att_count).pricing_attribute71
3850 := p_line_price_attributes_tbl(l).pricing_attribute71;
3851 l_line_price_att_tbl(p_att_count).pricing_attribute72
3852 := p_line_price_attributes_tbl(l).pricing_attribute72;
3853 l_line_price_att_tbl(p_att_count).pricing_attribute73
3854 := p_line_price_attributes_tbl(l).pricing_attribute73;
3855 l_line_price_att_tbl(p_att_count).pricing_attribute74
3856 := p_line_price_attributes_tbl(l).pricing_attribute74;
3857 l_line_price_att_tbl(p_att_count).pricing_attribute75
3858 := p_line_price_attributes_tbl(l).pricing_attribute75;
3859 l_line_price_att_tbl(p_att_count).pricing_attribute76
3860 := p_line_price_attributes_tbl(l).pricing_attribute76;
3861 l_line_price_att_tbl(p_att_count).pricing_attribute77
3862 := p_line_price_attributes_tbl(l).pricing_attribute77;
3863 l_line_price_att_tbl(p_att_count).pricing_attribute78
3864 := p_line_price_attributes_tbl(l).pricing_attribute78;
3865 l_line_price_att_tbl(p_att_count).pricing_attribute79
3866 := p_line_price_attributes_tbl(l).pricing_attribute79;
3867 l_line_price_att_tbl(p_att_count).pricing_attribute80
3868 := p_line_price_attributes_tbl(l).pricing_attribute80;
3869 l_line_price_att_tbl(p_att_count).pricing_attribute81
3870 := p_line_price_attributes_tbl(l).pricing_attribute81;
3871 l_line_price_att_tbl(p_att_count).pricing_attribute82
3872 := p_line_price_attributes_tbl(l).pricing_attribute82;
3873 l_line_price_att_tbl(p_att_count).pricing_attribute83
3874 := p_line_price_attributes_tbl(l).pricing_attribute83;
3875 l_line_price_att_tbl(p_att_count).pricing_attribute84
3876 := p_line_price_attributes_tbl(l).pricing_attribute84;
3877 l_line_price_att_tbl(p_att_count).pricing_attribute85
3878 := p_line_price_attributes_tbl(l).pricing_attribute85;
3879 l_line_price_att_tbl(p_att_count).pricing_attribute86
3880 := p_line_price_attributes_tbl(l).pricing_attribute86;
3881 l_line_price_att_tbl(p_att_count).pricing_attribute87
3882 := p_line_price_attributes_tbl(l).pricing_attribute87;
3883 l_line_price_att_tbl(p_att_count).pricing_attribute88
3884 := p_line_price_attributes_tbl(l).pricing_attribute88;
3885 l_line_price_att_tbl(p_att_count).pricing_attribute89
3886 := p_line_price_attributes_tbl(l).pricing_attribute89;
3887 l_line_price_att_tbl(p_att_count).pricing_attribute90
3888 := p_line_price_attributes_tbl(l).pricing_attribute90;
3889 l_line_price_att_tbl(p_att_count).pricing_attribute91
3890 := p_line_price_attributes_tbl(l).pricing_attribute91;
3891 l_line_price_att_tbl(p_att_count).pricing_attribute92
3892 := p_line_price_attributes_tbl(l).pricing_attribute92;
3893 l_line_price_att_tbl(p_att_count).pricing_attribute93
3894 := p_line_price_attributes_tbl(l).pricing_attribute93;
3895 l_line_price_att_tbl(p_att_count).pricing_attribute94
3896 := p_line_price_attributes_tbl(l).pricing_attribute94;
3897 l_line_price_att_tbl(p_att_count).pricing_attribute95
3898 := p_line_price_attributes_tbl(l).pricing_attribute95;
3899 l_line_price_att_tbl(p_att_count).pricing_attribute96
3900 := p_line_price_attributes_tbl(l).pricing_attribute96;
3901 l_line_price_att_tbl(p_att_count).pricing_attribute97
3902 := p_line_price_attributes_tbl(l).pricing_attribute97;
3903 l_line_price_att_tbl(p_att_count).pricing_attribute98
3904 := p_line_price_attributes_tbl(l).pricing_attribute98;
3905 l_line_price_att_tbl(p_att_count).pricing_attribute99
3906 := p_line_price_attributes_tbl(l).pricing_attribute99;
3907 l_line_price_att_tbl(p_att_count).pricing_attribute100
3908 := p_line_price_attributes_tbl(l).pricing_attribute100;
3909
3910 -- bug# 2020930
3911 l_line_price_att_tbl(p_att_count).flex_title
3912 := p_line_price_attributes_tbl(l).flex_title;
3913 l_line_price_att_tbl(p_att_count).context
3914 := p_line_price_attributes_tbl(l).context;
3915 l_line_price_att_tbl(p_att_count).attribute1
3916 := p_line_price_attributes_tbl(l).attribute1;
3917 l_line_price_att_tbl(p_att_count).attribute2
3918 := p_line_price_attributes_tbl(l).attribute2;
3919 l_line_price_att_tbl(p_att_count).attribute3
3920 := p_line_price_attributes_tbl(l).attribute3;
3921 l_line_price_att_tbl(p_att_count).attribute4
3922 := p_line_price_attributes_tbl(l).attribute4;
3923 l_line_price_att_tbl(p_att_count).attribute5
3924 := p_line_price_attributes_tbl(l).attribute5;
3925 l_line_price_att_tbl(p_att_count).attribute6
3926 := p_line_price_attributes_tbl(l).attribute6;
3927 l_line_price_att_tbl(p_att_count).attribute7
3928 := p_line_price_attributes_tbl(l).attribute7;
3929 l_line_price_att_tbl(p_att_count).attribute8
3930 := p_line_price_attributes_tbl(l).attribute8;
3931 l_line_price_att_tbl(p_att_count).attribute9
3932 := p_line_price_attributes_tbl(l).attribute9;
3933 l_line_price_att_tbl(p_att_count).attribute10
3934 := p_line_price_attributes_tbl(l).attribute10;
3935 l_line_price_att_tbl(p_att_count).attribute11
3936 := p_line_price_attributes_tbl(l).attribute11;
3937 l_line_price_att_tbl(p_att_count).attribute12
3938 := p_line_price_attributes_tbl(l).attribute12;
3939 l_line_price_att_tbl(p_att_count).attribute13
3940 := p_line_price_attributes_tbl(l).attribute13;
3941 l_line_price_att_tbl(p_att_count).attribute14
3942 := p_line_price_attributes_tbl(l).attribute14;
3943 l_line_price_att_tbl(p_att_count).attribute15
3944 := p_line_price_attributes_tbl(l).attribute15;
3945 -- bug# 2020930 end
3946
3947 IF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
3948 l_line_price_att_tbl(p_att_count).order_price_attrib_id
3949 := FND_API.G_MISS_NUM;
3950 ELSIF p_operation = OE_GLOBALS.G_OPR_UPDATE THEN
3951 l_line_price_att_tbl(p_att_count).header_id
3952 := p_line_price_attributes_tbl(l).quote_header_id;
3953 l_line_price_att_tbl(p_att_count).line_id
3954 := p_line_price_attributes_tbl(l).quote_line_id;
3955 l_line_price_att_tbl(p_att_count).order_price_attrib_id
3956 := p_line_price_attributes_tbl(l).price_attribute_id;
3957 ELSIF p_operation = OE_GLOBALS.G_OPR_DELETE THEN
3958 l_line_price_att_tbl(p_att_count).order_price_attrib_id
3959 := p_line_price_attributes_tbl(l).price_attribute_id;
3960 END IF;
3961
3962 l_line_price_att_tbl(p_att_count).operation := p_operation;
3963
3964 p_att_count := p_att_count + 1;
3965 END IF;
3966
3967 END LOOP; -- price attributes
3968
3969 FOR i in 1..l_line_price_att_tbl.count LOOP
3970 x_line_price_att_tbl(x_line_price_att_tbl.count + 1) :=
3971 l_line_price_att_tbl(i);
3972 END LOOP;
3973
3974 END map_line_price_att;
3975
3976
3977 PROCEDURE map_line_price_adj(
3978 p_line_price_adj_tbl IN ASO_QUOTE_PUB.Price_Adj_Tbl_Type,
3979 p_line_price_adj_attr_tbl IN ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type,
3980 p_line_index IN NUMBER,
3981 p_qte_line_index IN NUMBER,
3982 p_operation IN VARCHAR2,
3983 x_line_adj_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Tbl_Type,
3984 x_line_adj_att_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Att_Tbl_Type,
3985 lx_Line_Price_Adj_rltship_Tbl IN OUT NOCOPY ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type
3986 )
3987 IS
3988
3989 pad_count NUMBER;
3990 l NUMBER;
3991 i NUMBER;
3992 j NUMBER;
3993 adj_count NUMBER := 1;
3994 attr_count NUMBER := 1;
3995 l_line_adj_tbl OE_Order_PUB.Line_Adj_Tbl_Type
3996 := OE_ORDER_PUB.G_MISS_LINE_ADJ_TBL;
3997 l_line_adj_att_tbl OE_Order_PUB.Line_Adj_Att_Tbl_Type;
3998
3999 BEGIN
4000
4001 FOR pad_count in 1..p_line_price_adj_tbl.count LOOP
4002 IF ( p_line_price_adj_tbl(pad_count).qte_line_index = p_qte_line_index) THEN
4003
4004 l_line_adj_tbl(adj_count).attribute1 := p_line_price_adj_tbl(pad_count).attribute1;
4005 l_line_adj_tbl(adj_count).attribute10 := p_line_price_adj_tbl(pad_count).attribute10;
4006 l_line_adj_tbl(adj_count).attribute11 := p_line_price_adj_tbl(pad_count).attribute11;
4007 l_line_adj_tbl(adj_count).attribute12 := p_line_price_adj_tbl(pad_count).attribute12;
4008 l_line_adj_tbl(adj_count).attribute13 := p_line_price_adj_tbl(pad_count).attribute13;
4009 l_line_adj_tbl(adj_count).attribute14 := p_line_price_adj_tbl(pad_count).attribute14;
4010 l_line_adj_tbl(adj_count).attribute15 := p_line_price_adj_tbl(pad_count).attribute15;
4011 l_line_adj_tbl(adj_count).attribute2 := p_line_price_adj_tbl(pad_count).attribute2;
4012 l_line_adj_tbl(adj_count).attribute3 := p_line_price_adj_tbl(pad_count).attribute3;
4013 l_line_adj_tbl(adj_count).attribute4 := p_line_price_adj_tbl(pad_count).attribute4;
4014 l_line_adj_tbl(adj_count).attribute5 := p_line_price_adj_tbl(pad_count).attribute5;
4015 l_line_adj_tbl(adj_count).attribute6 := p_line_price_adj_tbl(pad_count).attribute6;
4016 l_line_adj_tbl(adj_count).attribute7 := p_line_price_adj_tbl(pad_count).attribute7;
4017 l_line_adj_tbl(adj_count).attribute8 := p_line_price_adj_tbl(pad_count).attribute8;
4018 l_line_adj_tbl(adj_count).attribute9 := p_line_price_adj_tbl(pad_count).attribute9;
4019 l_line_adj_tbl(adj_count).context := p_line_price_adj_tbl(pad_count).attribute_category;
4020 l_line_adj_tbl(adj_count).automatic_flag := p_line_price_adj_tbl(pad_count).automatic_flag;
4021
4022 IF p_operation <> OE_GLOBALS.G_OPR_CREATE AND p_operation <> FND_API.G_MISS_CHAR THEN
4023
4024 l_line_adj_tbl(adj_count).header_id := p_line_price_adj_tbl(pad_count).quote_header_id;
4025 l_line_adj_tbl(adj_count).line_id := p_line_price_adj_tbl(pad_count).quote_line_id;
4026
4027 ELSIF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
4028
4029 l_line_adj_tbl(adj_count).price_adjustment_id := FND_API.G_MISS_NUM;
4030
4031 END IF;
4032
4033 l_line_adj_tbl(adj_count).operation := p_operation;
4034 l_line_adj_tbl(adj_count).line_index := p_line_index;
4035
4036 l_line_adj_tbl(adj_count).list_header_id := p_line_price_adj_tbl(pad_count).modifier_header_id;
4037 l_line_adj_tbl(adj_count).list_line_id := p_line_price_adj_tbl(pad_count).modifier_line_id;
4038 l_line_adj_tbl(adj_count).list_line_type_code := p_line_price_adj_tbl(pad_count).modifier_line_type_code;
4039 l_line_adj_tbl(adj_count).modified_from := p_line_price_adj_tbl(pad_count).modified_from;
4040 l_line_adj_tbl(adj_count).modified_to := p_line_price_adj_tbl(pad_count).modified_to;
4041 l_line_adj_tbl(adj_count).updated_flag := p_line_price_adj_tbl(pad_count).updated_flag;
4042 l_line_adj_tbl(adj_count).operand := p_line_price_adj_tbl(pad_count).operand;
4043 l_line_adj_tbl(adj_count).arithmetic_operator := p_line_price_adj_tbl(pad_count).arithmetic_operator;
4044 l_line_adj_tbl(adj_count).automatic_flag := p_line_price_adj_tbl(pad_count).automatic_flag;
4045 l_line_adj_tbl(adj_count).applied_flag := p_line_price_adj_tbl(pad_count).applied_flag;
4046 l_line_adj_tbl(adj_count).pricing_phase_id := p_line_price_adj_tbl(pad_count).pricing_phase_id;
4047 l_line_adj_tbl(adj_count).update_allowed := p_line_price_adj_tbl(pad_count).update_allowed;
4048 l_line_adj_tbl(adj_count).updated_flag := p_line_price_adj_tbl(pad_count).updated_flag;
4049 l_line_adj_tbl(adj_count).updated_flag := p_line_price_adj_tbl(pad_count).updated_flag;
4050 l_line_adj_tbl(adj_count).modifier_mechanism_type_code := p_line_price_adj_tbl(pad_count).modifier_mechanism_type_code;
4051
4052 -- added later
4053 l_line_adj_tbl(adj_count).orig_sys_discount_ref := p_line_price_adj_tbl(pad_count).orig_sys_discount_ref;
4054 l_line_adj_tbl(adj_count).change_reason_code := p_line_price_adj_tbl(pad_count).change_reason_code;
4055 l_line_adj_tbl(adj_count).change_reason_text := p_line_price_adj_tbl(pad_count).change_reason_text;
4056 l_line_adj_tbl(adj_count).cost_id := p_line_price_adj_tbl(pad_count).cost_id;
4057 l_line_adj_tbl(adj_count).tax_code := p_line_price_adj_tbl(pad_count).tax_code;
4058 l_line_adj_tbl(adj_count).tax_exempt_flag := p_line_price_adj_tbl(pad_count).tax_exempt_flag;
4059 l_line_adj_tbl(adj_count).tax_exempt_number := p_line_price_adj_tbl(pad_count).tax_exempt_number;
4060 l_line_adj_tbl(adj_count).tax_exempt_reason_code := p_line_price_adj_tbl(pad_count).tax_exempt_reason_code;
4061 l_line_adj_tbl(adj_count).parent_adjustment_id := p_line_price_adj_tbl(pad_count).parent_adjustment_id;
4062 l_line_adj_tbl(adj_count).invoiced_flag := p_line_price_adj_tbl(pad_count).invoiced_flag;
4063 l_line_adj_tbl(adj_count).estimated_flag := p_line_price_adj_tbl(pad_count).estimated_flag;
4064 l_line_adj_tbl(adj_count).inc_in_sales_performance := p_line_price_adj_tbl(pad_count).inc_in_sales_performance;
4065 l_line_adj_tbl(adj_count).split_action_code := p_line_price_adj_tbl(pad_count).split_action_code;
4066 l_line_adj_tbl(adj_count).adjusted_amount := p_line_price_adj_tbl(pad_count).adjusted_amount;
4067 l_line_adj_tbl(adj_count).charge_type_code := p_line_price_adj_tbl(pad_count).charge_type_code;
4068 l_line_adj_tbl(adj_count).charge_subtype_code := p_line_price_adj_tbl(pad_count).charge_subtype_code;
4069 l_line_adj_tbl(adj_count).RANGE_BREAK_QUANTITY := p_line_price_adj_tbl(pad_count).RANGE_BREAK_QUANTITY;
4070 l_line_adj_tbl(adj_count).ACCRUAL_CONVERSION_RATE := p_line_price_adj_tbl(pad_count).ACCRUAL_CONVERSION_RATE;
4071 l_line_adj_tbl(adj_count).PRICING_GROUP_SEQUENCE := p_line_price_adj_tbl(pad_count).PRICING_GROUP_SEQUENCE;
4072 l_line_adj_tbl(adj_count).ACCRUAL_FLAG := p_line_price_adj_tbl(pad_count).ACCRUAL_FLAG;
4073 l_line_adj_tbl(adj_count).LIST_LINE_NO := p_line_price_adj_tbl(pad_count).LIST_LINE_NO;
4074 l_line_adj_tbl(adj_count).SOURCE_SYSTEM_CODE := p_line_price_adj_tbl(pad_count).SOURCE_SYSTEM_CODE;
4075 l_line_adj_tbl(adj_count).BENEFIT_QTY := p_line_price_adj_tbl(pad_count).BENEFIT_QTY;
4076 l_line_adj_tbl(adj_count).BENEFIT_UOM_CODE := p_line_price_adj_tbl(pad_count).BENEFIT_UOM_CODE;
4077 l_line_adj_tbl(adj_count).PRINT_ON_INVOICE_FLAG := p_line_price_adj_tbl(pad_count).PRINT_ON_INVOICE_FLAG;
4078 l_line_adj_tbl(adj_count).EXPIRATION_DATE := p_line_price_adj_tbl(pad_count).EXPIRATION_DATE;
4079 l_line_adj_tbl(adj_count).REBATE_TRANSACTION_TYPE_CODE := p_line_price_adj_tbl(pad_count).REBATE_TRANSACTION_TYPE_CODE;
4080 l_line_adj_tbl(adj_count).REBATE_TRANSACTION_REFERENCE := p_line_price_adj_tbl(pad_count).REBATE_TRANSACTION_REFERENCE;
4081 l_line_adj_tbl(adj_count).REBATE_PAYMENT_SYSTEM_CODE := p_line_price_adj_tbl(pad_count).REBATE_PAYMENT_SYSTEM_CODE;
4082 l_line_adj_tbl(adj_count).REDEEMED_DATE := p_line_price_adj_tbl(pad_count).REDEEMED_DATE;
4083 l_line_adj_tbl(adj_count).REDEEMED_FLAG := p_line_price_adj_tbl(pad_count).REDEEMED_FLAG;
4084 l_line_adj_tbl(adj_count).MODIFIER_LEVEL_CODE := p_line_price_adj_tbl(pad_count).MODIFIER_LEVEL_CODE;
4085 l_line_adj_tbl(adj_count).PRICE_BREAK_TYPE_CODE := p_line_price_adj_tbl(pad_count).PRICE_BREAK_TYPE_CODE;
4086 l_line_adj_tbl(adj_count).SUBSTITUTION_ATTRIBUTE := p_line_price_adj_tbl(pad_count).SUBSTITUTION_ATTRIBUTE;
4087 l_line_adj_tbl(adj_count).PRORATION_TYPE_CODE := p_line_price_adj_tbl(pad_count).PRORATION_TYPE_CODE;
4088 l_line_adj_tbl(adj_count).INCLUDE_ON_RETURNS_FLAG := p_line_price_adj_tbl(pad_count).INCLUDE_ON_RETURNS_FLAG;
4089 l_line_adj_tbl(adj_count).CREDIT_OR_CHARGE_FLAG := p_line_price_adj_tbl(pad_count).CREDIT_OR_CHARGE_FLAG;
4090 -- end added later
4091
4092 FOR l in 1..p_line_price_adj_attr_tbl.count LOOP
4093
4094 IF p_line_price_adj_attr_tbl(l).price_adj_index = pad_count THEN
4095
4096 IF p_operation = OE_GLOBALS.G_OPR_CREATE THEN
4097 l_line_adj_att_tbl(attr_count).price_adj_attrib_id := FND_API.G_MISS_NUM;
4098 ELSIF p_operation <> OE_GLOBALS.G_OPR_CREATE THEN
4099 l_line_adj_att_tbl(attr_count).price_adj_attrib_id := p_line_price_adj_attr_tbl(l).price_adj_attrib_id;
4100 l_line_adj_att_tbl(attr_count).price_adjustment_id := p_line_price_adj_attr_tbl(l).price_adjustment_id;
4101 END IF;
4102
4103 l_line_adj_att_tbl(attr_count).operation := p_operation;
4104
4105 l_line_adj_att_tbl(attr_count).Adj_index := adj_count;
4106 l_line_adj_att_tbl(attr_count).flex_title := p_line_price_adj_attr_tbl(l).flex_title;
4107 l_line_adj_att_tbl(attr_count).pricing_context := p_line_price_adj_attr_tbl(l).pricing_context;
4108 l_line_adj_att_tbl(attr_count).pricing_attribute := p_line_price_adj_attr_tbl(l).pricing_attribute;
4109 l_line_adj_att_tbl(attr_count).pricing_attr_value_from := p_line_price_adj_attr_tbl(l).pricing_attr_value_from;
4110 l_line_adj_att_tbl(attr_count).pricing_attr_value_to := p_line_price_adj_attr_tbl(l).pricing_attr_value_to;
4111 l_line_adj_att_tbl(attr_count).comparison_operator := p_line_price_adj_attr_tbl(l).comparison_operator;
4112
4113 attr_count := attr_count+1;
4114 END IF;
4115
4116 END LOOP; --price adjustment attributes
4117
4118 adj_count := adj_count + 1;
4119
4120 END IF;
4121
4122 -- pbh/prg
4123 FOR j in 1..lx_Line_Price_Adj_rltship_Tbl.count LOOP
4124 IF aso_debug_pub.g_debug_flag = 'Y' THEN
4125 aso_debug_pub.add('map_ln_rlt: p_line_price_adj_tbl(pad_count).price_adjustment_id: '||p_line_price_adj_tbl(pad_count).price_adjustment_id,1,'N');
4126 END IF;
4127
4128 IF lx_Line_Price_Adj_rltship_Tbl(j).price_adjustment_id
4129 = p_line_price_adj_tbl(pad_count).price_adjustment_id THEN
4130 lx_Line_Price_Adj_rltship_Tbl(j).price_adj_index := pad_count;
4131 END IF;
4132
4133 IF lx_Line_Price_Adj_rltship_Tbl(j).rltd_price_adj_id
4134 = p_line_price_adj_tbl(pad_count).price_adjustment_id THEN
4135 lx_Line_Price_Adj_rltship_Tbl(j).rltd_price_adj_index := pad_count;
4136 END IF;
4137
4138 END LOOP;
4139 -- pbh/prg
4140
4141 IF aso_debug_pub.g_debug_flag = 'Y' THEN
4142 aso_debug_pub.add('map_ln_rlt: lx_Line_Price_Adj_rltship_Tbl.count: '||lx_Line_Price_Adj_rltship_Tbl.count,1,'N');
4143 END IF;
4144
4145 END LOOP; --- price adjustments
4146
4147 FOR i IN 1..l_line_adj_tbl.count LOOP
4148 x_line_adj_tbl(x_line_adj_tbl.count +1 ) := l_line_adj_tbl(i);
4149 END LOOP;
4150
4151 FOR i IN 1..l_line_adj_att_tbl.count LOOP
4152 x_line_adj_att_tbl(x_line_adj_att_tbl.count + 1)
4153 := l_line_adj_att_tbl(i);
4154 END LOOP;
4155
4156 END map_line_price_adj;
4157
4158
4159 PROCEDURE map_line_price_adj_rltn(
4160 P_Line_Price_Adj_rltship_Tbl IN ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type,
4161 p_line_index IN NUMBER,
4162 p_qte_line_index IN NUMBER,
4163 p_operation IN VARCHAR2,
4164 x_Line_Adj_Assoc_tbl IN OUT NOCOPY OE_Order_PUB.Line_Adj_Assoc_Tbl_Type
4165 )
4166 IS
4167
4168 l NUMBER;
4169 i NUMBER;
4170 adj_rlt_count NUMBER := 1;
4171 l_Line_Adj_Assoc_tbl OE_Order_PUB.Line_Adj_Assoc_Tbl_Type;
4172
4173 BEGIN
4174
4175 FOR l in 1..P_Line_Price_Adj_rltship_Tbl.count LOOP
4176 IF P_Line_Price_Adj_rltship_Tbl(l).qte_line_index = p_qte_line_index THEN
4177
4178 l_Line_Adj_Assoc_tbl(adj_rlt_count).operation := p_operation;
4179
4180 IF l_Line_Adj_Assoc_tbl(adj_rlt_count).operation = OE_GLOBALS.G_OPR_CREATE THEN
4181 l_Line_Adj_Assoc_tbl(adj_rlt_count).Adj_index
4182 := P_Line_Price_Adj_rltship_Tbl(l).price_adj_index;
4183 l_Line_Adj_Assoc_tbl(adj_rlt_count).Rltd_Adj_index
4184 := P_Line_Price_Adj_rltship_Tbl(l).rltd_price_adj_index;
4185 l_Line_Adj_Assoc_tbl(adj_rlt_count).Line_Index := p_line_index;
4186 l_line_Adj_Assoc_tbl(adj_rlt_count).price_adj_assoc_id := FND_API.G_MISS_NUM;
4187
4188 ELSIF l_Line_Adj_Assoc_tbl(adj_rlt_count).operation = OE_GLOBALS.G_OPR_UPDATE THEN
4189
4190 l_Line_Adj_Assoc_tbl(adj_rlt_count).line_id
4191 := P_Line_Price_Adj_rltship_Tbl(l).QUOTE_LINE_ID;
4192 l_Line_Adj_Assoc_tbl(adj_rlt_count).price_adj_assoc_id
4193 := P_Line_Price_Adj_rltship_Tbl(l).ADJ_RELATIONSHIP_ID;
4194 l_Line_Adj_Assoc_tbl(adj_rlt_count).price_adjustment_id
4195 := P_Line_Price_Adj_rltship_Tbl(l).PRICE_ADJUSTMENT_ID ;
4196 l_Line_Adj_Assoc_tbl(adj_rlt_count).rltd_Price_Adj_Id
4197 := P_Line_Price_Adj_rltship_Tbl(l).RLTD_PRICE_ADJ_ID;
4198 END IF;
4199
4200 adj_rlt_count := adj_rlt_count + 1;
4201
4202 END IF;
4203
4204 END LOOP; -- price adj rltship
4205
4206 FOR i in 1..l_Line_Adj_Assoc_tbl.count loop
4207 x_Line_Adj_Assoc_tbl(x_Line_Adj_Assoc_tbl.count + 1) :=
4208 l_Line_Adj_Assoc_tbl(i);
4209 END LOOP;
4210
4211 END map_line_price_adj_rltn;
4212
4213
4214 PROCEDURE map_line_sales_credit(
4215 P_line_sales_credit_Tbl IN ASO_QUOTE_PUB.Sales_credit_tbl_type,
4216 p_line_index IN NUMBER,
4217 p_qte_line_index IN NUMBER,
4218 p_line_operation IN VARCHAR2,
4219 p_operation IN VARCHAR2,
4220 x_Line_Scredit_tbl IN OUT NOCOPY OE_Order_PUB.Line_Scredit_Tbl_Type
4221 )
4222 IS
4223
4224 l NUMBER;
4225 i NUMBER;
4226 sc_count NUMBER := 1;
4227 l_Line_Scredit_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
4228
4229 CURSOR salesrep( p_resource_id NUMBER) IS
4230 select salesrep_id
4231 -- from jtf_rs_srp_vl Commented Code Yogeshwar (MOAC)
4232 from jtf_rs_salesreps_mo_v --New Code Yogeshwar (MOAC)
4233 where resource_id = p_resource_id ;
4234 --Commented Code Start Yogeshwar (MOAC)
4235 /*
4236 and NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), '',
4237 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(
4238 USERENV('CLIENT_INFO'),1,1), ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
4239 */
4240 --Commented Code End Yogeshwar (MOAC)
4241
4242 BEGIN
4243
4244 FOR l in 1..P_line_sales_credit_Tbl.count LOOP
4245
4246 IF P_line_sales_credit_Tbl(l).qte_line_index = p_qte_line_index THEN
4247 l_Line_Scredit_tbl(sc_count).line_index := p_line_index;
4248 l_Line_Scredit_tbl(sc_count).attribute1
4249 := P_line_sales_credit_Tbl(l).attribute1 ;
4250 l_Line_Scredit_tbl(sc_count).attribute10
4251 := P_line_sales_credit_Tbl(l).attribute10;
4252 l_Line_Scredit_tbl(sc_count).attribute11
4253 := P_line_sales_credit_Tbl(l).attribute11;
4254 l_Line_Scredit_tbl(sc_count).attribute12
4255 := P_line_sales_credit_Tbl(l).attribute12;
4256 l_Line_Scredit_tbl(sc_count).attribute13
4257 := P_line_sales_credit_Tbl(l).attribute13;
4258 l_Line_Scredit_tbl(sc_count).attribute14
4259 := P_line_sales_credit_Tbl(l).attribute14;
4260 l_Line_Scredit_tbl(sc_count).attribute15
4261 := P_line_sales_credit_Tbl(l).attribute15;
4262 l_Line_Scredit_tbl(sc_count).attribute2
4263 := P_line_sales_credit_Tbl(l).attribute2;
4264 l_Line_Scredit_tbl(sc_count).attribute3
4265 := P_line_sales_credit_Tbl(l).attribute3;
4266 l_Line_Scredit_tbl(sc_count).attribute4
4267 := P_line_sales_credit_Tbl(l).attribute4;
4268 l_Line_Scredit_tbl(sc_count).attribute5
4269 := P_line_sales_credit_Tbl(l).attribute5;
4270 l_Line_Scredit_tbl(sc_count).attribute6
4271 := P_line_sales_credit_Tbl(l).attribute6;
4272 l_Line_Scredit_tbl(sc_count).attribute7
4273 := P_line_sales_credit_Tbl(l).attribute7;
4274 l_Line_Scredit_tbl(sc_count).attribute8
4275 := P_line_sales_credit_Tbl(l).attribute8;
4276 l_Line_Scredit_tbl(sc_count).attribute9
4277 := P_line_sales_credit_Tbl(l).attribute9;
4278 l_Line_Scredit_tbl(sc_count).context
4279 := P_line_sales_credit_Tbl(l).attribute_category_code;
4280 l_Line_Scredit_tbl(sc_count).percent
4281 := P_line_sales_credit_Tbl(l).percent;
4282
4283 OPEN salesrep(P_line_sales_credit_Tbl(l).resource_id);
4284 FETCH salesrep into l_Line_Scredit_tbl(sc_count).salesrep_id;
4285 CLOSE salesrep;
4286
4287 l_Line_Scredit_tbl(sc_count).SALES_CREDIT_TYPE_ID
4288 := P_line_sales_credit_Tbl(l).SALES_CREDIT_TYPE_ID;
4289
4290 IF p_line_operation <> OE_GLOBALS.G_OPR_CREATE THEN
4291 l_Line_Scredit_tbl(sc_count).sales_credit_id
4292 := P_line_sales_credit_Tbl(l).sales_credit_id;
4293 ELSIF p_line_operation = OE_GLOBALS.G_OPR_CREATE THEN
4294 l_Line_Scredit_tbl(sc_count).sales_credit_id := FND_API.G_MISS_NUM;
4295 END IF;
4296
4297 l_Line_Scredit_tbl(sc_count).operation := p_line_operation;
4298
4299 sc_count := sc_count +1;
4300
4301 END IF;
4302 END LOOP; -- salescredit
4303
4304 FOR i in 1..l_line_Scredit_tbl.count loop
4305 x_line_Scredit_tbl(x_line_Scredit_tbl.count + 1)
4306 := l_line_Scredit_tbl(i);
4307 END LOOP;
4308
4309 END map_line_sales_credit;
4310
4311 --Line Payments change
4312
4313 PROCEDURE map_line_payments(
4314 P_line_payment_Tbl IN ASO_QUOTE_PUB.payment_tbl_type,
4315 p_line_index IN NUMBER,
4316 p_qte_line_index IN NUMBER,
4317 p_line_operation IN VARCHAR2,
4318 p_operation IN VARCHAR2,
4319 x_Line_tbl IN OUT NOCOPY OE_Order_PUB.Line_Tbl_Type,
4320 x_Line_Payment_tbl IN OUT NOCOPY OE_Order_PUB.Line_Payment_Tbl_Type
4321 )
4322 IS
4323 i NUMBER;
4324 pay_count NUMBER := 1;
4325 l_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
4326 l_line_tbl OE_Order_PUB.Line_Tbl_Type := x_Line_tbl;
4327 l_om_defaulting_prof VARCHAR2(2)
4328 := NVL(FND_PROFILE.Value('ASO_OM_DEFAULTING'), 'N');
4329 Begin
4330 FOR i in 1..P_Line_Payment_Tbl.count LOOP
4331
4332 IF P_Line_Payment_Tbl(i).qte_line_index = p_qte_line_index THEN
4333 l_Line_payment_tbl(pay_count).line_index := p_line_index;
4334
4335 IF (NVL(l_om_defaulting_prof, 'N') = 'Y') THEN
4336 IF P_Line_Payment_Tbl(i).payment_term_id IS NULL THEN
4337 l_Line_tbl(p_line_index).payment_term_id := FND_API.G_MISS_NUM;
4338 ELSE
4339 l_Line_tbl(p_line_index).payment_term_id := P_Line_Payment_Tbl(i).payment_term_id;
4340 END IF;
4341 ELSE
4342 l_Line_tbl(p_line_index).payment_term_id := P_Line_Payment_Tbl(i).payment_term_id;
4343 END IF;
4344
4345 IF aso_debug_pub.g_debug_flag = 'Y' THEN
4346 aso_debug_pub.add('P_Line_Payment_Tbl(i).cust_po_number: '||P_Line_Payment_Tbl(i).cust_po_number,1,'N');
4347 aso_debug_pub.add('P_Line_Payment_Tbl(i).cust_po_line_number: '||P_Line_Payment_Tbl(i).cust_po_line_number,1,'N');
4348 END IF;
4349
4350 l_Line_tbl(p_line_index).cust_po_number := P_Line_Payment_Tbl(i).cust_po_number;
4351 l_Line_tbl(p_line_index).customer_line_number := P_Line_Payment_Tbl(i).cust_po_line_number;
4352
4353 IF aso_debug_pub.g_debug_flag = 'Y' THEN
4354 aso_debug_pub.add('P_Line_Payment_Tbl(i).payment_type_code: '||P_Line_Payment_Tbl(i).payment_type_code,1,'N');
4355 aso_debug_pub.add('P_Line_Payment_Tbl(i).payment_amount: '||P_Line_Payment_Tbl(i).payment_amount,1,'N');
4356 END IF;
4357 IF P_Line_Payment_Tbl(i).payment_type_code IS NOT NULL THEN
4358
4359 l_Line_Payment_tbl(pay_count).payment_type_code := P_Line_Payment_Tbl(i).payment_type_code;
4360
4361 l_Line_Payment_tbl(pay_count).payment_amount := P_Line_Payment_Tbl(i).payment_amount;
4362
4363 l_Line_Payment_tbl(pay_count).operation := p_line_operation;
4364
4365 l_Line_Payment_tbl(pay_count).trxn_extension_id := P_Line_Payment_Tbl(i).trxn_extension_id;
4366 l_Line_Payment_tbl(pay_count).payment_collection_event := 'INVOICE';
4367 l_Line_Payment_tbl(pay_count).payment_level_code := 'LINE';
4368
4369 IF aso_debug_pub.g_debug_flag = 'Y' THEN
4370 aso_debug_pub.add('l_Line_Payment_tbl(pay_count).payment_type_code: '||l_Line_Payment_tbl(pay_count).payment_type_code,1,'N');
4371 aso_debug_pub.add('l_Line_Payment_tbl(pay_count).payment_amount: '||l_Line_Payment_tbl(pay_count).payment_amount,1,'N');
4372 aso_debug_pub.add('l_Line_Payment_tbl(pay_count).operation: '||l_Line_Payment_tbl(pay_count).operation,1,'N');
4373 aso_debug_pub.add('l_Line_Payment_tbl(pay_count).trxn_extension_id: '||l_Line_Payment_tbl(pay_count).trxn_extension_id,1,'N');
4374 aso_debug_pub.add('l_Line_Payment_tbl(pay_count).payment_collection_event: '||l_Line_Payment_tbl(pay_count).payment_collection_event,1,'N');
4375 aso_debug_pub.add('l_Line_Payment_tbl(pay_count).payment_level_code: '||l_Line_Payment_tbl(pay_count).payment_level_code,1,'N');
4376 END IF;
4377
4378 IF P_Line_Payment_Tbl(i).payment_type_code = 'CHECK' THEN
4379 l_Line_Payment_tbl(pay_count).check_number := P_Line_Payment_Tbl(i).payment_ref_number;
4380 END IF;
4381
4382 END IF; -- payment_type_code is not null
4383
4384 pay_count := pay_count +1;
4385
4386 END IF;
4387
4388 END LOOP;
4389
4390 x_Line_Payment_tbl := l_Line_Payment_tbl;
4391 x_Line_tbl := l_Line_tbl;
4392 IF aso_debug_pub.g_debug_flag = 'Y' THEN
4393 aso_debug_pub.add('mapped line payment tblx_Line_Payment_tbl.count'||x_Line_Payment_tbl.count,1,'N');
4394 aso_debug_pub.add('mapped line payment tbl',1,'N');
4395 aso_debug_pub.add('mapped line payment tblx_Line_tbl.count'||x_Line_tbl.count,1,'N');
4396 END IF;
4397
4398
4399 end map_line_payments;
4400
4401 PROCEDURE map_lot_serial(
4402 P_lot_serial_tbl IN ASO_QUOTE_PUB.Lot_Serial_Tbl_Type,
4403 p_operation IN VARCHAR2,
4404 p_line_index IN NUMBER,
4405 p_qte_line_index IN NUMBER,
4406 x_lot_serial_tbl OUT NOCOPY /* file.sql.39 change */ OE_Order_PUB.Lot_Serial_Tbl_Type
4407 )
4408 IS
4409
4410 l NUMBER;
4411 lot_srl_count NUMBER :=1;
4412 l_lot_serial_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
4413
4414 BEGIN
4415
4416 For l in 1..P_lot_serial_tbl.count LOOP
4417
4418 IF P_lot_serial_tbl(l).line_index = p_qte_line_index THEN
4419
4420
4421 l_lot_serial_tbl(lot_srl_count).attribute1
4422 := P_lot_serial_tbl(l).attribute1;
4423 l_lot_serial_tbl(lot_srl_count).attribute10
4424 := P_lot_serial_tbl(l).attribute10 ;
4425 l_lot_serial_tbl(lot_srl_count).attribute11
4426 := P_lot_serial_tbl(l).attribute11 ;
4427 l_lot_serial_tbl(lot_srl_count).attribute12
4428 := P_lot_serial_tbl(l).attribute12;
4429 l_lot_serial_tbl(lot_srl_count).attribute13
4430 := P_lot_serial_tbl(l).attribute13 ;
4431 l_lot_serial_tbl(lot_srl_count).attribute14
4432 := P_lot_serial_tbl(l).attribute14 ;
4433 l_lot_serial_tbl(lot_srl_count).attribute15
4434 := P_lot_serial_tbl(l).attribute15 ;
4435 l_lot_serial_tbl(lot_srl_count).attribute2
4436 := P_lot_serial_tbl(l).attribute2 ;
4437 l_lot_serial_tbl(lot_srl_count).attribute3
4438 := P_lot_serial_tbl(l).attribute3;
4439 l_lot_serial_tbl(lot_srl_count).attribute4
4440 := P_lot_serial_tbl(l).attribute4;
4441 l_lot_serial_tbl(lot_srl_count).attribute5
4442 := P_lot_serial_tbl(l).attribute5;
4443 l_lot_serial_tbl(lot_srl_count).attribute6
4444 := P_lot_serial_tbl(l).attribute6;
4445 l_lot_serial_tbl(lot_srl_count).attribute7
4446 := P_lot_serial_tbl(l).attribute7;
4447 l_lot_serial_tbl(lot_srl_count).attribute8
4448 := P_lot_serial_tbl(l).attribute8;
4449 l_lot_serial_tbl(lot_srl_count).attribute9
4450 := P_lot_serial_tbl(l).attribute9;
4451 l_lot_serial_tbl(lot_srl_count).context
4452 := P_lot_serial_tbl(l).context ;
4453 l_lot_serial_tbl(lot_srl_count).created_by
4454 := P_lot_serial_tbl(l).created_by ;
4455 l_lot_serial_tbl(lot_srl_count).creation_date
4456 := P_lot_serial_tbl(l).creation_date;
4457 l_lot_serial_tbl(lot_srl_count).from_serial_number
4458 := P_lot_serial_tbl(l).from_serial_number ;
4459 l_lot_serial_tbl(lot_srl_count).last_updated_by
4460 := P_lot_serial_tbl(l).last_updated_by ;
4461 l_lot_serial_tbl(lot_srl_count).last_update_date
4462 := P_lot_serial_tbl(l).last_update_date;
4463 l_lot_serial_tbl(lot_srl_count).last_update_login
4464 := P_lot_serial_tbl(l).last_update_login;
4465 -- l_lot_serial_tbl(lot_srl_count).line_id
4466 -- := P_lot_serial_tbl(l).line_id;
4467 l_lot_serial_tbl(lot_srl_count).lot_number
4468 := P_lot_serial_tbl(l).lot_number;
4469 l_lot_serial_tbl(lot_srl_count).lot_serial_id
4470 := P_lot_serial_tbl(l).lot_serial_id;
4471 l_lot_serial_tbl(lot_srl_count).quantity
4472 := P_lot_serial_tbl(l).quantity ;
4473 l_lot_serial_tbl(lot_srl_count).to_serial_number
4474 := P_lot_serial_tbl(l).to_serial_number;
4475 l_lot_serial_tbl(lot_srl_count).return_status
4476 := P_lot_serial_tbl(l).return_status;
4477 l_lot_serial_tbl(lot_srl_count).db_flag
4478 := P_lot_serial_tbl(l).db_flag;
4479 l_lot_serial_tbl(lot_srl_count).operation
4480 := P_lot_serial_tbl(l).operation ;
4481 l_lot_serial_tbl(lot_srl_count).line_index := p_line_index;
4482 l_lot_serial_tbl(lot_srl_count).orig_sys_lotserial_ref
4483 := P_lot_serial_tbl(l).orig_sys_lotserial_ref ;
4484 l_lot_serial_tbl(lot_srl_count).change_request_code
4485 := P_lot_serial_tbl(l).change_request_code;
4486 l_lot_serial_tbl(lot_srl_count).status_flag
4487 := P_lot_serial_tbl(l).status_flag;
4488 l_lot_serial_tbl(lot_srl_count).line_set_id
4489 := P_lot_serial_tbl(l).line_set_id ;
4490
4491 IF p_operation <> OE_GLOBALS.G_OPR_CREATE THEN
4492 l_lot_serial_tbl(lot_srl_count).line_id
4493 := P_lot_serial_tbl(l).line_id;
4494 END IF;
4495
4496 l_lot_serial_tbl(lot_srl_count).operation := p_operation;
4497
4498 lot_srl_count := lot_srl_count + 1;
4499
4500 END IF;
4501 END LOOP; -- lot serial LOOP
4502
4503 x_lot_serial_tbl := l_lot_serial_tbl;
4504
4505 END map_lot_serial;
4506
4507
4508 End ASO_MAP_QUOTE_ORDER_INT;