[Home] [Help]
PACKAGE BODY: APPS.ASO_OPP_QTE_PUB
Source
1 PACKAGE BODY ASO_OPP_QTE_PUB AS
2 /* $Header: asopopqb.pls 120.16.12000000.3 2007/07/09 16:47:52 ajosephg ship $ */
3
4 -- Start of Comments
5 -- Package name : ASO_OPP_QTE_PUB
6 -- Purpose : API to create quote from opportunity
7 -- End of Comments
8
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_OPP_QTE_PUB';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asopopqb.pls';
12
13
14 PROCEDURE Create_Qte_Opportunity(
15 P_API_VERSION_NUMBER IN NUMBER,
16 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
17 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
18 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
19 P_SOURCE_CODE IN VARCHAR2,
20 P_QUOTE_HEADER_REC IN ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_MISS_Qte_Header_Rec,
21 P_HEADER_PAYMENT_REC IN ASO_QUOTE_PUB.Payment_Rec_Type := ASO_QUOTE_PUB.G_MISS_Payment_REC,
22 P_HEADER_SHIPMENT_REC IN ASO_QUOTE_PUB.Shipment_Rec_Type := ASO_QUOTE_PUB.G_MISS_Shipment_REC,
23 P_HEADER_TAX_DETAIL_REC IN ASO_QUOTE_PUB.Tax_Detail_Rec_Type := ASO_QUOTE_PUB.G_MISS_Tax_Detail_Rec,
24 P_TEMPLATE_TBL IN ASO_QUOTE_PUB.TEMPLATE_TBL_TYPE := ASO_QUOTE_PUB.G_MISS_TEMPLATE_TBL,
25 P_OPP_QTE_IN_REC IN OPP_QTE_IN_REC_TYPE,
26 P_CONTROL_REC IN ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_MISS_Control_Rec,
27 X_OPP_QTE_OUT_REC OUT NOCOPY /* file.sql.39 change */ OPP_QTE_OUT_REC_TYPE,
28 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
29 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
30 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2
31 )
32 IS
33
34 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
35 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
36
37 L_API_NAME VARCHAR2(50) := 'Create_Qte_Opportunity';
38 L_API_VERSION CONSTANT NUMBER := 1.0;
39
40 l_line_count NUMBER;
41 l_ln_shipment_count NUMBER;
42 l_ln_sales_credit_count NUMBER;
43 l_ln_price_attr_count NUMBER;
44
45 l_serv_item_flag VARCHAR2(1);
46 l_dtl_line_count NUMBER;
47 l_serv_duraion Number;
48 l_serv_period VARCHAR2(3);
49
50 l_cust_account_id NUMBER := P_OPP_QTE_IN_REC.cust_account_id;
51 l_conc_segments VARCHAR2(40);
52
53 l_copy_notes_flag VARCHAR2(1);
54 l_copy_task_flag VARCHAR2(1);
55 l_copy_att_flag VARCHAR2(1);
56
57 -- Variables to hold values to be passed to ASO_VALIDATE_PRICING_PVT.Validate_Pricing_Order()
58 l_pricing_control_rec ASO_PRICING_INT.Pricing_Control_Rec_Type ;
59 lp_ln_price_attributes_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
60 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl;
61 lp_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Shipment_Tbl;
62 lp_ln_sales_credit_tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_type := ASO_QUOTE_PUB.G_Miss_Sales_Credit_Tbl;
63 lpx_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
64 lpx_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
65 lpx_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
66 lpx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
67 lpx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
68 lpx_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
69
70 -- Variables to hold values to be passed to ASO_QUOTE_PUB.Create_Quote()
71 l_control_rec ASO_QUOTE_PUB.Control_Rec_Type := ASO_QUOTE_PUB.G_MISS_Control_Rec;
72 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_type := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
73 l_hd_price_attributes_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl;
74 l_hd_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type := ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL;
75 l_hd_Shipment_Rec ASO_QUOTE_PUB.Shipment_Rec_Type := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
76 l_qte_line_tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Qte_Line_Tbl;
77 l_qte_line_dtl_tbl ASO_QUOTE_PUB.Qte_Line_dtl_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Qte_Line_dtl_Tbl;
78 l_ln_price_attributes_tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type
79 := ASO_QUOTE_PUB.G_Miss_Price_Attributes_Tbl;
80 l_ln_shipment_tbl ASO_QUOTE_PUB.Shipment_Tbl_Type := ASO_QUOTE_PUB.G_Miss_Shipment_Tbl;
81 l_ln_sales_credit_tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_type := ASO_QUOTE_PUB.G_Miss_Sales_Credit_Tbl;
82 lx_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type := ASO_QUOTE_PUB.G_Miss_Qte_Header_Rec;
83 lx_out_Qte_Header_Rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
84 lx_Qte_Line_Tbl ASO_QUOTE_PUB.Qte_Line_Tbl_Type;
85 lx_Qte_Line_Dtl_Tbl ASO_QUOTE_PUB.Qte_Line_Dtl_Tbl_Type;
86 lx_Hd_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
87 lx_Hd_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
88 lx_Hd_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
89 lx_Hd_Shipment_Rec ASO_QUOTE_PUB.Shipment_Rec_Type;
90 lx_Hd_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
91 lx_Hd_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
92 lx_Hd_Attr_Ext_tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
93 lx_Hd_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
94 lx_Hd_Quote_Party_tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
95 lx_Line_Attr_Ext_Tbl ASO_QUOTE_PUB.Line_Attribs_Ext_Tbl_Type;
96 lx_Line_Rltship_tbl ASO_QUOTE_PUB.Line_Rltship_Tbl_Type;
97 lx_Price_Adjustment_Tbl ASO_QUOTE_PUB.Price_Adj_Tbl_Type;
98 lx_Price_Adj_Attr_Tbl ASO_QUOTE_PUB.Price_Adj_Attr_Tbl_Type;
99 lx_Price_Adj_Rltship_Tbl ASO_QUOTE_PUB.Price_Adj_Rltship_Tbl_Type;
100 lx_Ln_Price_Attributes_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
101 lx_Ln_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type;
102 lx_Ln_Shipment_Tbl ASO_QUOTE_PUB.Shipment_Tbl_Type;
103 lx_Ln_Freight_Charge_Tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
104 lx_Ln_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
105 lx_Ln_Sales_Credit_Tbl ASO_QUOTE_PUB.Sales_Credit_Tbl_Type;
106 lx_Ln_Quote_Party_tbl ASO_QUOTE_PUB.Quote_Party_Tbl_Type;
107 lx_Qte_Template_Tbl ASO_QUOTE_PUB.Template_Tbl_Type;
108 l_last_update_date Date;
109 l_Related_Obj_Tbl ASO_QUOTE_PUB.Related_Obj_Tbl_Type;
110 lx_Related_Obj_Tbl ASO_QUOTE_PUB.Related_Obj_Tbl_Type;
111
112 -- Variables for creating object relationship between opportunity and quote
113 l_related_obj_rec ASO_QUOTE_PUB.Related_Obj_Rec_Type := ASO_QUOTE_PUB.G_MISS_RELATED_OBJ_REC;
114 lx_related_object_id NUMBER;
115 l_payment_term_id NUMBER;
116
117 -- Variables for Sales Team (Security)
118 l_qte_access_rec ASO_QUOTE_PUB.Qte_Access_Rec_Type := ASO_QUOTE_PUB.G_MISS_QTE_ACCESS_REC;
119 l_qte_access_tbl ASO_QUOTE_PUB.Qte_Access_Tbl_Type := ASO_QUOTE_PUB.G_MISS_QTE_ACCESS_TBL;
120 lx_qte_access_tbl ASO_QUOTE_PUB.Qte_Access_Tbl_Type := ASO_QUOTE_PUB.G_MISS_QTE_ACCESS_TBL;
121 l_primary_uom_code VARCHAR2(3);
122
123 -- Dummy Variable
124 l_dummy VARCHAR(1) := NULL;
125 l_party_type VARCHAR2(30);
126 l_party_site_id Number;
127 x_valid VARCHAR(1);
128
129 -- Recurring charges Change
130 l_charge_periodicity_code VARCHAR(3);
131
132
133 l_def_control_rec ASO_DEFAULTING_INT.Control_Rec_Type := ASO_DEFAULTING_INT.G_MISS_CONTROL_REC;
134 l_db_object_name VARCHAR2(30);
135 l_hd_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type := ASO_QUOTE_PUB.G_MISS_Payment_REC;
136 l_hd_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type := ASO_QUOTE_PUB.G_MISS_Tax_Detail_REC;
137 l_hd_misc_rec ASO_DEFAULTING_INT.Header_Misc_Rec_Type
138 := ASO_DEFAULTING_INT.G_MISS_HEADER_MISC_REC;
139 lx_hd_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
140 lx_hd_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
141 lx_hd_misc_rec ASO_DEFAULTING_INT.Header_Misc_Rec_Type;
142 lx_quote_line_rec ASO_QUOTE_PUB.Qte_Line_Rec_Type;
143 lx_ln_misc_rec ASO_DEFAULTING_INT.Line_Misc_Rec_Type;
144 lx_ln_shipment_rec ASO_QUOTE_PUB.Shipment_Rec_Type;
145 lx_ln_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
146 lx_ln_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
147 lx_changed_flag VARCHAR2(1);
148 l_hd_tax_detail_tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type;
149
150 l_header_Shipment_Rec ASO_QUOTE_PUB.Shipment_Rec_Type := ASO_QUOTE_PUB.G_Miss_Shipment_rec;
151 l_header_Payment_Tbl ASO_QUOTE_PUB.Payment_Tbl_Type := ASO_QUOTE_PUB.G_MISS_PAYMENT_TBL;
152 l_header_Tax_Detail_Tbl ASO_QUOTE_PUB.Tax_Detail_Tbl_Type := ASO_QUOTE_PUB.G_MISS_Tax_Detail_Tbl;
153
154
155 -- Cursors to fetch data from OSO Tables
156
157 -- AS_LEADS_ALL
158 CURSOR C_lead (p_lead_id NUMBER) IS
159 SELECT offer_id
160 FROM as_leads_all
161 WHERE lead_id = p_lead_id;
162
163 l_lead_rec c_lead%ROWTYPE;
164
165 -- AS_LEAD_LINES_ALL
166 CURSOR C_lead_line (p_lead_id NUMBER) IS
167 SELECT lead_line_id,
168 inventory_item_id,
169 organization_id,
170 uom_code,
171 NVL(quantity, 1) quantity,
172 offer_id,
173 forecast_date
174 FROM as_lead_lines_all
175 WHERE lead_id = p_lead_id
176 AND inventory_item_id IS NOT NULL
177 AND organization_id IS NOT NULL;
178
179 -- AS_SALES_CREDITS
180 CURSOR C_sales_credits (p_lead_id NUMBER, p_lead_line_id NUMBER) IS
181 SELECT salesforce_id,
182 salesgroup_id,
183 credit_type_id,
184 credit_percent
185 FROM as_sales_credits
186 WHERE lead_id = p_lead_id
187 AND lead_line_id = p_lead_line_id;
188
189 CURSOR C_phone (p_party_id number) is
190 SELECT contact_point_id
191 FROM hz_contact_points
192 WHERE owner_table_id = p_party_id
193 AND owner_table_name = 'HZ_PARTIES'
194 AND contact_point_type = 'PHONE'
195 AND status = 'A'
196 AND primary_flag = 'Y';
197
198 CURSOR C_campaign_id (l_sc_id NUMBER) IS
199 SELECT source_code_for_id
200 FROM ams_source_codes
201 WHERE source_code_id = l_sc_id;
202
203 CURSOR c_conc_segments (l_inventory_item_id NUMBER) IS
204 SELECT concatenated_segments
205 FROM MTL_SYSTEM_ITEMS_VL
206 WHERE inventory_item_id = l_inventory_item_id;
207
208 CURSOR C_sales_team (l_lead_id NUMBER) IS
209 SELECT salesforce_id,
210 sales_group_id,
211 request_id,
212 program_application_id,
213 program_id,
214 program_update_date,
215 freeze_flag,
216 team_leader_flag,
217 created_by_tap_flag,
218 attribute_category,
219 attribute1,
220 attribute2,
221 attribute3,
222 attribute4,
223 attribute5,
224 attribute6,
225 attribute7,
226 attribute8,
227 attribute9,
228 attribute10,
229 attribute11,
230 attribute12,
231 attribute13,
232 attribute14,
233 attribute15
234 FROM AS_ACCESSES_ALL
235 WHERE lead_id = l_lead_id;
236
237 -- bug 5534788, fixed cursor so that freeze flag
238 -- is carried over from opp to qte correctly
239 Cursor C_opp_owner(p_lead_id NUMBER,p_qte_number Number) IS
240 SELECT a.freeze_flag,c.access_id
241 FROM AS_ACCESSES_ALL a, ASO_QUOTE_HEADERS_ALL B,aso_quote_accesses c
242 WHERE a.lead_id = p_lead_id
243 AND b.quote_number = p_qte_number
244 AND a.salesforce_id = c.resource_id
245 AND b.quote_number =c.quote_number;
246
247 CURSOR c_base_valid(p_item_id IN Number,p_organization_id IN Number) IS
248 SELECT primary_uom_code
249 FROM MTL_SYSTEM_ITEMS_B
250 WHERE inventory_item_id = p_item_id
251 AND organization_id = p_organization_id;
252
253 CURSOR c_pay_term_aggrement(p_agreement_id IN Number) IS
254 SELECT term_id
255 FROM oe_agreements
256 WHERE agreement_id = p_agreement_id;
257
258 CURSOR c_pay_term_acct(p_cust_account_id IN Number) IS
259 SELECT hcp.standard_terms
260 FROM hz_cust_accounts hca,hz_customer_profiles hcp
261 WHERE hca.cust_account_id = p_cust_account_id
262 AND hcp.cust_account_id = hca.cust_account_id
263 AND nvl(hcp.status,'A') = 'A';
264
265 CURSOR c_primary_address(p_party_id IN Number,p_site_use_type VARCHAR2) IS
266 SELECT hps.party_site_id
267 FROM hz_party_sites hps, hz_party_site_uses hpsu
268 WHERE hps.party_id= p_party_id
269 AND hps.status='A'
270 AND hps.party_site_id= hpsu.party_site_id
271 AND hpsu.site_use_type= p_site_use_type
272 AND hpsu.primary_per_type='Y'
273 AND hpsu.status='A';
274
275 CURSOR c_identifying_address(p_party_id IN Number) IS
276 SELECT party_site_id
277 FROM hz_party_sites
278 WHERE party_id= p_party_id
279 AND status='A'
280 AND identifying_address_flag='Y';
281
282 -- Recurring charges Change
283
284 CURSOR c_periodicity(p_inventory_item_id IN Number, p_organization_id IN Number) IS
285 SELECT charge_periodicity_code
286 FROM mtl_system_items_b
287 WHERE inventory_item_id = p_inventory_item_id
288 AND organization_id = p_organization_id;
289
290 CURSOR c_serv_item (l_inventory_item_id NUMBER, l_organization_id NUMBER) IS
291 SELECT service_item_flag,service_duration,service_duration_period_code
292 FROM MTL_SYSTEM_ITEMS_VL
293 WHERE inventory_item_id = l_inventory_item_id
294 AND organization_id = l_organization_id
295 AND customer_order_enabled_flag = 'Y'
296 AND bom_item_type <> 2
297 AND NVL(start_date_active, SYSDATE) <= SYSDATE
298 AND NVL(end_date_active, SYSDATE) >= SYSDATE;
299
300 l_master_organization_id NUMBER;
301 l_profile_val varchar2(30) := null;
302 l_add_service varchar2(1);
303 l_add_line varchar2(1);
304
305 BEGIN
306
307 -- Standard Start of API savepoint
308 SAVEPOINT Create_Qte_Opportunity_pub;
309
310 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
311
312 IF aso_debug_pub.g_debug_flag = 'Y' THEN
313 aso_debug_pub.add('****** Start of Create_Qte_Opportunity API ******', 1, 'Y');
314 END IF;
315
316 -- Standard call to check for call compatibility.
317 IF NOT FND_API.Compatible_API_Call(
318 L_API_VERSION,
319 P_API_VERSION_NUMBER,
320 L_API_NAME,
321 G_PKG_NAME
322 ) THEN
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 END IF;
325
326 -- Initialize message list if p_init_msg_list is set to TRUE.
327 IF FND_API.To_Boolean(p_init_msg_list) THEN
328 FND_Msg_Pub.initialize;
329 END IF;
330
331 --Procedure added by Anoop Rajan on 30/09/2005 to print login details
332 IF aso_debug_pub.g_debug_flag = 'Y' THEN
333 aso_debug_pub.add('Before call to printing login info details', 1, 'Y');
334 ASO_UTILITY_PVT.print_login_info;
335 aso_debug_pub.add('After call to printing login info details', 1, 'Y');
336 END IF;
337
338 -- Change Done By Girish
339 -- Procedure added to validate the operating unit
340 ASO_VALIDATE_PVT.VALIDATE_OU(P_Quote_Header_Rec);
341
342
343 -- Initialize API return status to success
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345
346 --defaulting framework begin
347
348 l_qte_header_rec := P_Quote_Header_Rec;
349 l_control_rec := p_control_rec;
350
351 IF aso_debug_pub.g_debug_flag = 'Y' THEN
352 aso_debug_pub.add('Create_Qte_Opportunity - before defaulting framework', 1, 'Y');
353 aso_debug_pub.add('Create_Qte_Opportunity - populate defaulting control record from header control record',
354 1, 'Y');
355 END IF ;
356
357 --Yogeshwar(MOAC)
358 if (l_qte_header_rec.CUST_PARTY_ID IS NULL OR l_qte_header_rec.CUST_PARTY_ID = FND_API.G_MISS_NUM ) THEN
359 l_qte_header_rec.ORG_ID := P_OPP_QTE_IN_REC.ORG_ID;
360 End if;
361 --Yogeshwar(MOAC)
362
363
364 l_def_control_rec.Dependency_Flag := FND_API.G_FALSE;
365 l_def_control_rec.Defaulting_Flag := l_control_rec.Defaulting_Flag;
366 l_def_control_rec.Application_Type_Code := l_control_rec.Application_Type_Code;
367 l_def_control_rec.Defaulting_Flow_Code := 'CREATE';
368
369 IF aso_debug_pub.g_debug_flag = 'Y' THEN
370 aso_debug_pub.add('Defaulting_Fwk_Flag - '||l_control_rec.Defaulting_Fwk_Flag, 1, 'Y');
371 aso_debug_pub.add('Dependency_Flag - '||l_def_control_rec.Dependency_Flag, 1, 'Y');
372 aso_debug_pub.add('Defaulting_Flag - '||l_def_control_rec.Defaulting_Flag, 1, 'Y');
373 aso_debug_pub.add('Application_Type_Code - '||l_def_control_rec.Application_Type_Code, 1, 'Y');
374 aso_debug_pub.add('Defaulting_Flow_Code - '||l_def_control_rec.Defaulting_Flow_Code, 1, 'Y');
375 END IF ;
376
377 IF l_def_control_rec.application_type_code = 'QUOTING HTML'
378 OR l_def_control_rec.application_type_code = 'QUOTING FORM' THEN
379 l_db_object_name := ASO_QUOTE_HEADERS_PVT.G_QUOTE_HEADER_DB_NAME;
380 ELSE
381 l_control_rec.Defaulting_Fwk_Flag := 'N';
382 END IF;
383
384 IF aso_debug_pub.g_debug_flag = 'Y' THEN
385 aso_debug_pub.add('Create_Qte_Opportunity - Pick '||l_db_object_name
386 ||' based on calling application '||l_def_control_rec.application_type_code, 1, 'Y');
387 END IF ;
388
389 /*
390 -- In create quote, it never deaults any line level records.
391 IF l_control_rec.defaulting_fwk_flag = 'Y' THEN
392
393 IF aso_debug_pub.g_debug_flag = 'Y' THEN
394 aso_debug_pub.add('Create_Qte_Opportunity - Calling default_entity', 1, 'Y');
395 END IF ;
396
397 ASO_DEFAULTING_INT.Default_Entity (
398 p_api_version => 1.0
399 , p_control_rec => l_def_control_rec
400 , p_database_object_name => l_db_object_name
401 , p_quote_header_rec => P_Quote_Header_Rec
402 , p_header_misc_rec => l_hd_misc_rec
403 , p_header_shipment_rec => P_header_shipment_rec
404 , p_header_payment_rec => P_header_payment_rec
405 , p_header_tax_detail_rec => P_header_tax_detail_rec
406 , x_quote_header_rec => lx_qte_header_rec
407 , x_header_misc_rec => lx_hd_misc_rec
408 , x_header_shipment_rec => lx_hd_shipment_rec
409 , x_header_payment_rec => lx_hd_payment_rec
410 , x_header_tax_detail_rec => lx_hd_tax_detail_rec
411 , x_quote_line_rec => lx_quote_line_rec
412 , x_line_misc_rec => lx_ln_misc_rec
413 , x_line_shipment_rec => lx_ln_shipment_rec
414 , x_line_payment_rec => lx_ln_payment_rec
415 , x_line_tax_detail_rec => lx_ln_tax_detail_rec
416 , x_changed_flag => lx_changed_flag
417 , x_return_status => x_return_status
418 , x_msg_count => x_msg_count
419 , x_msg_data => x_msg_data
420 );
421
422 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
423 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
424 FND_MESSAGE.Set_Name('ASO', 'ASO_API_ERROR_DEFAULTING');
425 FND_MSG_PUB.ADD;
426 END IF;
427
428 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
431 RAISE FND_API.G_EXC_ERROR;
432 END IF;
433 END IF;
434
435 l_qte_header_rec := lx_qte_header_rec;
436
437 IF ASO_QUOTE_HEADERS_PVT.Shipment_Null_Rec_Exists(lx_hd_shipment_rec, l_db_object_name) THEN
438 l_hd_shipment_rec := lx_hd_shipment_rec;
439 l_hd_shipment_rec.operation_code := 'CREATE';
440 END IF;
441
442 IF ASO_QUOTE_HEADERS_PVT.Payment_Null_Rec_Exists(lx_hd_payment_rec, l_db_object_name) THEN
443 l_hd_Payment_Tbl(1) := lx_hd_payment_rec;
444 l_hd_Payment_Tbl(1).operation_code := 'CREATE';
445 END IF;
446
447 IF ASO_QUOTE_HEADERS_PVT.Tax_Detail_Null_Rec_Exists(lx_hd_tax_detail_rec, l_db_object_name) THEN
448 l_hd_tax_detail_tbl(1) := lx_hd_tax_detail_rec;
449 l_hd_tax_detail_tbl(1).operation_code := 'CREATE';
450 END IF;
451
452 END IF;
453 */
454 -- defaulting framework end
455
456 l_hd_shipment_rec := P_header_shipment_rec;
457 l_hd_Payment_Tbl(1) := P_header_payment_rec;
458
459 IF aso_utility_pvt.tax_rec_exists(P_header_tax_detail_rec) then
460 l_hd_tax_detail_tbl(1) := P_header_tax_detail_rec;
461
462 IF aso_debug_pub.g_debug_flag = 'Y' THEN
463 aso_debug_pub.add('ASO_OPP_QTE_PUB: Assigning the header tax record',1,'N');
464 aso_debug_pub.add('ASO_OPP_QTE_PUB: Header tax_exempt_flag: '||P_header_tax_detail_rec.tax_exempt_flag,1,'N');
465 END IF ;
466 end if;
467
468 -- API body
469 FOR l_lead_rec IN c_lead(P_OPP_QTE_IN_REC.OPPORTUNITY_ID) LOOP
470
471 IF aso_debug_pub.g_debug_flag = 'Y' THEN
472 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.OPPORTUNITY_ID:'
473 ||P_OPP_QTE_IN_REC.OPPORTUNITY_ID, 1, 'N');
474 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.QUOTE_NUMBER:'
475 ||P_OPP_QTE_IN_REC.QUOTE_NUMBER, 1, 'N');
476 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.QUOTE_NAME:'
477 ||P_OPP_QTE_IN_REC.QUOTE_NAME, 1, 'N');
478 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID:'
479 ||P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID, 1, 'N');
480 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.RESOURCE_ID:'
481 ||P_OPP_QTE_IN_REC.RESOURCE_ID, 1, 'N');
482 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.SOLD_TO_CONTACT_ID:'
483 ||P_OPP_QTE_IN_REC.SOLD_TO_CONTACT_ID, 1, 'N');
484 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.SOLD_TO_PARTY_SITE_ID:'
485 ||P_OPP_QTE_IN_REC.SOLD_TO_PARTY_SITE_ID, 1, 'N');
486 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.PRICE_LIST_ID:'
487 ||P_OPP_QTE_IN_REC.PRICE_LIST_ID, 1, 'N');
488 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.RESOURCE_GRP_ID:'
489 ||P_OPP_QTE_IN_REC.RESOURCE_GRP_ID, 1, 'N');
490 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.CHANNEL_CODE:'
491 ||P_OPP_QTE_IN_REC.CHANNEL_CODE, 1, 'N');
492 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.ORDER_TYPE_ID:'
493 ||P_OPP_QTE_IN_REC.ORDER_TYPE_ID, 1, 'N');
494 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.AGREEMENT_ID:'
495 ||P_OPP_QTE_IN_REC.AGREEMENT_ID, 1, 'N');
496 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.CONTRACT_TEMPLATE_ID:'
497 ||P_OPP_QTE_IN_REC.CONTRACT_TEMPLATE_ID, 1, 'N');
498 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.CONTRACT_TEMPLATE_MAJOR_VER:'
499 ||P_OPP_QTE_IN_REC.CONTRACT_TEMPLATE_MAJOR_VER, 1, 'N');
500 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.CURRENCY_CODE:'
501 ||P_OPP_QTE_IN_REC.CURRENCY_CODE, 1, 'N');
502 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.MARKETING_SOURCE_CODE_ID:'
503 ||P_OPP_QTE_IN_REC.MARKETING_SOURCE_CODE_ID, 1, 'N');
504 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_lead_rec.offer_id:'||l_lead_rec.offer_id, 1, 'N');
505 END IF;
506
507
508 -- Set Control Record
509 l_control_rec.pricing_request_type := 'ASO';
510
511 if p_control_rec.header_pricing_event = 'PRICE' then
512 l_control_rec.header_pricing_event := 'ORDER';
513 else
514 l_control_rec.header_pricing_event := 'BATCH';
515 end if;
516
517 l_control_rec.price_mode := 'ENTIRE_QUOTE';
518 l_control_rec.quote_source := 'OPP_QUOTE';
519 l_qte_header_rec.quote_source_code := P_SOURCE_CODE;
520
521 IF l_control_rec.defaulting_fwk_flag <> 'Y' THEN
522 l_qte_header_rec.quote_number := P_OPP_QTE_IN_REC.quote_number;
523 l_qte_header_rec.resource_id := P_OPP_QTE_IN_REC.resource_id;
524 l_qte_header_rec.price_list_id := P_OPP_QTE_IN_REC.price_list_id;
525 l_qte_header_rec.resource_grp_id := P_OPP_QTE_IN_REC.resource_grp_id;
526 l_qte_header_rec.order_type_id := P_OPP_QTE_IN_REC.order_type_id;
527 l_qte_header_rec.contract_id := P_OPP_QTE_IN_REC.agreement_id;
528 l_qte_header_rec.contract_template_id := P_OPP_QTE_IN_REC.contract_template_id;
529 l_qte_header_rec.contract_template_major_ver := P_OPP_QTE_IN_REC.contract_template_major_ver;
530 l_qte_header_rec.QUOTE_EXPIRATION_DATE := P_OPP_QTE_IN_REC.QUOTE_EXPIRATION_DATE;
531
532 IF P_OPP_QTE_IN_REC.quote_name <> FND_API.G_MISS_CHAR THEN
533 l_qte_header_rec.quote_name := P_OPP_QTE_IN_REC.quote_name;
534 END IF;
535
536 IF P_OPP_QTE_IN_REC.sold_to_contact_id <> FND_API.G_MISS_NUM THEN
537 l_qte_header_rec.party_id := P_OPP_QTE_IN_REC.sold_to_contact_id;
538 Else
539 l_qte_header_rec.party_id := P_OPP_QTE_IN_REC.cust_party_id;
540 END IF;
541
542 END IF;
543
544 -- Phone # population
545 IF l_control_rec.defaulting_fwk_flag <> 'Y' THEN
546 IF l_qte_header_rec.party_id <> FND_API.G_MISS_NUM THEN
547 FOR l_phone_rec IN C_phone(l_qte_header_rec.party_id) LOOP
548 l_qte_header_rec.phone_id := l_phone_rec.contact_point_id;
549 END LOOP;
550 END IF;
551
552 IF P_OPP_QTE_IN_REC.sold_to_party_site_id <> FND_API.G_MISS_NUM THEN
553 Address_Validation(
554 p_party_site_id => P_OPP_QTE_IN_REC.sold_to_party_site_id ,
555 p_use_type => 'SOLD_TO',
556 x_valid => x_valid,
557 X_RETURN_STATUS => X_RETURN_STATUS,
558 X_MSG_COUNT => X_MSG_COUNT,
559 X_MSG_DATA => X_MSG_DATA) ;
560
561 IF aso_debug_pub.g_debug_flag = 'Y' THEN
562 aso_debug_pub.add('ASO_OPP_QTE_PUB: sold_to_party_site_id:'
563 ||P_OPP_QTE_IN_REC.sold_to_party_site_id, 1, 'N');
564 aso_debug_pub.add('ASO_OPP_QTE_PUB: valid flag:Sold_To:'||x_valid, 1, 'N');
565 END IF;
566
567
568 IF x_valid = 'Y' THEN
569 l_qte_header_rec.sold_to_party_site_id := P_OPP_QTE_IN_REC.sold_to_party_site_id;
570 ELSE
571 l_qte_header_rec.sold_to_party_site_id := FND_API.G_MISS_NUM;
572 END IF;
573
574 END IF;
575
576 IF P_OPP_QTE_IN_REC.channel_code <> FND_API.G_MISS_CHAR THEN
577 l_qte_header_rec.sales_channel_code := P_OPP_QTE_IN_REC.channel_code;
578 END IF;
579
580 IF P_OPP_QTE_IN_REC.currency_code <> FND_API.G_MISS_CHAR THEN
581 l_qte_header_rec.currency_code := P_OPP_QTE_IN_REC.currency_code;
582 END IF;
583
584 IF P_OPP_QTE_IN_REC.marketing_source_code_id <> FND_API.G_MISS_NUM THEN
585 l_qte_header_rec.marketing_source_code_id := P_OPP_QTE_IN_REC.marketing_source_code_id;
586 END IF;
587
588 IF P_OPP_QTE_IN_REC.PRICING_STATUS_INDICATOR <> FND_API.G_MISS_CHAR THEN
589 l_qte_header_rec.PRICING_STATUS_INDICATOR := P_OPP_QTE_IN_REC.PRICING_STATUS_INDICATOR;
590 END IF;
591
592 IF P_OPP_QTE_IN_REC.TAX_STATUS_INDICATOR <> FND_API.G_MISS_CHAR THEN
593 l_qte_header_rec.TAX_STATUS_INDICATOR := P_OPP_QTE_IN_REC.TAX_STATUS_INDICATOR;
594 END IF;
595
596 IF P_OPP_QTE_IN_REC.PRICE_UPDATED_DATE <> FND_API.G_MISS_DATE THEN
597 l_qte_header_rec.PRICE_UPDATED_DATE := P_OPP_QTE_IN_REC.PRICE_UPDATED_DATE;
598 END IF;
599
600 IF P_OPP_QTE_IN_REC.TAX_UPDATED_DATE <> FND_API.G_MISS_DATE THEN
601 l_qte_header_rec.TAX_UPDATED_DATE := P_OPP_QTE_IN_REC.TAX_UPDATED_DATE;
602 END IF;
603
604
605 IF aso_debug_pub.g_debug_flag = 'Y' THEN
606 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.quote_header_id:'
607 || l_qte_header_rec.quote_header_id, 1, 'N');
608 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.quote_source_code:'
609 || l_qte_header_rec.quote_source_code, 1, 'N');
610 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.quote_number:'
611 || l_qte_header_rec.quote_number, 1, 'N');
612 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.resource_id:'
613 || l_qte_header_rec.resource_id, 1, 'N');
614 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.price_list_id:'
615 || l_qte_header_rec.price_list_id, 1, 'N');
616 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.resource_grp_id:'
617 || l_qte_header_rec.resource_grp_id, 1, 'N');
618 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.order_type_id:'
619 || l_qte_header_rec.order_type_id, 1, 'N');
620 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.contract_id:'
621 || l_qte_header_rec.contract_id, 1, 'N');
622 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.contract_template_id:'
623 || l_qte_header_rec.contract_template_id, 1, 'N');
624 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.contract_template_major_ver: '
625 || l_qte_header_rec.contract_template_major_ver, 1, 'N');
626 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.quote_name:'
627 || l_qte_header_rec.quote_name, 1, 'N');
628 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.party_id:'
629 || l_qte_header_rec.party_id, 1, 'N');
630 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.phone_id:'
631 || l_qte_header_rec.phone_id, 1, 'N');
632 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.cust_account_id:'
633 || l_qte_header_rec.cust_account_id, 1, 'N');
634 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.sold_to_party_site_id:'
635 || l_qte_header_rec.sold_to_party_site_id, 1, 'N');
636 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.sales_channel_code:'
637 || l_qte_header_rec.sales_channel_code, 1, 'N');
638 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.currency_code:'
639 || l_qte_header_rec.currency_code, 1, 'N');
640 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_header_rec.marketing_source_code_id:'
641 || l_qte_header_rec.marketing_source_code_id, 1, 'N');
642 END IF;
643
644 l_qte_header_rec.cust_party_id := P_OPP_QTE_IN_REC.cust_party_id ;
645 l_qte_header_rec.invoice_to_cust_party_id := P_OPP_QTE_IN_REC.cust_party_id ;
646
647 IF P_OPP_QTE_IN_REC.cust_party_id <> FND_API.G_MISS_NUM
648 AND P_OPP_QTE_IN_REC.cust_party_id IS NOT NULL THEN
649 l_hd_Shipment_Rec.operation_code := 'CREATE' ;
650 l_hd_Shipment_Rec.ship_to_cust_party_id := P_OPP_QTE_IN_REC.cust_party_id ;
651 END IF;
652
653 OPEN c_pay_term_aggrement(P_OPP_QTE_IN_REC.agreement_id);
654 FETCH c_pay_term_aggrement INTO l_payment_term_id;
655
656 IF c_pay_term_aggrement%NOTFOUND THEN
657 OPEN c_pay_term_acct(P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID);
658 FETCH c_pay_term_acct INTO l_payment_term_id;
659 IF c_pay_term_acct%NOTFOUND THEN
660 l_payment_term_id := null;
661 end if;
662 close c_pay_term_acct;
663 END IF;
664 close c_pay_term_aggrement;
665
666 IF aso_debug_pub.g_debug_flag = 'Y' THEN
667 aso_debug_pub.add('ASO_OPP_QTE_PUB: payment_term_id:'||l_payment_term_id, 1, 'N');
668 END IF;
669
670 IF l_payment_term_id <> FND_API.G_MISS_NUM and l_payment_term_id IS NOT NULL THEN
671 l_hd_Payment_Tbl(1).operation_code := 'CREATE';
672 l_hd_Payment_Tbl(1).payment_term_id := l_payment_term_id;
673 END IF;
674
675 IF aso_debug_pub.g_debug_flag = 'Y' THEN
676 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_OPP_QTE_IN_REC.cust_party_id:'
677 ||P_OPP_QTE_IN_REC.cust_party_id, 1, 'N');
678 END IF;
679
680 IF P_OPP_QTE_IN_REC.cust_party_id <> FND_API.G_MISS_NUM
681 AND P_OPP_QTE_IN_REC.cust_party_id IS NOT NULL THEN
682 OPEN c_primary_address(P_OPP_QTE_IN_REC.cust_party_id,'BILL_TO');
683 FETCH c_primary_address INTO l_party_site_id;
684
685 IF c_primary_address%NOTFOUND THEN
686 OPEN c_identifying_address(P_OPP_QTE_IN_REC.cust_party_id);
687 FETCH c_identifying_address INTO l_party_site_id;
688 IF c_identifying_address%NOTFOUND THEN
689 l_party_site_id := null;
690 END IF;
691 close c_identifying_address;
692 END IF;
693 close c_primary_address;
694 END IF;
695
696 IF l_party_site_id <> FND_API.G_MISS_NUM and l_party_site_id IS NOT NULL THEN
697 Address_Validation(
698 p_party_site_id => l_party_site_id ,
699 p_use_type => 'BILL_TO',
700 x_valid => x_valid,
701 X_RETURN_STATUS => X_RETURN_STATUS,
702 X_MSG_COUNT => X_MSG_COUNT,
703 X_MSG_DATA => X_MSG_DATA) ;
704
705 IF aso_debug_pub.g_debug_flag = 'Y' THEN
706 aso_debug_pub.add('ASO_OPP_QTE_PUB: invoice_to_party_site_id:'||l_party_site_id, 1, 'N');
707 aso_debug_pub.add('ASO_OPP_QTE_PUB: valid flag:Invoice_To:'||x_valid, 1, 'N');
708 END IF;
709
710 IF x_valid = 'Y' THEN
711 l_qte_header_rec.invoice_to_party_site_id := l_party_site_id;
712 else
713 l_qte_header_rec.invoice_to_party_site_id := FND_API.G_MISS_NUM;
714 end if;
715
716 END IF;
717
718 IF P_OPP_QTE_IN_REC.cust_party_id <> FND_API.G_MISS_NUM
719 AND P_OPP_QTE_IN_REC.cust_party_id IS NOT NULL THEN
720 OPEN c_primary_address(P_OPP_QTE_IN_REC.cust_party_id,'SHIP_TO');
721 FETCH c_primary_address INTO l_party_site_id;
722
723 IF c_primary_address%NOTFOUND THEN
724 OPEN c_identifying_address(P_OPP_QTE_IN_REC.cust_party_id);
725 FETCH c_identifying_address INTO l_party_site_id;
726 IF c_identifying_address%NOTFOUND THEN
727 l_party_site_id := null;
728 end if;
729 close c_identifying_address;
730 end if;
731 close c_primary_address;
732 END IF;
733
734 IF l_party_site_id <> FND_API.G_MISS_NUM and l_party_site_id IS NOT NULL THEN
735 l_hd_Shipment_Rec.operation_code := 'CREATE';
736 Address_Validation(
737 p_party_site_id => l_party_site_id,
738 p_use_type => 'SHIP_TO',
739 x_valid => x_valid,
740 X_RETURN_STATUS => X_RETURN_STATUS,
741 X_MSG_COUNT => X_MSG_COUNT,
742 X_MSG_DATA => X_MSG_DATA);
743
744
745 IF aso_debug_pub.g_debug_flag = 'Y' THEN
746 aso_debug_pub.add('ASO_OPP_QTE_PUB: ship_to_party_site_id :'||l_party_site_id, 1, 'N');
747 aso_debug_pub.add('ASO_OPP_QTE_PUB: valid flag:Ship_To:'||x_valid, 1, 'N');
748 END IF;
749
750 IF x_valid = 'Y' THEN
751 l_hd_Shipment_Rec.ship_to_party_site_id := l_party_site_id;
752 else
753 l_hd_Shipment_Rec.ship_to_party_site_id := FND_API.G_MISS_NUM;
754 end if;
755 END IF;
756
757 IF aso_debug_pub.g_debug_flag = 'Y' THEN
758 aso_debug_pub.add('ASO_OPP_QTE_PUB: ship_to_party_site_id:'||l_party_site_id, 1, 'N');
759 END IF;
760
761 IF P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID <> FND_API.G_MISS_NUM THEN
762 l_qte_header_rec.cust_account_id := P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID;
763 l_qte_header_rec.invoice_to_cust_account_id := P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID;
764 l_hd_Shipment_Rec.operation_code := 'CREATE' ;
765 l_hd_Shipment_Rec.ship_to_cust_account_id := P_OPP_QTE_IN_REC.CUST_ACCOUNT_ID;
766 END IF;
767
768 END IF;
769
770
771 IF l_lead_rec.offer_id IS NOT NULL AND l_lead_rec.offer_id <> FND_API.G_MISS_NUM THEN
772 FOR l_campaign_rec IN C_campaign_id(l_lead_rec.offer_id) LOOP
773 l_hd_price_attributes_tbl(1).operation_code := 'CREATE';
774 l_hd_price_attributes_tbl(1).pricing_context := 'MODLIST';
775 l_hd_price_attributes_tbl(1).flex_title := 'QP_ATTR_DEFNS_QUALIFIER';
776 l_hd_price_attributes_tbl(1).pricing_attribute1 := l_campaign_rec.source_code_for_id;
777
778 IF aso_debug_pub.g_debug_flag = 'Y' THEN
779 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_hd_price_attributes_tbl(1).pricing_attribute1: '
780 || l_hd_price_attributes_tbl(1).pricing_attribute1, 1, 'N');
781 END IF;
782 END LOOP;
783 END IF;
784
785 If l_control_rec.defaulting_fwk_flag = 'Y' then
786 IF l_qte_header_rec.sold_to_party_site_id <> FND_API.G_MISS_NUM THEN
787 Address_Validation(
788 p_party_site_id => l_qte_header_rec.sold_to_party_site_id ,
789 p_use_type => 'SOLD_TO',
790 x_valid => x_valid,
791 X_RETURN_STATUS => X_RETURN_STATUS,
792 X_MSG_COUNT => X_MSG_COUNT,
793 X_MSG_DATA => X_MSG_DATA);
794
795 IF aso_debug_pub.g_debug_flag = 'Y' THEN
796 aso_debug_pub.add('ASO_OPP_QTE_PUB: sold_to_party_site_id:'
797 ||l_qte_header_rec.sold_to_party_site_id, 1, 'N');
798 aso_debug_pub.add('ASO_OPP_QTE_PUB: valid flag:Sold_To:'||x_valid, 1, 'N');
799 END IF;
800
801 IF x_valid = 'Y' THEN
802 l_qte_header_rec.sold_to_party_site_id := l_qte_header_rec.sold_to_party_site_id;
803 else
804 l_qte_header_rec.sold_to_party_site_id := FND_API.G_MISS_NUM;
805 END IF;
806
807 end if;
808
809 IF l_qte_header_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM
810 AND l_qte_header_rec.invoice_to_party_site_id IS NOT NULL THEN
811 Address_Validation(
812 p_party_site_id => l_qte_header_rec.invoice_to_party_site_id ,
813 p_use_type => 'BILL_TO',
814 x_valid => x_valid,
815 X_RETURN_STATUS => X_RETURN_STATUS,
816 X_MSG_COUNT => X_MSG_COUNT,
817 X_MSG_DATA => X_MSG_DATA);
818
819 IF aso_debug_pub.g_debug_flag = 'Y' THEN
820 aso_debug_pub.add('ASO_OPP_QTE_PUB: invoice_to_party_site_id:'
821 ||l_qte_header_rec.invoice_to_party_site_id, 1, 'N');
822 aso_debug_pub.add('ASO_OPP_QTE_PUB: valid flag:Invoice_To:'||x_valid, 1, 'N');
823 END IF;
824
825 IF x_valid = 'Y' THEN
826 l_qte_header_rec.invoice_to_party_site_id := l_qte_header_rec.invoice_to_party_site_id;
827 else
828 l_qte_header_rec.invoice_to_party_site_id := FND_API.G_MISS_NUM;
829 end if;
830
831 END IF;
832
833 IF l_hd_Shipment_Rec.ship_to_party_site_id <> FND_API.G_MISS_NUM
834 AND l_hd_Shipment_Rec.ship_to_party_site_id IS NOT NULL THEN
835 l_hd_Shipment_Rec.operation_code := 'CREATE' ;
836 Address_Validation(
837 p_party_site_id => l_hd_Shipment_Rec.ship_to_party_site_id,
838 p_use_type => 'SHIP_TO',
839 x_valid => x_valid,
840 X_RETURN_STATUS => X_RETURN_STATUS,
841 X_MSG_COUNT => X_MSG_COUNT,
842 X_MSG_DATA => X_MSG_DATA);
843
844
845 IF aso_debug_pub.g_debug_flag = 'Y' THEN
846 aso_debug_pub.add('ASO_OPP_QTE_PUB: ship_to_party_site_id :'
847 ||l_hd_Shipment_Rec.ship_to_party_site_id, 1, 'N');
848 aso_debug_pub.add('ASO_OPP_QTE_PUB: valid flag:Ship_To:'||x_valid, 1, 'N');
849 END IF;
850
851 IF x_valid = 'Y' THEN
852 l_hd_Shipment_Rec.ship_to_party_site_id := l_hd_Shipment_Rec.ship_to_party_site_id;
853 else
854 l_hd_Shipment_Rec.ship_to_party_site_id := FND_API.G_MISS_NUM;
855 end if;
856
857 END IF;
858
859 END IF;
860
861 l_related_obj_rec.OPERATION_CODE := 'CREATE';
862 l_related_obj_rec.QUOTE_OBJECT_TYPE_CODE := 'HEADER';
863 l_related_obj_rec.QUOTE_OBJECT_ID := lx_qte_header_rec.quote_header_id;
864 l_related_obj_rec.OBJECT_TYPE_CODE := 'LDID';
865 l_related_obj_rec.OBJECT_ID := P_OPP_QTE_IN_REC.OPPORTUNITY_ID;
866 l_related_obj_rec.RELATIONSHIP_TYPE_CODE := 'OPP_QUOTE';
867
868
869 IF aso_debug_pub.g_debug_flag = 'Y' THEN
870 aso_debug_pub.add('ASO_OPP_QTE_PUB: before Create_Object_Relationship: quote_header_id:'
871 || l_related_obj_rec.QUOTE_OBJECT_ID, 1, 'Y');
872 aso_debug_pub.add('ASO_OPP_QTE_PUB: before Create_Object_Relationship: opportunity_id:'
873 || l_related_obj_rec.OBJECT_ID, 1, 'Y');
874 END IF;
875
876 l_Related_Obj_Tbl(l_Related_Obj_Tbl.count+1) := l_related_obj_rec;
877
878
879 l_line_count := 0;
880 l_ln_shipment_count := 0;
881 l_ln_sales_credit_count := 0;
882 l_ln_price_attr_count := 0;
883
884 ASO_QUOTE_PUB.Create_Quote(
885 P_Api_Version_Number => 1.0,
886 P_Init_Msg_List => FND_API.G_FALSE,
887 P_Commit => FND_API.G_FALSE,
888 p_control_rec => l_control_rec,
889 p_qte_header_rec => l_qte_header_rec,
890 P_hd_Price_Attributes_Tbl => l_hd_price_attributes_tbl,
891 P_hd_Payment_Tbl => l_hd_Payment_Tbl,
892 P_hd_Shipment_Rec => l_hd_Shipment_Rec,
893 P_hd_tax_detail_Tbl => l_hd_Tax_Detail_Tbl,
894 P_Related_Obj_Tbl => l_Related_Obj_Tbl,
895 X_Qte_Header_Rec => lx_Qte_Header_Rec,
896 X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
897 X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
898 X_Hd_Price_Attributes_Tbl => lx_Hd_Price_Attributes_Tbl,
899 X_Hd_Payment_Tbl => lx_Hd_Payment_Tbl,
900 X_Hd_Shipment_Rec => lx_Hd_Shipment_Rec,
901 X_Hd_Freight_Charge_Tbl => lx_Hd_Freight_Charge_Tbl,
902 X_Hd_Tax_Detail_Tbl => lx_Hd_Tax_Detail_Tbl,
903 X_Hd_Attr_Ext_tbl => lx_Hd_Attr_Ext_tbl,
904 X_Hd_Sales_Credit_Tbl => lx_Hd_Sales_Credit_Tbl,
905 X_Hd_Quote_Party_Tbl => lx_Hd_Quote_Party_tbl,
906 X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
907 X_Line_rltship_tbl => lx_Line_Rltship_Tbl,
908 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
909 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
910 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
911 X_Ln_Price_Attributes_Tbl => lx_Ln_Price_Attributes_Tbl,
912 X_Ln_Payment_Tbl => lx_Ln_Payment_Tbl,
913 X_Ln_Shipment_Tbl => lx_Ln_Shipment_Tbl,
914 X_Ln_Freight_Charge_Tbl => lx_Ln_Freight_Charge_Tbl,
915 X_Ln_Tax_Detail_Tbl => lx_Ln_Tax_Detail_Tbl,
916 X_Ln_Sales_Credit_Tbl => lx_Ln_Sales_Credit_Tbl,
917 X_Ln_Quote_Party_Tbl => lx_Ln_Quote_Party_tbl,
918 X_Qte_Access_Tbl => lx_Qte_Access_Tbl,
919 x_Template_Tbl => lx_Qte_Template_Tbl,
920 x_Related_Obj_Tbl => lx_Related_Obj_Tbl,
921 X_Return_Status => x_return_status,
922 X_Msg_Count => x_msg_count,
923 X_Msg_Data => x_msg_data
924 );
925
926 IF aso_debug_pub.g_debug_flag = 'Y' THEN
927 aso_debug_pub.add('ASO_OPP_QTE_PUB: after Create_Quote: x_return_status: '|| x_return_status, 1, 'Y');
928 END IF;
929
930 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
931 RAISE FND_API.G_EXC_ERROR;
932 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
933 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
934 END IF;
935
936 FOR l_lead_line_rec IN c_lead_line(P_OPP_QTE_IN_REC.OPPORTUNITY_ID) LOOP
937
938 IF (l_lead_line_rec.uom_code IS NULL) OR (l_lead_line_rec.uom_code = FND_API.G_MISS_CHAR) THEN
939 IF aso_debug_pub.g_debug_flag = 'Y' THEN
940 aso_debug_pub.add('ASO_OPP_QTE_PUB: NO UOM Passed from Opportunity', 1, 'N');
941 END IF;
942
943 OPEN c_base_valid(l_lead_line_rec.inventory_item_id,l_lead_line_rec.organization_id);
944 FETCH c_base_valid INTO l_primary_uom_code;
945 IF c_base_valid%NOTFOUND THEN
946 IF aso_debug_pub.g_debug_flag = 'Y' THEN
947 aso_debug_pub.add('ASO_OPP_QTE_PUB:IF c_base_valid%NOTFOUND invitem'
948 ||l_lead_line_rec.inventory_item_id, 1, 'N');
949 aso_debug_pub.add('ASO_OPP_QTE_PUB: UOM from MTL_SYSTEMS passed to pricing', 1, 'N');
950 END IF;
951
952 FND_MESSAGE.Set_Name('ASO', 'API_INVALID_ID');
953 FND_MESSAGE.Set_Token('COLUMN', 'UOM_CODE', FALSE);
954 FND_MSG_PUB.ADD;
955 END IF;
956 l_lead_line_rec.uom_code := l_primary_uom_code;
957 close c_base_valid;
958 END IF;
959
960 IF Validate_Item(
961 p_qte_header_rec => l_qte_header_rec,
962 p_inventory_item_id => l_lead_line_rec.inventory_item_id,
963 p_organization_id => l_lead_line_rec.organization_id,
964 p_quantity => l_lead_line_rec.quantity,
965 p_uom_code => l_lead_line_rec.uom_code) THEN
966
967 l_line_count := l_line_count + 1;
968 l_qte_line_tbl(l_line_count).operation_code := 'CREATE';
969 l_qte_line_tbl(l_line_count).inventory_item_id := l_lead_line_rec.inventory_item_id;
970 l_qte_line_tbl(l_line_count).organization_id := l_lead_line_rec.organization_id;
971 l_qte_line_tbl(l_line_count).quantity := l_lead_line_rec.quantity;
972 l_qte_line_tbl(l_line_count).uom_code := l_lead_line_rec.uom_code;
973
974 -- Recurring charges Change
975 IF aso_debug_pub.g_debug_flag = 'Y' THEN
976 aso_debug_pub.add('lx_qte_header_rec.org_id: ' || lx_qte_header_rec.org_id);
977 END IF;
978
979 l_master_organization_id := oe_sys_parameters.value(param_name => 'MASTER_ORGANIZATION_ID',
980 p_org_id => lx_qte_header_rec.org_id);
981
982 IF aso_debug_pub.g_debug_flag = 'Y' THEN
983 aso_debug_pub.add('l_master_organization_id: ' || l_master_organization_id);
984 END IF;
985
986 OPEN c_periodicity(l_lead_line_rec.inventory_item_id, l_master_organization_id);
987 FETCH c_periodicity INTO l_charge_periodicity_code;
988
989 l_qte_line_tbl(l_line_count).charge_periodicity_code := l_charge_periodicity_code;
990
991 IF c_periodicity%NOTFOUND THEN
992 IF aso_debug_pub.g_debug_flag = 'Y' THEN
993 aso_debug_pub.add('ASO_OPP_QTE_PUB:IF c_periodicity%NOTFOUND invitem'
994 ||l_lead_line_rec.inventory_item_id, 1, 'N');
995 END IF;
996 END IF;
997 close c_periodicity;
998
999 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1000 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').operation_code:'
1001 ||l_qte_line_tbl(l_line_count).operation_code, 1, 'N');
1002 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').inventory_item_id:'
1003 ||l_qte_line_tbl(l_line_count).inventory_item_id, 1, 'N');
1004 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').organization_id:'
1005 ||l_qte_line_tbl(l_line_count).organization_id, 1, 'N');
1006 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').quantity:'
1007 ||l_qte_line_tbl(l_line_count).quantity, 1, 'N');
1008 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').uom_code:'
1009 ||l_qte_line_tbl(l_line_count).uom_code, 1, 'N');
1010 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').price_list_id:'
1011 ||l_qte_line_tbl(l_line_count).price_list_id, 1, 'N');
1012 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_qte_line_tbl('||l_line_count||').charge_periodicity_code:'
1013 ||l_qte_line_tbl(l_line_count).charge_periodicity_code, 1, 'N');
1014 END IF;
1015
1016
1017 /* Line level forecast date should not be mapped to the Request Date Bug (3115703*/
1018 /* IF l_lead_line_rec.forecast_date IS NOT NULL
1019 AND l_lead_line_rec.forecast_date <> FND_API.G_MISS_DATE THEN
1020 IF l_lead_line_rec.forecast_date >= SYSDATE THEN
1021 l_ln_shipment_count := l_ln_shipment_count + 1;
1022 lp_ln_shipment_tbl(l_ln_shipment_count).qte_line_index := l_line_count;
1023 lp_ln_shipment_tbl(l_ln_shipment_count).operation_code := 'CREATE';
1024 lp_ln_shipment_tbl(l_ln_shipment_count).request_date := l_lead_line_rec.forecast_date;
1025 END IF;
1026 END IF;
1027 */
1028 -- Populate Sales Credits
1029 FOR l_sales_credit_rec IN c_sales_credits(P_OPP_QTE_IN_REC.OPPORTUNITY_ID,
1030 l_lead_line_rec.lead_line_id) LOOP
1031 l_ln_sales_credit_count := l_ln_sales_credit_count + 1;
1032 lp_ln_sales_credit_tbl(l_ln_sales_credit_count).operation_code := 'CREATE';
1033 lp_ln_sales_credit_tbl(l_ln_sales_credit_count).qte_line_index := l_line_count;
1034 lp_ln_sales_credit_tbl(l_ln_sales_credit_count).resource_id
1035 := l_sales_credit_rec.salesforce_id;
1036 lp_ln_sales_credit_tbl(l_ln_sales_credit_count).resource_group_id
1037 := l_sales_credit_rec.salesgroup_id;
1038 lp_ln_sales_credit_tbl(l_ln_sales_credit_count).sales_credit_type_id
1039 := l_sales_credit_rec.credit_type_id;
1040 lp_ln_sales_credit_tbl(l_ln_sales_credit_count).percent := l_sales_credit_rec.credit_percent;
1041 END LOOP;
1042
1043 -- Populate Price Attributes with Offer ID
1044 FOR l_campaign_rec IN C_campaign_id(l_lead_line_rec.offer_id) LOOP
1045 l_ln_price_attr_count := l_ln_price_attr_count + 1;
1046 lp_ln_price_attributes_tbl(l_ln_price_attr_count).operation_code := 'CREATE';
1047 lp_ln_price_attributes_tbl(l_ln_price_attr_count).qte_line_index := l_line_count;
1048 lp_ln_price_attributes_tbl(l_ln_price_attr_count).pricing_context := 'MODLIST';
1049 lp_ln_price_attributes_tbl(l_ln_price_attr_count).flex_title := 'QP_ATTR_DEFNS_QUALIFIER';
1050 lp_ln_price_attributes_tbl(l_ln_price_attr_count).pricing_attribute1
1051 := l_campaign_rec.source_code_for_id;
1052 END LOOP;
1053
1054 END IF; -- Validate Item
1055
1056 END LOOP; -- Line Loop
1057
1058 l_pricing_control_rec.request_type := l_control_rec.pricing_request_type;
1059 l_pricing_control_rec.pricing_event := l_control_rec.header_pricing_event;
1060
1061 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1062 aso_debug_pub.add('ASO_OPP_QTE_PUB: before Validate_Pricing_Order', 1, 'Y');
1063 END IF;
1064
1065 ASO_VALIDATE_PRICING_PVT.Validate_Pricing_Order(
1066 p_api_version_number => 1.0,
1067 p_init_msg_list => FND_API.G_FALSE,
1068 p_commit => FND_API.G_FALSE,
1069 p_control_rec => l_pricing_control_rec,
1070 p_qte_header_rec => lx_qte_header_rec,
1071 p_qte_line_tbl => l_qte_line_tbl,
1072 p_ln_shipment_tbl => lp_ln_shipment_tbl,
1073 p_ln_price_attr_tbl => lp_ln_price_attributes_tbl,
1074 x_qte_header_rec => lpx_qte_header_rec,
1075 x_qte_line_tbl => lpx_qte_line_tbl,
1076 x_qte_line_dtl_tbl => lpx_qte_line_dtl_tbl,
1077 x_price_adj_tbl => lpx_price_adjustment_tbl,
1078 x_price_adj_attr_tbl => lpx_price_adj_attr_tbl,
1079 x_price_adj_rltship_tbl => lpx_price_adj_rltship_tbl,
1080 x_return_status => x_return_status,
1081 x_msg_count => x_msg_count,
1082 x_msg_data => x_msg_data
1083 );
1084
1085 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1086 aso_debug_pub.add('ASO_OPP_QTE_PUB: after Validate_Pricing_Order', 1, 'Y');
1087 END IF;
1088
1089 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1090 RAISE FND_API.G_EXC_ERROR;
1091 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 END IF;
1094
1095 l_dtl_line_count := 0;
1096 l_line_count := 0;
1097 l_ln_shipment_count := 0;
1098 l_ln_sales_credit_count := 0;
1099 l_ln_price_attr_count := 0;
1100 l_qte_line_tbl := ASO_QUOTE_PUB.G_Miss_Qte_Line_Tbl;
1101 l_profile_val := fnd_profile.value('ASO_REQUIRE_SERVICE_REFERENCE');
1102
1103 FOR i IN 1..lpx_qte_line_tbl.count LOOP
1104
1105 IF lpx_qte_line_tbl(i).pricing_status_code <> FND_API.G_RET_STS_SUCCESS THEN
1106 FOR conc_segments_rec IN c_conc_segments(lpx_qte_line_tbl(i).inventory_item_id) LOOP
1107 l_conc_segments := conc_segments_rec.concatenated_segments;
1108 END LOOP;
1109 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1110 aso_debug_pub.add('ASO_OPP_QTE_PUB: item has pricing error: '
1111 || lpx_qte_line_tbl(i).pricing_status_text, 1, 'N');
1112 END IF;
1113
1114 FND_MESSAGE.Set_Name('ASO', 'ASO_OTQ_INVALID_PRICELIST');
1115 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
1116 FND_MSG_PUB.ADD;
1117
1118 ELSE
1119
1120 l_serv_item_flag := null;
1121 l_serv_duraion := null;
1122 l_serv_period := null;
1123 l_add_line := fnd_api.g_true;
1124 l_add_service := fnd_api.g_false;
1125
1126 open c_serv_item(lpx_qte_line_tbl(i).inventory_item_id,lpx_qte_line_tbl(i).organization_id);
1127 fetch c_serv_item INTO l_serv_item_flag,l_serv_duraion,l_serv_period;
1128 close c_serv_item;
1129
1130 if (nvl(l_serv_item_flag, 'N') = 'Y') then
1131
1132 if (nvl(l_profile_val, 'Y') = 'N') then
1133
1134 l_line_count := l_line_count + 1;
1135
1136 l_qte_line_tbl(l_line_count) := lpx_qte_line_tbl(i);
1137 l_qte_line_tbl(l_line_count).start_date_active := SYSDATE;
1138 l_qte_line_tbl(l_line_count).item_type_code := 'SRV';
1139
1140 l_dtl_line_count := l_dtl_line_count + 1;
1141
1142 l_qte_line_dtl_tbl(l_dtl_line_count).qte_line_index := l_line_count;
1143 l_qte_line_dtl_tbl(l_dtl_line_count).operation_code := 'CREATE';
1144 l_qte_line_dtl_tbl(l_dtl_line_count).service_duration := l_serv_duraion;
1145 l_qte_line_dtl_tbl(l_dtl_line_count).service_period := l_serv_period;
1146
1147 l_add_service := fnd_api.g_true;
1148
1149 else
1150 l_add_line := fnd_api.g_false;
1151
1152 end if;
1153
1154 end if;
1155
1156
1157 if l_add_line = fnd_api.g_true then
1158
1159 if l_add_service = fnd_api.g_false then
1160
1161 l_line_count := l_line_count + 1;
1162 l_qte_line_tbl(l_line_count) := lpx_qte_line_tbl(i);
1163
1164 end if;
1165
1166 l_qte_line_tbl(l_line_count).quote_header_id := lx_qte_header_rec.quote_header_id;
1167 l_qte_line_tbl(l_line_count).operation_code := 'CREATE';
1168
1169 FOR j IN 1..lp_ln_shipment_tbl.count LOOP
1170
1171 IF lp_ln_shipment_tbl(j).qte_line_index = i THEN
1172 l_ln_shipment_count := l_ln_shipment_count + 1;
1173 l_ln_shipment_tbl(l_ln_shipment_count) := lp_ln_shipment_tbl(j);
1174 l_ln_shipment_tbl(l_ln_shipment_count).qte_line_index := l_line_count;
1175 END IF;
1176
1177 END LOOP;
1178
1179 FOR k IN 1..lp_ln_sales_credit_tbl.count LOOP
1180
1181 IF lp_ln_sales_credit_tbl(k).qte_line_index = i THEN
1182 l_ln_sales_credit_count := l_ln_sales_credit_count + 1;
1183 l_ln_sales_credit_tbl(l_ln_sales_credit_count) := lp_ln_sales_credit_tbl(k);
1184 l_ln_sales_credit_tbl(l_ln_sales_credit_count).qte_line_index := l_line_count;
1185 END IF;
1186
1187 END LOOP;
1188
1189 FOR l IN 1..lp_ln_price_attributes_tbl.count LOOP
1190
1191 IF lp_ln_price_attributes_tbl(l).qte_line_index = i THEN
1192 l_ln_price_attr_count := l_ln_price_attr_count + 1;
1193 l_ln_price_attributes_tbl(l_ln_price_attr_count) := lp_ln_price_attributes_tbl(l);
1194 l_ln_price_attributes_tbl(l_ln_price_attr_count).qte_line_index := l_line_count;
1195 END IF;
1196
1197 END LOOP;
1198
1199 end if; --l_add_line = fnd_api.g_true then
1200
1201 END IF;
1202
1203 END LOOP;
1204
1205 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1206 aso_debug_pub.add('ASO_OPP_QTE_PUB: value of ASO_API_ENABLE_SECURITY:'
1207 || FND_PROFILE.value('ASO_API_ENABLE_SECURITY'), 1, 'N');
1208 aso_debug_pub.add('ASO_OPP_QTE_PUB: before copy sales team loop: lx_qte_header_rec.resource_id:'
1209 || lx_qte_header_rec.resource_id, 1, 'Y');
1210 END IF;
1211
1212 IF NVL(FND_PROFILE.value('ASO_API_ENABLE_SECURITY'),'N') = 'Y' THEN
1213 FOR l_sales_team_rec IN C_sales_team(P_OPP_QTE_IN_REC.OPPORTUNITY_ID) LOOP
1214 IF l_sales_team_rec.salesforce_id <> lx_qte_header_rec.resource_id THEN
1215 l_qte_access_rec := ASO_SECURITY_INT.G_MISS_QTE_ACCESS_REC;
1216 l_qte_access_rec.OPERATION_CODE := 'CREATE';
1217 l_qte_access_rec.QUOTE_NUMBER := lx_qte_header_rec.quote_number;
1218 l_qte_access_rec.RESOURCE_ID := l_sales_team_rec.salesforce_id;
1219 l_qte_access_rec.RESOURCE_GRP_ID := l_sales_team_rec.sales_group_id;
1220 l_qte_access_rec.CREATED_BY := G_USER_ID;
1221 l_qte_access_rec.CREATION_DATE := SYSDATE;
1222 l_qte_access_rec.LAST_UPDATED_BY := G_USER_ID;
1223 l_qte_access_rec.LAST_UPDATE_LOGIN := G_LOGIN_ID;
1224 l_qte_access_rec.LAST_UPDATE_DATE := SYSDATE;
1225 l_qte_access_rec.REQUEST_ID := l_sales_team_rec.request_id;
1226 l_qte_access_rec.PROGRAM_APPLICATION_ID := l_sales_team_rec.program_application_id;
1227 l_qte_access_rec.PROGRAM_ID := l_sales_team_rec.program_id;
1228 l_qte_access_rec.PROGRAM_UPDATE_DATE := l_sales_team_rec.program_update_date;
1229 l_qte_access_rec.KEEP_FLAG := l_sales_team_rec.freeze_flag;
1230 l_qte_access_rec.UPDATE_ACCESS_FLAG := l_sales_team_rec.team_leader_flag;
1231 l_qte_access_rec.CREATED_BY_TAP_FLAG := l_sales_team_rec.created_by_tap_flag;
1232 l_qte_access_rec.ATTRIBUTE_CATEGORY := l_sales_team_rec.attribute_category;
1233 l_qte_access_rec.ATTRIBUTE1 := l_sales_team_rec.attribute1;
1234 l_qte_access_rec.ATTRIBUTE2 := l_sales_team_rec.attribute2;
1235 l_qte_access_rec.ATTRIBUTE3 := l_sales_team_rec.attribute3;
1236 l_qte_access_rec.ATTRIBUTE4 := l_sales_team_rec.attribute4;
1237 l_qte_access_rec.ATTRIBUTE5 := l_sales_team_rec.attribute5;
1238 l_qte_access_rec.ATTRIBUTE6 := l_sales_team_rec.attribute6;
1239 l_qte_access_rec.ATTRIBUTE7 := l_sales_team_rec.attribute7;
1240 l_qte_access_rec.ATTRIBUTE8 := l_sales_team_rec.attribute8;
1241 l_qte_access_rec.ATTRIBUTE9 := l_sales_team_rec.attribute9;
1242 l_qte_access_rec.ATTRIBUTE10 := l_sales_team_rec.attribute10;
1243 l_qte_access_rec.ATTRIBUTE11 := l_sales_team_rec.attribute11;
1244 l_qte_access_rec.ATTRIBUTE12 := l_sales_team_rec.attribute12;
1245 l_qte_access_rec.ATTRIBUTE13 := l_sales_team_rec.attribute13;
1246 l_qte_access_rec.ATTRIBUTE14 := l_sales_team_rec.attribute14;
1247 l_qte_access_rec.ATTRIBUTE15 := l_sales_team_rec.attribute15;
1248 l_qte_access_tbl(l_qte_access_tbl.count+1) := l_qte_access_rec;
1249 END IF;
1250 END LOOP;
1251
1252 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1253 aso_debug_pub.add('ASO_OPP_QTE_PUB: after copy sales team loop: l_qte_access_tbl.count:'
1254 || l_qte_access_tbl.count, 1, 'N');
1255 END IF;
1256
1257 END IF; --NVL(FND_PROFILE.value('ASO_API_ENABLE_SECURITY'),'N') = 'Y'
1258
1259
1260
1261
1262
1263 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1264 aso_debug_pub.add('ASO_OPP_QTE_PUB: before Update_Quote', 1, 'Y');
1265 END IF;
1266
1267
1268 BEGIN
1269 SELECT last_update_date into l_last_update_date
1270 FROM ASO_QUOTE_HEADERS_ALL
1271 WHERE quote_header_id = lx_qte_header_rec.quote_header_id;
1272
1273 lx_QTE_HEADER_REC.last_update_date := l_last_update_date;
1274
1275 EXCEPTION WHEN NO_DATA_FOUND THEN
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1278 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
1279 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1280 FND_MSG_PUB.ADD;
1281 END IF;
1282 RAISE FND_API.G_EXC_ERROR;
1283 END;
1284
1285
1286 ASO_QUOTE_PUB.Update_Quote(
1287 p_api_version_number => 1.0,
1288 p_init_msg_list => fnd_api.g_false,
1289 p_commit => fnd_api.g_false,
1290 p_control_rec => p_control_rec,
1291 p_qte_header_rec => lx_qte_header_rec,
1292 p_qte_line_tbl => l_qte_line_tbl,
1293 p_qte_line_dtl_tbl => l_qte_line_dtl_tbl,
1294 p_ln_Price_Attributes_Tbl => l_ln_price_attributes_tbl,
1295 p_ln_shipment_tbl => l_ln_shipment_tbl,
1296 p_ln_sales_credit_Tbl => l_ln_sales_credit_tbl,
1297 P_Qte_Access_Tbl => l_qte_access_tbl,
1298 P_Template_Tbl => P_Template_Tbl,
1299 X_Qte_Header_Rec => lx_out_qte_header_rec,
1300 X_Qte_Line_Tbl => lx_Qte_Line_Tbl,
1301 X_Qte_Line_Dtl_Tbl => lx_Qte_Line_Dtl_Tbl,
1302 X_hd_Price_Attributes_Tbl => lx_hd_Price_Attributes_Tbl,
1303 X_hd_Payment_Tbl => lx_hd_Payment_Tbl,
1304 X_hd_Shipment_Tbl => lx_hd_Shipment_Tbl,
1305 X_hd_Freight_Charge_Tbl => lx_hd_Freight_Charge_Tbl,
1306 X_hd_Tax_Detail_Tbl => lx_hd_Tax_Detail_Tbl,
1307 X_hd_Attr_Ext_Tbl => lX_hd_Attr_Ext_Tbl,
1308 X_hd_Sales_Credit_Tbl => lx_hd_Sales_Credit_Tbl,
1309 X_hd_Quote_Party_Tbl => lx_hd_Quote_Party_Tbl,
1310 X_Line_Attr_Ext_Tbl => lx_Line_Attr_Ext_Tbl,
1311 X_line_rltship_tbl => lx_line_rltship_tbl,
1312 X_Price_Adjustment_Tbl => lx_Price_Adjustment_Tbl,
1313 X_Price_Adj_Attr_Tbl => lx_Price_Adj_Attr_Tbl,
1314 X_Price_Adj_Rltship_Tbl => lx_Price_Adj_Rltship_Tbl,
1315 X_ln_Price_Attributes_Tbl => lx_ln_Price_Attributes_Tbl,
1316 X_ln_Payment_Tbl => lx_ln_Payment_Tbl,
1317 X_ln_Shipment_Tbl => lx_ln_Shipment_Tbl,
1318 X_ln_Freight_Charge_Tbl => lx_ln_Freight_Charge_Tbl,
1319 X_ln_Tax_Detail_Tbl => lx_ln_Tax_Detail_Tbl,
1320 X_Ln_Sales_Credit_Tbl => lX_Ln_Sales_Credit_Tbl,
1321 X_Ln_Quote_Party_Tbl => lX_Ln_Quote_Party_Tbl,
1322 X_Qte_Access_Tbl => lx_Qte_Access_Tbl,
1323 X_Template_Tbl => lx_Qte_Template_Tbl,
1324 X_Related_Obj_Tbl => lX_Related_Obj_Tbl,
1325 X_Return_Status => x_Return_Status,
1326 X_Msg_Count => x_Msg_Count,
1327 X_Msg_Data => x_Msg_Data);
1328
1329 lx_qte_header_rec := lx_out_qte_header_rec;
1330 --existing code -- Not sure if this is necessary
1331 l_qte_access_tbl := lx_qte_access_tbl;
1332
1333 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1334 aso_debug_pub.add('update_quote:X_Return_Status:'||X_Return_Status,1,'N');
1335 aso_debug_pub.add('update_quote:X_Msg_Count:'||X_Msg_Count,1,'N');
1336 END IF;
1337
1338 IF x_return_status = FND_API.G_RET_STS_ERROR then
1339 raise FND_API.G_EXC_ERROR;
1340 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1341 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1342 END IF;
1343
1344
1345 -- security changes
1346 -- copying the sales team from opportunity to quote
1347
1348
1349 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1350 aso_debug_pub.add('ASO_OPP_QTE_PUB: value of ASO_API_ENABLE_SECURITY:'
1351 || FND_PROFILE.value('ASO_API_ENABLE_SECURITY'), 1, 'N');
1352 END IF;
1353
1354 IF NVL(FND_PROFILE.value('ASO_API_ENABLE_SECURITY'),'N') = 'Y' THEN
1355 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1356 aso_debug_pub.add('ASO_OPP_QTE_PUB: before copy sales team loop: lx_qte_header_rec.resource_id:'
1357 || lx_qte_header_rec.resource_id, 1, 'Y');
1358 END IF;
1359
1360 FOR l_opp_rec IN C_opp_owner(P_OPP_QTE_IN_REC.OPPORTUNITY_ID,lx_qte_header_rec.quote_number) LOOP
1361 ASO_QUOTE_ACCESSES_PKG.UPDATE_ROW(
1362 P_ACCESS_ID => l_opp_rec.access_id,
1363 P_QUOTE_NUMBER => FND_API.G_MISS_NUM,
1364 P_RESOURCE_ID => FND_API.G_MISS_NUM,
1365 P_RESOURCE_GRP_ID => FND_API.G_MISS_NUM,
1366 P_CREATED_BY => FND_API.G_MISS_NUM,
1367 P_CREATION_DATE => FND_API.G_MISS_DATE,
1368 P_LAST_UPDATED_BY => G_USER_ID,
1369 P_LAST_UPDATE_LOGIN => G_LOGIN_ID,
1370 P_LAST_UPDATE_DATE => SYSDATE,
1371 P_REQUEST_ID => FND_API.G_MISS_NUM,
1372 P_PROGRAM_APPLICATION_ID => FND_API.G_MISS_NUM,
1373 P_PROGRAM_ID => FND_API.G_MISS_NUM,
1374 P_PROGRAM_UPDATE_DATE => FND_API.G_MISS_DATE,
1375 P_KEEP_FLAG => l_opp_rec.freeze_flag,
1376 P_UPDATE_ACCESS_FLAG => FND_API.G_MISS_CHAR,
1377 P_CREATED_BY_TAP_FLAG => FND_API.G_MISS_CHAR,
1378 p_TERRITORY_ID => FND_API.G_MISS_NUM,
1379 p_TERRITORY_SOURCE_FLAG => FND_API.G_MISS_CHAR,
1380 p_ROLE_ID => FND_API.G_MISS_NUM,
1381 P_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
1382 P_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
1383 P_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
1384 P_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
1385 P_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
1386 P_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
1387 P_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
1388 P_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
1389 P_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
1390 P_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
1391 P_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
1392 P_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
1393 P_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
1394 P_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
1395 P_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
1396 P_ATTRIBUTE15 => FND_API.G_MISS_CHAR,
1397 p_ATTRIBUTE16 => FND_API.G_MISS_CHAR,
1398 p_ATTRIBUTE17 => FND_API.G_MISS_CHAR,
1399 p_ATTRIBUTE18 => FND_API.G_MISS_CHAR,
1400 p_ATTRIBUTE19 => FND_API.G_MISS_CHAR,
1401 p_ATTRIBUTE20 => FND_API.G_MISS_CHAR,
1402 P_OBJECT_VERSION_NUMBER => FND_API.G_MISS_NUM
1403 );
1404
1405 END LOOP;
1406
1407 END IF;
1408
1409 -- end security changes
1410
1411 -- sanity check
1412 -- call to check the validity of the passed flags
1413 Set_Copy_Flags(
1414 p_object_id => p_opp_qte_in_rec.opportunity_id,
1415 x_copy_notes_flag => l_copy_notes_flag,
1416 x_copy_task_flag => l_copy_task_flag,
1417 x_copy_att_flag => l_copy_att_flag
1418 );
1419
1420 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1421 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_CONTROL_REC.COPY_NOTES_FLAG:'
1422 ||P_CONTROL_REC.COPY_NOTES_FLAG, 1, 'N');
1423 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_copy_notes_flag:'||l_copy_notes_flag, 1, 'N');
1424 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_CONTROL_REC.COPY_TASK_FLAG:'
1425 ||P_CONTROL_REC.COPY_TASK_FLAG, 1, 'N');
1426 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_copy_task_flag:'||l_copy_task_flag, 1, 'N');
1427 aso_debug_pub.add('ASO_OPP_QTE_PUB: P_CONTROL_REC.COPY_ATT_FLAG:'
1428 ||P_CONTROL_REC.COPY_ATT_FLAG, 1, 'N');
1429 aso_debug_pub.add('ASO_OPP_QTE_PUB: l_copy_att_flag:'||l_copy_att_flag, 1, 'N');
1430 END IF;
1431
1432 IF (p_control_rec.COPY_NOTES_FLAG = 'Y') AND (l_copy_notes_flag = 'Y') THEN
1433 ASO_NOTES_INT.Copy_Opp_Notes_To_Qte(
1434 p_api_version => 1.0,
1435 p_init_msg_list => FND_API.G_FALSE,
1436 p_commit => FND_API.G_FALSE,
1437 p_old_object_id => P_OPP_QTE_IN_REC.OPPORTUNITY_ID,
1438 p_new_object_id => lx_qte_header_rec.quote_header_id,
1439 p_old_object_type_code => 'OPPORTUNITY',
1440 p_new_object_type_code => 'ASO_QUOTE',
1441 x_return_status => x_return_status,
1442 x_msg_count => x_msg_count,
1443 x_msg_data => x_msg_data
1444 );
1445 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1446 RAISE FND_API.G_EXC_ERROR;
1447 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449 END IF;
1450 END IF;
1451
1452 IF (p_control_rec.COPY_TASK_FLAG = 'Y') AND (l_copy_task_flag = 'Y') THEN
1453 ASO_TASK_INT.Copy_Opp_Tasks_To_Qte(
1454 p_api_version => 1.0,
1455 p_init_msg_list => FND_API.G_FALSE,
1456 p_commit => FND_API.G_FALSE,
1457 p_old_object_id => P_OPP_QTE_IN_REC.OPPORTUNITY_ID,
1458 p_new_object_id => lx_qte_header_rec.quote_header_id,
1459 p_old_object_type_code => 'OPPORTUNITY',
1460 p_new_object_type_code => 'ASO_QUOTE',
1461 p_new_object_name => lx_qte_header_rec.quote_number || FND_GLOBAL.local_chr(45) || lx_qte_header_rec.quote_version,
1462 x_return_status => x_return_status,
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data
1465 );
1466 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1467 RAISE FND_API.G_EXC_ERROR;
1468 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1470 END IF;
1471 END IF;
1472
1473 IF (p_control_rec.COPY_ATT_FLAG = 'Y') AND (l_copy_att_flag = 'Y') THEN
1474 ASO_ATTACHMENT_INT.Copy_Attachments(
1475 p_api_version => l_api_version,
1476 p_init_msg_list => FND_API.G_FALSE,
1477 p_commit => FND_API.G_FALSE,
1478 p_old_object_code => 'AS_OPPORTUNITY_ATTCH',
1479 p_new_object_code => 'ASO_QUOTE_HEADERS_ALL',
1480 p_old_object_id => P_OPP_QTE_IN_REC.OPPORTUNITY_ID,
1481 p_new_object_id => lx_qte_header_rec.quote_header_id,
1482 x_return_status => x_return_status,
1483 x_msg_count => x_msg_count,
1484 x_msg_data => x_msg_data
1485 );
1486 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1487 RAISE FND_API.G_EXC_ERROR;
1488 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1490 END IF;
1491 END IF;
1492
1493 END LOOP;
1494
1495 -- Setting OUT NOCOPY /* file.sql.39 change */ parameter values
1496 X_OPP_QTE_OUT_REC.quote_header_id := lx_qte_header_rec.quote_header_id;
1497 X_OPP_QTE_OUT_REC.quote_number := lx_qte_header_rec.quote_number;
1498 X_OPP_QTE_OUT_REC.related_object_id := lx_related_object_id;
1499 X_OPP_QTE_OUT_REC.cust_account_id := lx_qte_header_rec.cust_account_id;
1500 X_OPP_QTE_OUT_REC.party_id := lx_qte_header_rec.party_id;
1501 X_OPP_QTE_OUT_REC.currency_code := lx_qte_header_rec.currency_code;
1502
1503 -- End of API body.
1504
1505 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1506 aso_debug_pub.add('****** End of Create_Qte_Opportunity API ******', 1, 'Y');
1507 END IF;
1508
1509 -- Standard check of p_commit.
1510 IF FND_API.To_Boolean(p_commit) THEN
1511 COMMIT WORK;
1512 END IF;
1513
1514 -- Standard call to get message count and if count is 1, get message info.
1515 FND_Msg_Pub.Count_And_Get(
1516 p_encoded => FND_API.G_FALSE,
1517 p_count => x_msg_count ,
1518 p_data => x_msg_data
1519 );
1520
1521 EXCEPTION
1522 WHEN FND_API.G_EXC_ERROR THEN
1523 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1524 P_API_NAME => L_API_NAME,
1525 P_PKG_NAME => G_PKG_NAME,
1526 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1527 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB,
1528 P_SQLCODE => SQLCODE,
1529 P_SQLERRM => SQLERRM,
1530 X_MSG_COUNT => X_MSG_COUNT,
1531 X_MSG_DATA => X_MSG_DATA,
1532 X_RETURN_STATUS => X_RETURN_STATUS
1533 );
1534
1535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1537 P_API_NAME => L_API_NAME,
1538 P_PKG_NAME => G_PKG_NAME,
1539 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1540 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB,
1541 P_SQLCODE => SQLCODE,
1542 P_SQLERRM => SQLERRM,
1543 X_MSG_COUNT => X_MSG_COUNT,
1544 X_MSG_DATA => X_MSG_DATA,
1545 X_RETURN_STATUS => X_RETURN_STATUS
1546 );
1547
1548 WHEN OTHERS THEN
1549 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1550 P_API_NAME => L_API_NAME,
1551 P_PKG_NAME => G_PKG_NAME,
1552 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
1553 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PUB,
1554 P_SQLCODE => SQLCODE,
1555 P_SQLERRM => SQLERRM,
1556 X_MSG_COUNT => X_MSG_COUNT,
1557 X_MSG_DATA => X_MSG_DATA,
1558 X_RETURN_STATUS => X_RETURN_STATUS
1559 );
1560
1561 END Create_Qte_Opportunity;
1562
1563
1564 FUNCTION Validate_Item(
1565 p_qte_header_rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
1566 p_inventory_item_id IN NUMBER,
1567 p_organization_id IN NUMBER,
1568 p_quantity IN NUMBER,
1569 p_uom_code IN VARCHAR2
1570 ) RETURN BOOLEAN
1571 IS
1572
1573 CURSOR c_conc_segments (l_inventory_item_id NUMBER) IS
1574 SELECT concatenated_segments
1575 FROM MTL_SYSTEM_ITEMS_VL
1576 WHERE inventory_item_id = l_inventory_item_id;
1577
1578 CURSOR c_orderable_items (l_inventory_item_id NUMBER, l_organization_id NUMBER) IS
1579 SELECT bom_item_type,
1580 primary_uom_code,
1581 service_item_flag
1582 FROM MTL_SYSTEM_ITEMS_VL
1583 WHERE inventory_item_id = l_inventory_item_id
1584 AND organization_id = l_organization_id
1585 AND customer_order_enabled_flag = 'Y'
1586 AND bom_item_type <> 2
1587 AND NVL(start_date_active, SYSDATE) <= SYSDATE
1588 AND NVL(end_date_active, SYSDATE) >= SYSDATE;
1589
1590 cursor c_in_org_in_master_org(l_inventory_item_id NUMBER, l_organization_id NUMBER) IS
1591 select segment1
1592 from mtl_system_items_vl
1593 WHERE inventory_item_id = l_inventory_item_id
1594 AND organization_id = l_organization_id;
1595
1596 l_conc_segments VARCHAR2(40);
1597 l_orderable_flag VARCHAR2(1) := 'N';
1598 l_uom_code MTL_SYSTEM_ITEMS_B.primary_uom_code%TYPE;
1599 l_resp_id NUMBER;
1600 l_resp_appl_id NUMBER;
1601 l_ui_def_id NUMBER;
1602 l_output_qty NUMBER;
1603 l_primary_qty NUMBER;
1604 l_return_status VARCHAR2(30);
1605
1606 lx_return_status VARCHAR2(50);
1607 lx_msg_count NUMBER;
1608 lx_msg_data VARCHAR2(2000);
1609 l_master_organization_id NUMBER;
1610 l_segment1 VARCHAR2(240);
1611 BEGIN
1612
1613 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1614
1615 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1616 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: *** Start of API body ***', 1, 'Y');
1617 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: p_inventory_item_id: '|| p_inventory_item_id, 1, 'N');
1618 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: p_organization_id: '|| p_organization_id, 1, 'N');
1619 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: p_quantity: '|| p_quantity, 1, 'N');
1620 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: p_uom_code: '|| p_uom_code, 1, 'N');
1621 END IF;
1622
1623
1624 FOR conc_segments_rec IN c_conc_segments(p_inventory_item_id) LOOP
1625 l_conc_segments := conc_segments_rec.concatenated_segments;
1626 END LOOP;
1627
1628 -- bug 4932359
1629 l_master_organization_id := oe_sys_parameters.value(param_name => 'MASTER_ORGANIZATION_ID',
1630 p_org_id => p_qte_header_rec.org_id);
1631
1632 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1633 aso_debug_pub.add('l_master_organization_id: ' || l_master_organization_id);
1634 aso_debug_pub.add(' p_qte_header_rec.org_id: ' || p_qte_header_rec.org_id);
1635 END IF;
1636
1637 open c_in_org_in_master_org(p_inventory_item_id,l_master_organization_id);
1638 fetch c_in_org_in_master_org into l_segment1;
1639 if c_in_org_in_master_org%NOTFOUND THEN
1640 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1641 aso_debug_pub.add('ASO_OPP_QTE_PUB: Item does not exist in the master org',1,'N');
1642 END IF;
1643 FND_MESSAGE.Set_Name('ASO', 'ASO_INV_NOT_IN_OP_UNIT');
1644 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
1645 FND_MESSAGE.Set_Token('OPUNIT', l_master_organization_id, FALSE);
1646 FND_MSG_PUB.ADD;
1647 RETURN FALSE;
1648
1649 end if;
1650 close c_in_org_in_master_org;
1651
1652
1653 FOR orderable_items_rec IN c_orderable_items(p_inventory_item_id, p_organization_id) LOOP
1654 l_orderable_flag := 'Y';
1655 IF p_uom_code IS NULL THEN
1656 l_uom_code := orderable_items_rec.primary_uom_code;
1657
1658 ELSIF p_uom_code IS NOT NULL AND p_uom_code <> FND_API.G_MISS_CHAR THEN
1659 l_uom_code := p_uom_code;
1660
1661 END IF;
1662
1663 IF orderable_items_rec.service_item_flag = 'Y' THEN
1664 IF (fnd_profile.value('ASO_REQUIRE_SERVICE_REFERENCE') <> 'N')
1665 OR (fnd_profile.value('ASO_REQUIRE_SERVICE_REFERENCE') is null) THEN
1666 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1667 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: item is of type service:'
1668 || p_inventory_item_id, 1, 'N');
1669 END IF;
1670 FND_MESSAGE.Set_Name('ASO', 'ASO_OTQ_SERVICE_ITEM');
1671 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
1672 FND_MSG_PUB.ADD;
1673 RETURN FALSE;
1674
1675 end if;
1676 END IF;
1677
1678 IF orderable_items_rec.bom_item_type = 1 THEN
1679 l_resp_id := FND_PROFILE.Value('RESP_ID');
1680 l_resp_appl_id := FND_PROFILE.Value('RESP_APPL_ID');
1681 l_ui_def_id := CZ_CF_API.UI_FOR_ITEM(
1682 p_inventory_item_id,
1683 p_organization_id,
1684 SYSDATE,
1685 'APPLET',
1686 FND_API.G_MISS_NUM,
1687 FND_PROFILE.Value('RESP_ID'),
1688 FND_PROFILE.Value('RESP_APPL_ID')
1689 );
1690
1691 IF l_ui_def_id IS NULL THEN
1692 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1693 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: item does not have a configurable ui:'
1694 || p_inventory_item_id, 1, 'N');
1695 END IF;
1696
1697 FND_MESSAGE.Set_Name('ASO', 'ASO_OTQ_NO_CFG_UI_FOR_ITEM');
1698 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
1699 FND_MSG_PUB.ADD;
1700 RETURN FALSE;
1701 END IF;
1702 END IF;
1703
1704 INV_DECIMALS_PUB.Validate_Quantity(
1705 p_inventory_item_id,
1706 p_organization_id,
1707 p_quantity,
1708 l_uom_code,
1709 l_output_qty,
1710 l_primary_qty,
1711 l_return_status
1712 );
1713 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1714 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item:p_quantity'|| p_quantity, 1, 'N');
1715 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item:l_return_status'|| l_return_status, 1, 'N');
1716 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item:l_uom_code'|| l_uom_code, 1, 'N');
1717 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item:l_output_qty'|| l_output_qty, 1, 'N');
1718 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item:l_primary_qty'|| l_primary_qty, 1, 'N');
1719 END IF;
1720
1721 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR p_quantity <= 0 THEN
1722 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1723 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: item has invalid quantity:'
1724 || p_inventory_item_id, 1, 'N');
1725 END IF;
1726
1727 FND_MESSAGE.Set_Name('ASO', 'ASO_OTQ_INVALID_QTY');
1728 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
1729 FND_MSG_PUB.ADD;
1730 RETURN FALSE;
1731 END IF;
1732
1733 END LOOP;
1734
1735 IF l_orderable_flag = 'N' THEN
1736 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1737 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: item not orderable:'|| p_inventory_item_id, 1, 'N');
1738 END IF;
1739
1740 FND_MESSAGE.Set_Name('ASO', 'ASO_OTQ_NOT_ORDERABLE');
1741 FND_MESSAGE.Set_Token('INVITEM', l_conc_segments, FALSE);
1742 FND_MSG_PUB.ADD;
1743 RETURN FALSE;
1744 END IF;
1745
1746
1747 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1748 aso_debug_pub.add('ASO_OPP_QTE_PUB: Validate_Item: *** End of API body ***', 1, 'Y');
1749 END IF;
1750
1751
1752 RETURN TRUE;
1753
1754 END;
1755
1756
1757 PROCEDURE Set_Copy_Flags
1758 (
1759 p_object_id IN NUMBER,
1760 x_copy_notes_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1761 x_copy_task_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1762 x_copy_att_flag OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1763 )
1764 IS
1765
1766 CURSOR l_notes_csr(object_id NUMBER) IS
1767 SELECT jtf_note_id
1768 FROM jtf_notes_b
1769 WHERE source_object_id = object_id
1770 AND source_object_code = 'OPPORTUNITY';
1771
1772 CURSOR l_tasks_csr(object_id NUMBER) IS
1773 SELECT task_id
1774 FROM jtf_tasks_b
1775 WHERE source_object_id = object_id
1776 AND source_object_type_code = 'OPPORTUNITY';
1777
1778 CURSOR l_attch_csr(object_id NUMBER) IS
1779 SELECT attached_document_id
1780 FROM fnd_attached_documents
1781 WHERE pk1_value = TO_CHAR(object_id)
1782 AND entity_name = 'AS_OPPORTUNITY_ATTCH';
1783
1784 BEGIN
1785
1786 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
1787
1788 x_copy_notes_flag := 'N';
1789 x_copy_task_flag := 'N';
1790 x_copy_att_flag := 'N';
1791
1792 FOR notes_rec IN l_notes_csr(p_object_id) LOOP
1793 x_copy_notes_flag := 'Y';
1794 END LOOP;
1795
1796 FOR tasks_rec IN l_tasks_csr(p_object_id) LOOP
1797 x_copy_task_flag := 'Y';
1798 END LOOP;
1799
1800 FOR attachments_rec IN l_attch_csr(p_object_id) LOOP
1801 x_copy_att_flag := 'Y';
1802 END LOOP;
1803
1804 END Set_Copy_Flags;
1805
1806 Procedure Address_Validation(
1807 p_party_site_id IN Number,
1808 p_use_type IN VARCHAR2,
1809 x_valid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1810 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1811 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
1812 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1813 )
1814 IS
1815 CURSOR c_location(p_party_site_id IN Number) IS
1816 SELECT location_id from hz_party_sites
1817 WHERE party_site_id = p_party_site_id ;
1818
1819 CURSOR c_loc_assign(p_location_id IN Number) IS
1820 SELECT loc_id from hz_loc_assignments
1821 WHERE location_id = p_location_id ;
1822 --Commented Code Start Yogeshwar (MOAC)
1823 /*
1824 AND NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO' ),1,1) , ' ', NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99);
1825 */
1826 --Commented Code End Yogeshwar (MOAC)
1827 l_location_id Number;
1828 l_loc_id Number;
1829 lx_loc_id Number;
1830 l_token VARCHAR2(10);
1831
1832 BEGIN
1833 x_valid := 'Y';
1834
1835 OPEN c_location(p_party_site_id);
1836 FETCH c_location INTO l_location_id;
1837 close c_location;
1838
1839 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1840 aso_debug_pub.add('ASO_OPP_QTE_PUB:Address_Validation:location_id'||l_location_id, 1, 'N');
1841 END IF;
1842
1843 OPEN c_loc_assign(l_location_id);
1844 FETCH c_loc_assign INTO l_loc_id;
1845
1846 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1847 aso_debug_pub.add('ASO_OPP_QTE_PUB:Address_Validation:loc_assignment_id:'||l_loc_id, 1, 'N');
1848 END IF;
1849
1850 IF c_loc_assign%NOTFOUND THEN
1851 HZ_TAX_ASSIGNMENT_V2PUB.create_loc_assignment(
1852 p_init_msg_list => FND_API.G_FALSE,
1853 p_location_id => l_location_id,
1854 p_lock_flag => FND_API.G_FALSE,
1855 p_created_by_module => 'ASO_CUSTOMER_DATA',
1856 p_application_id => FND_API.G_MISS_NUM,
1857 X_RETURN_STATUS => X_RETURN_STATUS,
1858 X_MSG_COUNT => X_MSG_COUNT,
1859 X_MSG_DATA => X_MSG_DATA,
1860 x_loc_id => lx_loc_id
1861 );
1862
1863 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1864 aso_debug_pub.add('ASO_OPP_QTE_PUB:Address_Validation:X_RETURN_STATUS:'||X_RETURN_STATUS, 1, 'N');
1865 aso_debug_pub.add('ASO_OPP_QTE_PUB:Address_Validation:lx_loc_id:'||lx_loc_id, 1, 'N');
1866 END IF;
1867
1868 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1869 x_valid := 'N';
1870 FND_Msg_Pub.initialize;
1871 END IF;
1872 END IF;
1873
1874 END;
1875
1876
1877 END;