1 PACKAGE BODY CSD_Refurbish_IRO_GRP AS
2 /* $Header: csdrirob.pls 120.6 2008/03/14 01:06:27 takwong ship $ */
3
4 -- ---------------------------------------------------------
5 -- Define global variables
6 -- ---------------------------------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_Refurbish_IRO_GRP';
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdrirob.pls';
10
11 /*--------------------------------------------------------------------*/
12 /* PROCEDURE Name : Get_PartySiteID */
13 /* x_return_status Standard OUT param */
14 /* x_msg_data Standard OUT param */
15 /* x_msg_count Standard OUT param */
16 /* p_site_use_type Site Use Type Like To_Ship or To_Bill */
17 /* p_cust_site_use_id Customer Site USe Id value */
18 /* x_party_site_use_id Party Site Use Id OUT value */
19 /* Description : Takes Customer site use Id and site use type */
20 /* variables as input and returns corresponding */
21 /* party site use id , party Id and party site use id */
22 /*--------------------------------------------------------------------*/
23 /*--------------------------------------------------------------------*/
24 /* Note : this procedure is also used by Vivek's API, since this */
25 /* is commonly used, it will be better if it is moved to */
26 /* csd_process_utils package. Discuss with Vivek */
27 /*--------------------------------------------------------------------*/
28
29 Procedure Get_PartySiteId
30 (
31 x_return_status Out NOCOPY Varchar2,
32 x_msg_Data Out NoCopy Varchar2 ,
33 x_msg_Count Out NoCopy Number,
34 p_site_use_type In Varchar2,
35 p_cust_site_use_id In Number ,
36 x_party_id OUT NOCOPY Number,
37 x_party_site_id OUT NOCOPY Number,
38 x_party_site_use_id Out NOCOPY Number ) IS
39
40 -- Define local variables
41 l_party_site_id Number;
42 l_party_site_use_id Number;
43 l_party_id Number ;
44
45 -- Define Constants for debug level variables
46 C_Procedure_Level Constant Number := Fnd_Log.Level_Procedure ;
47 C_Exception_Level Constant Number := Fnd_Log.Level_Exception ;
48 C_Statement_Level Constant Number := Fnd_Log.Level_Statement ;
49 C_Debug_Level Constant Number := Fnd_Log.G_Current_Runtime_Level ;
50 C_Module Constant Varchar2(240) := 'csd.plsql.CSD_Refurbish_IRO_GRP.Get_PartySiteID' ;
51
52 C_API_Name Constant Varchar2(30) := 'Get_PartySiteID';
53 C_Site_Status_Active Constant Varchar2(1) := 'A' ;
54 Begin
55 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
56 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || 'Begin','Begining of procedure : Get_PartySiteID ');
57 End If;
58
59 --- Check if required input parameters are NULL
60 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
61 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
62 'Checking if required input parameter cust_Site_Use_id is Null');
63 End If;
64 -- Check the required parameter(p_Cust_Site_Use_Id)
65 CSD_PROCESS_UTIL.Check_Reqd_Param
66 ( p_param_value => p_Cust_Site_Use_Id,
67 p_param_name => 'Cust_Site_Use_Id',
68 p_api_name => C_API_Name);
69
70 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
71 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
72 'Checking if required input parameter Site_Use_Type is Null');
73 End If;
74
75 -- Check the required parameter(p_Site_Use_Type)
76 CSD_PROCESS_UTIL.Check_Reqd_Param
77 ( p_param_value => p_Site_Use_Type,
78 p_param_name => 'Site_Use_Type',
79 p_api_name => C_API_Name);
80
81 Begin
82 Select hcas.party_site_id
83 Into x_party_site_id
84 From hz_cust_acct_sites_all hcas,
85 hz_cust_site_uses_all hcsu
86 Where hcas.cust_acct_site_id = hcsu.cust_acct_site_id
87 And hcsu.site_use_id = p_cust_site_use_id ;
88 Exception
89 When No_Data_found then
90 Fnd_Message.Set_Name('CSD', 'CSD_PARTY_SITE_NOT_FOUND');
91 Fnd_Message.Set_Token('CUST_ACCT_SITE_ID', p_cust_site_use_id);
92 Fnd_Msg_Pub.Add;
93 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
94 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module , ' Party site id not found for customer acct site use id ' || p_cust_site_use_id );
95 End If;
96 Raise FND_API.G_EXC_ERROR ;
97 End;
98 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
99 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module , ' Party site id found for customer acct site use id ' || p_cust_site_use_id );
100 End If;
101 If x_party_site_id is not null Then
102 Begin
103 Select hpsu.party_site_use_id,
104 hps.party_id
105 Into x_party_site_use_id,
106 x_party_id
107 From Hz_Party_Sites hps,
108 Hz_Party_Site_uses hpsu,
109 Hz_Locations hl
110 Where hps.party_site_id = x_party_site_id
111 And hpsu.site_use_type = p_site_use_type
112 And hps.status = C_Site_Status_Active
113 And hps.location_id = hl.location_id
114 And hps.party_site_id = hpsu.party_site_id;
115 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
116 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module , ' Party id is found for party site id ' || x_party_site_id );
117 End If;
118 Exception
119 When No_Data_found then
120 Fnd_Message.Set_Name('CSD','CSD_PARTY_SITE_USE_NOT_FOUND');
121 Fnd_Message.Set_Token('PARTY_SITE_ID',l_Party_Site_Id);
122 Fnd_Msg_Pub.Add;
123 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
124 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module , ' Party id is not found for party site id ' || x_party_site_id );
125 End If;
126 Raise FND_API.G_EXC_ERROR ;
127 When Too_Many_Rows Then
128 Fnd_Message.Set_Name('CSD','CSD_TOO_MANY_PARTY_LOCATIONS');
129 Fnd_Message.Set_Token('PARTY_SITE_ID',l_Party_Site_Id);
130 Fnd_Message.Set_Token('SITE_USE_TYPE',p_Site_Use_Type);
131 Fnd_Msg_Pub.Add;
132 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
133 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module , ' Too many Party site use id are found for party site id ' || x_party_site_id );
134 End If;
135 Raise Fnd_Api.G_Exc_Error ;
136 End;
137 End If;
138 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
139 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || 'End','End of procedure : Get_PartySiteID was successful ');
140 End If;
141 Exception
142 When Fnd_Api.G_Exc_Error Then
143 x_return_status := Fnd_Api.G_Ret_Sts_Error ;
144
145 --- Standard call to get message count and if count is greater then 1, get message info
146 FND_MSG_PUB.Count_And_Get
147 (p_count => x_msg_count,
148 p_data => x_msg_data );
149 When Others Then
150 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error ;
151 If Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level Then
152 Fnd_Log.String(Fnd_Log.Level_Exception,C_Module , x_msg_data );
153 End If;
154 IF FND_MSG_PUB.Check_Msg_Level
155 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
156 THEN
157 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,c_api_name );
158 END IF;
159 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
160 p_data => x_msg_data );
161 If Fnd_Log.Level_Exception >= Fnd_Log.G_Current_Runtime_Level Then
162 Fnd_Log.String(Fnd_Log.Level_Exception,C_Module , x_msg_data );
163 End If;
164 End Get_PartySiteId;
165
166 /*-----------------------------------------------------------------------------------------*/
167 /*-- Create_InternalRO Procedure takes 4 input parameters p_Internal_SO_Header_Id_In, */
168 /*-- p_Req_Header_Id_In,p_Internal_SO_Header_Id_Out, p_Req_Header_Id_Out and creates one */
169 /*-- serive request and returns service request in x_Service_Request_Number out parameter. */
170 /* */
171 /*-- If procedure is not processed successfully then it returns error code and */
172 /*-- message. In case procedure returns errors all database transactions are rolled */
173 /*-- back. If item on internal sales order In is non serialized then one repair order */
174 /*-- is created under above service request. If item is serialized then number of */
175 /*-- repair orders will be as many as ordered quantity on internal sales order in. */
176 /*-- THis procedure creates two product trxn lines for each repair order, one product */
177 /*-- trxn line for internal SO Move In and another for internal SO Move out. */
178 /*-- p_Internal_SO_Header_Id_In, p_Req_Header_Id_In,p_Internal_SO_Header_Id_Out, */
179 /*-- p_Req_Header_Id_Out are required parameters. */
180 /*-- Internal RO are always created under new SR. */
181 /*-- If messgage count is greater then 1 then API does not return error message calling */
182 /*-- program should handle getting all the error message from message stack. */
183 /*-----------------------------------------------------------------------------------------*/
184 /* Procedure Name : Create_InternalRO */
185 /* P_api_version Standard In param */
186 /* P_init_msg_list Standard In param */
187 /* P_commit Standard In param */
188 /* P_validation_level Standard In param */
189 /* x_return_status Standard Out param */
190 /* x_msg_count Standard Out param */
191 /* x_msg_data Standard Out param */
192 /* P_req_header_id_in Requisition Header Id for IO1 (Required) */
193 /* P_internal_SO_header_id_in Internal SO header Id for IO1 (Required) */
194 /* P_req_header_id_out Requisition Header Id for IO2 (Required) */
195 /* P_internal_SO_header_id_out Internal SO header Id for IO2 (Required) */
196 /* x_service_request_number Service Request Number OUT variable */
197 /*-----------------------------------------------------------------------------------------*/
198
199
200 Procedure Create_InternalRO(
201 P_api_version In Number,
202 P_init_msg_list In Varchar2,
203 P_commit In Varchar2,
204 P_validation_level In Number,
205 x_return_status Out NOCOPY Varchar2,
206 x_msg_count Out NOCOPY Number,
207 x_msg_data Out NOCOPY Varchar2,
208 P_req_header_id_in In Number,
209 P_ISO_header_id_in In Number,
210 P_req_header_id_out In Number,
211 P_ISO_header_id_out In Number,
212 x_service_request_number Out NOCOPY Varchar2,
213 P_need_by_date In DATE) --Enhancement:3391950
214 IS
215 -- Declare local record variables for SR, Notes, contacts and repair line records
216 l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
217 l_notes_table CS_ServiceRequest_PUB.notes_table;
218 l_contacts_table CS_ServiceRequest_PUB.contacts_table;
219 l_rep_line_rec CSD_REPAIRS_PUB.Repln_Rec_Type;
220
221 l_inc_type_id Varchar2(255) ; -- Changed from number to Varchar2
222 l_inc_status_id Varchar2(255) ; -- Changed from number to Varchar2
223 l_inc_severity_id Varchar2(255) ; -- Changed from number to Varchar2
224 l_inc_urgency_id Varchar2(255) ; -- Changed from number to Varchar2
225 l_sr_owner_id Varchar2(255) ; -- Changed from number to Varchar2
226 l_repair_type_id Varchar2(255) ; -- Changed from number to Varchar2
227
228 l_inc_work_summary Varchar2(255) ;
229 l_Product_Trxn_Id Number ;
230 ln_interaction_id Number;
231 ln_workflow_id Number;
232 l_sr_count Number;
233 l_count Number;
234 l_ro_count Number;
235 l_error_count Number;
236 l_incident_id Number;
237 l_incident_number cs_incidents_all_b.Incident_Number%TYpe;
238 l_approval_flag Varchar2(1);
239 l_repair_mode csd_repairs.repair_mode%Type;
240
241 l_repair_number csd_repairs.repair_number%Type;
242 l_repair_line_id csd_repairs.repair_line_id%Type;
243 l_msg_count Number;
244 l_msg_data Varchar2(2000);
245 l_return_status Varchar2(30);
246 l_serialized_flag Varchar2(1);
247 l_customer_id NUMBER;
248 l_caller_type Varchar2(80);
249
250 l_ship_to_party_site_use_id Number;
251 l_ship_to_party_id Number;
252 l_ship_to_party_site_id Number;
253
254 l_bill_to_party_site_use_id Number;
255 l_bill_to_party_id Number;
256 l_bill_to_party_site_id Number;
257
258 l_rep_hist_id Number;
259 l_instance_id Number;
260 l_individual_owner Number;
261 l_group_owner Number ;
262 l_individual_type VARCHAR2(30) ;
263 l_IS_Move_In_Item_Serialized Varchar2(1) ;
264 l_IS_Move_Out_Item_Serialized Varchar2(1);
265 l_Item_In Varchar2(255);
266 l_Item_Out Varchar2(255) ;
267 l_Repair_Type_Name Varchar2(30) ;
268 -- Bug# 4000602 saupadhy local variable to hold value for default repair order organization
269 l_Default_RO_Org_ID Number;
270
271 --Define constants used in this procedure
272 c_api_name Constant Varchar2(30) := 'Create_InternalRO';
273 c_api_version Constant Number := 1.0;
274
275 C_SR_SubType_INC Constant Varchar2(30) := 'INC';
276 C_Yes Constant Varchar2(1) := 'Y';
277 C_NO Constant Varchar2(1) := 'N';
278 C_Status_Open Constant Varchar2(30) := 'OPEN';
279 C_Status_Open_Code Constant Varchar2(30) := 'O';
280 C_Site_Use_Type_Bill_To Constant Varchar2(30) := 'BILL_TO';
281 C_Site_Use_Type_Ship_To Constant Varchar2(30) := 'SHIP_TO';
282 C_RO_Txn_Status_Booked Constant Varchar2(30) := 'OM_BOOKED';
283
284 -- Constant to hold SR status column name
285 C_Col_SR_Status Constant Varchar2(30) := 'SR Status' ;
286 -- Constant to hold SR Type Column name
287 C_Col_SR_Type Constant Varchar2(30) := 'SR Type' ;
288 -- Constant to hold SR Severity
289 C_COl_SR_Severity Constant Varchar2(30) := 'SR Severity' ;
290 -- Constant to hold Repair Type
291 C_Col_Repair_Type Constant Varchar2(30) := 'Repair Type' ;
292
293 -- Define local constants to map fnd log message priority
294 C_Procedure_Level Constant Number := FND_LOG.LEVEL_PROCEDURE ;
295 C_Statement_Level Constant Number := Fnd_Log.Level_Statement ;
296 C_Exception_Level Constant Number := Fnd_Log.Level_Exception ;
297 C_Error_Level Constant Number := Fnd_Log.Level_Error;
298 C_Debug_Level Constant Number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
299 C_Module Constant Varchar2(240) := 'csd.plsql.CSD_Refurbish_IRO_GRP.Create_InternalRO' ;
300 -- Define local constants to for action type and action code
301 C_Action_Type_Move_In Constant Varchar2(30) := 'MOVE_IN' ;
302 C_Action_Type_Move_Out Constant Varchar2(30) := 'MOVE_OUT' ;
303
304 C_Action_Code_Usables Constant Varchar2(30) := 'USABLES' ;
305 C_Action_Code_Defectives Constant Varchar2(30) := 'DEFECTIVES' ;
306 C_PROD_TXN_STATUS_BOOKED Constant Varchar2(30) := 'BOOKED' ;
307
308
309 -- Cancelled_Flag column in oe_order_headers_all table can have values 'Y', 'N' or Null ,
310 -- always consider 'N' or Null
311 -- Open_flag column in oe_order_headers_all table can have values 'Y', 'N' , always consider 'Y'
312 -- Booked_flag column in oe_order_headers_all table can have values 'Y', 'N', always consider 'Y'
313 -- Cancelled_Flag column in oe_order_lines_all table can have values 'Y', 'N' or Null ,
314 -- always consider 'N' or Null
315 -- Open_flag column in oe_order_lines_all table can have values 'Y', 'N' , always consider 'Y'
316 -- Booked_flag column in oe_order_lines_all table can have values 'Y', 'N', always consider 'Y'
317 -- Party information, bill-to-address and ship-to-address are picked from ISO2 Bug # 3389067
318 Cursor Get_SRandRO_from_IRandISO_IN (p_ISO_Header_Id_IN Number, p_Req_Header_Id_IN Number ) IS
319 Select oeh.order_number order_number,
320 oeh.header_id order_header_id,
321 oeh.order_category_code,
322 oeh.booked_flag,
323 oeh.cust_po_number purchase_order_num,
324 NVL(oel.price_list_id,oeh.price_list_id) price_list_id,
325 oel.line_id ,
326 oel.inventory_item_id,
327 oel.line_type_id,
328 oel.order_quantity_uom,
329 prh.segment1 requisition_number ,
330 prh.requisition_header_Id req_Header_Id,
331 prl.line_num,
332 prl.requisition_Line_Id req_Line_Id,
333 prl.quantity requisition_quantity ,
334 prl.destination_organization_id ,
335 prl.destination_subinventory ,
336 prl.source_organization_id ,
337 prl.source_subinventory ,
338 prl.quantity,
339 oeh.transactional_curr_code currency_code,
340 prl.item_revision,
341 msi.serial_number_control_code,
342 msi.concatenated_segments
343 From Oe_Order_Headers_All oeh,
344 Oe_Order_Lines_all oel,
345 po_requisition_headers_all prh,
346 po_requisition_lines_all prl,
347 mtl_system_items_kfv msi
348 Where oeh.header_id = p_ISO_Header_Id_IN
349 And NVL(oeh.cancelled_flag,'N') = 'N'
350 And oeh.open_flag = 'Y'
351 And oeh.booked_flag = 'Y'
352 And oel.header_id = oeh.header_id
353 And oel.split_from_line_id is Null
354 And NVL(oel.cancelled_flag,'N') = 'N'
355 And oel.open_flag = 'Y'
356 And oel.booked_flag = 'Y'
357 And oel.inventory_item_id = msi.inventory_item_id
358 /*FP Fixed for bug#5368747
359 To get item attribute the join should be made with ship_from_org_id
360 from where the item will actually be shipped.
361 */
362 /*And oel.sold_from_org_id = msi.organization_id */
363 And oel.ship_from_org_id = msi.organization_id
364 And prh.requisition_header_id = p_req_header_id_In
365 And prh.requisition_header_id = prl.requisition_header_id
366 And NVL(prl.cancel_flag,'N') = 'N'
367 And prh.requisition_header_id = oeh.source_document_id
368 And prl.requisition_line_id = oel.source_document_line_id
369 And Not Exists
370 ( Select 'Found Record'
371 From csd_product_transactions
372 Where req_header_id = p_req_header_id_In
373 AND req_line_id > 0 )
374 And Not Exists
375 ( Select 'Found Record'
376 From csd_product_transactions
377 Where order_header_id = p_ISO_header_id_In
378 AND order_line_id > 0 ) ;
379
380 -- Party information, bill-to-address and shipp-to-address are picked from IO2 so
381 -- they are added to IO2 Bug # 3389067
382 Cursor Get_PrdTxn_from_IRandISO_OUT (p_ISO_Header_Id_Out Number, p_Req_Header_Id_Out Number ) IS
383 Select oeh.order_number order_number,
384 oeh.header_id order_header_id,
385 oeh.order_category_code,
386 oeh.booked_flag,
387 NVL(oeh.invoice_to_org_id,oel.invoice_to_org_id) bill_to_site_use_id,
388 NVL(oeh.ship_to_org_id,oel.ship_to_org_id) ship_to_site_use_id,
389 oeh.sold_to_org_id cust_account_id,
390 oel.line_id ,
391 oel.line_number line_number,
392 oel.inventory_item_id,
393 oel.line_type_id,
394 oel.order_quantity_uom,
395 oel.ordered_quantity ,
396 hp.party_type,
397 hp.party_id,
398 prh.segment1 requisition_number ,
399 prl.line_num,
400 prl.destination_organization_id ,
401 prl.destination_subinventory ,
402 prl.source_organization_id ,
403 prl.source_subinventory ,
404 prl.quantity,
405 prl.Requisition_Header_Id Req_Header_ID,
406 prl.Requisition_Line_ID Req_Line_Id,
407 msi.serial_number_control_code,
408 msi.concatenated_segments
409 From Oe_Order_Headers_All oeh,
410 Oe_Order_Lines_all oel,
411 hz_parties hp,
412 hz_cust_accounts hca,
413 po_requisition_headers_all prh,
414 po_requisition_lines_all prl,
415 mtl_system_items_kfv msi
416 Where oeh.header_id = p_ISO_Header_Id_Out
417 And NVL(oeh.cancelled_flag,'N') = 'N'
418 And oeh.open_flag = 'Y'
419 And oeh.booked_flag = 'Y'
420 And oel.header_id = oeh.header_id
421 And Nvl(oel.cancelled_flag,'N') = 'N'
422 And oel.open_flag = 'Y'
423 And oel.booked_flag = 'Y'
424 And oel.split_from_line_id is Null
425 And oel.inventory_item_id = msi.inventory_item_id
426 /*FP Fixed for bug#5368747
427 To get item attribute the join should be made with ship_from_org_id
428 from where the item will actually be shipped.
429 */
430 /*And oel.sold_from_org_id = msi.organization_id*/
431 And oel.ship_from_org_id = msi.organization_id
432 And oeh.sold_to_org_id = hca.cust_account_id
433 And hca.party_id = hp.party_id
434 And prh.requisition_header_id = p_req_header_id_Out
435 And prh.requisition_header_id = prl.requisition_header_id
436 And Nvl(prl.cancel_flag,'N') = 'N'
437 And prh.requisition_header_id = oeh.source_document_id
438 And prl.requisition_line_id = oel.source_document_line_id
439 And Not Exists
440 ( Select 'Found Record'
441 From csd_product_transactions
442 Where req_header_id = p_req_header_id_Out
443 AND req_line_id > 0 )
444 And Not Exists
445 ( Select 'Found Record'
446 From csd_product_transactions
447 Where order_header_id = p_ISO_header_id_Out
448 AND order_line_id > 0 ) ;
449
450 -- Fix for bug#5839636
451 -- Cursor to derive the (Repair) Inventory Org Id
452 --
453 Cursor get_req_org_id (p_requisition_header_id in number,
454 p_requisition_line_id in number) is
455 Select destination_organization_id
456 from po_requisition_lines_all
457 where requisition_header_id = p_requisition_header_id
458 and requisition_line_id = p_requisition_line_id;
459
460 --- Define Record Types of Type Cursor Definitions
461 l_IRandISO_In_Rec Get_SRandRO_from_IRandISO_IN%ROWTYPE;
462 l_IRandISO_Out_Rec Get_PrdTxn_from_IRandISO_OUT%ROWTYPE;
463 l_IRandISO_In_Rec2 Get_SRandRO_from_IRandISO_IN%ROWTYPE;
464 l_IRandISO_Out_Rec2 Get_PrdTxn_from_IRandISO_OUT%ROWTYPE;
465 Begin
466 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
467 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || 'Begin','Begining of procedure : Create_InternalRO ');
468 End If;
469 -- Standard Start of API savepoint
470 Savepoint Create_InternalRO;
471
472 -- Standard call to check for call compatibility.
473 If Not FND_API.Compatible_API_Call (c_api_version,
474 p_api_version, c_api_name , G_PKG_NAME )
475 Then
476 IF Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
477 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module||'.API_Version_Validation','API Version incompatibility');
478 End If;
479 Raise Fnd_Api.G_Exc_Unexpected_Error;
480 End If;
481
482 -- Initialize message list if p_init_msg_list is set to TRUE.
483 If Fnd_Api.to_Boolean( p_init_msg_list ) Then
484 Fnd_Msg_Pub.initialize;
485 End If;
486
487 -- Initialize API return status to success
488 x_return_status := Fnd_Api.G_Ret_Sts_Success;
489
490 --- Check if required input parameters are NULL
491 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
492 FND_LOG.STRING(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
493 'Checking if required input parameters are Null');
494 End If;
495
496 --- Check if required input parameters are NULL
497 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
498 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
499 'Checking if required input parameter Req_Header_id_in is Null');
500 End If;
501 -- Check the required parameter(p_Req_Header_Id_In)
502 CSD_PROCESS_UTIL.Check_Reqd_Param
503 ( p_param_value => p_Req_Header_Id_In,
504 p_param_name => 'Req_Header_Id_In',
505 p_api_name => C_API_Name);
506
507 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
508 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
509 'Checking if required input parameter Req_Header_id_Out is Null');
510 End If;
511 -- Check the required parameter(p_Req_Header_Id_Out)
512 CSD_PROCESS_UTIL.Check_Reqd_Param
513 ( p_param_value => p_Req_Header_Id_Out,
514 p_param_name => 'Req_Header_Id_Out',
515 p_api_name => C_API_Name);
516
517 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
518 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
519 'Checking if required input parameter ISO_Header_Id_In is Null');
520 End If;
521 -- Check the required parameter(p_ISO_Header_Id_In)
522 CSD_PROCESS_UTIL.Check_Reqd_Param
523 ( p_param_value => p_ISO_Header_Id_In,
524 p_param_name => 'ISO_Header_Id_In',
525 p_api_name => C_API_Name);
526 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
527 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.In_Parameter_Validation',
528 'Checking if required input parameter ISO_Header_Id_Out is Null');
529 End If;
530 -- Check the required parameter(p_ISO_Header_Id_Out)
531 CSD_PROCESS_UTIL.Check_Reqd_Param
532 ( p_param_value => p_ISO_Header_Id_Out,
533 p_param_name => 'ISO_Header_Id_Out',
534 p_api_name => C_API_Name);
535
536 --- IO1/IR1 and IO2/IR2 can not be same, validate.
537 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
538 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Val_IR1_and_IR2_are_same',
539 'Checking if parameters Req_Header_Id_In and Req_Header_Id_Out has same value');
540 End If;
541 -- Check if IR1 and IR2 are same
542 If p_Req_Header_Id_In = P_Req_header_id_Out Then
543 Fnd_Message.Set_Name('CSD','CSD_IR1_AND_IR2_SAME');
544 Fnd_Message.Set_Token('Req_Header_Id',p_Req_Header_Id_In);
545 Fnd_Msg_Pub.Add;
546 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
547 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Val_IR1_and_IR2_are_same',
548 'Input parameters Req_Header_Id_In and Req_Header_Id_Out has same value');
549 End If;
550 --- Message will say: Input parameter 'Requisition Header ID's p_req_header_Id_In are same for in and out requistion records';
551 x_return_status := FND_API.G_RET_STS_ERROR ;
552 Raise FND_API.G_EXC_ERROR ;
553 End IF;
554 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
555 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Val_IO1_and_IO2_are_same',
556 'Checking if parameters ISO_Header_Id_In and ISO_Header_Id_Out has same value');
557 End If;
558 -- Check if IO1 and IO2 are same.
559 If P_ISO_header_id_in = P_ISO_header_id_Out Then
560 Fnd_Message.Set_Name('CSD','CSD_IO1_AND_IO2_SAME');
561 Fnd_Message.Set_Token('Order_Header_Id',p_ISO_Header_Id_In);
562 Fnd_Msg_Pub.Add;
563 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
564 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Val_IO1_and_IO2_are_same',
565 'Input parameters ISO_Header_Id_In and ISO_Header_Id_Out has same value');
566 End If;
567 --- Message will say: Input parameter 'Internal Sales Order Header ID's p_Order_header_Id_In are same for in and out internal SO records';
568 x_return_status := FND_API.G_RET_STS_ERROR ;
569 Raise FND_API.G_EXC_ERROR ;
570 End If ;
571 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
572 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Record_Default_values',
573 'Getting default values for SR record from profile options');
574 End If;
575 -- get SR Type Id value from profile, this is a required field if not set raise error message.
576 FND_PROFILE.Get('CSD_IRO_DEFAULT_SR_TYPE',l_Inc_Type_Id);
577 If ( l_inc_type_id Is NULL ) Then
578 Fnd_Message.Set_Name('CSD','CSD_SR_REC_TYPE_REQ_COL_NULL');
579 Fnd_Message.Set_Token('COLUMN_NAME',c_COl_SR_Type );
580 Fnd_Msg_Pub.Add;
581 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
582 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.SR_Type_Validation','SR Type is Null') ;
583 End If;
584 --- Message will say: 'SR Type, required column for SR record Type is null';
585 x_return_status := FND_API.G_RET_STS_ERROR ;
586 Raise FND_API.G_EXC_ERROR ;
587 Else
588 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
589 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Type_Validation','SR TYpe id is :' || l_Inc_Type_Id ) ;
590 End If;
591 End If;
592
593 FND_PROFILE.Get('CSD_IRO_DEFAULT_SR_SEVERITY',l_Inc_Severity_Id);
594 -- Check if SR Severity Id is NULL, If so raise error
595 If (l_inc_severity_id Is NULL) Then
596 Fnd_Message.Set_Name('CSD','CSD_SR_REC_TYPE_REQ_COL_NULL');
597 Fnd_Message.Set_Token('COLUMN_NAME',C_Col_SR_Severity );
598 Fnd_Msg_Pub.Add;
599 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
600 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.SR_Severity_Validation','SR Severity is null' ) ;
601 End If;
602 --- Message will say: 'SR Severity, required column for SR record Type is null';
603 x_return_status := FND_API.G_RET_STS_ERROR ;
604 Raise FND_API.G_EXC_ERROR ;
605 Else
606 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
607 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Serverity_Validation',
608 'SR Severity id is :' || l_Inc_Severity_Id ) ;
609 End If;
610 End If;
611
612 -- DO not have to verify if value is null, as it is not a required column
613 FND_PROFILE.Get('CSD_OM_DEFAULT_SR_URGENCY',l_Inc_Urgency_Id);
614 --l_inc_work_summary := 'Internal Refurbish repair orders from internal sales orders for product : ' ;
615 -- Getting value from INC_DEFAULT_INCIDENT_OWNER since this profile is used to get value in UI
616 -- also CSD_OM_DEFAULT_SR_OWNER is incorrectly defined.
617 -- This is to fix bug 3395281 saupadhy
618 --l_sr_owner_id := FND_PROFILE.value('CSD_OM_DEFAULT_SR_OWNER');
619 FND_PROFILE.Get('INC_DEFAULT_INCIDENT_OWNER',l_Sr_Owner_Id);
620 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
621 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Owner_Id_value',
622 'Default value for SR Owner Id is :' ||l_sr_owner_id );
623 End If;
624 --Bug# 4000602 saupadhy DBI changes 11/05/2004
625 FND_PROFILE.GET('CSD_DEFAULT_REPAIR_ORG',l_Default_RO_Org_ID);
626
627 FND_PROFILE.Get('CSD_IRO_REPAIR_TYPE',l_Repair_Type_Id);
628 --- Check if Default values for RO exist
629 If (l_Repair_Type_id Is Null) Then
630 Fnd_Message.Set_Name('CSD','CSD_RO_REC_TYPE_REQ_COL_NULL');
631 Fnd_Message.Set_Token('COLUMN_NAME',C_Col_Repair_Type );
632 Fnd_Msg_Pub.Add;
633 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
634 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Repair_Type_Validation','Repair Type is Null' ) ;
635 End If;
636 -- Message will say: 'Repair Type, required column for SR record Type is null';
637 x_return_status := FND_API.G_RET_STS_ERROR ;
638 Raise FND_API.G_EXC_ERROR ;
639 Else
640 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
641 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Repair_Type_Validation','Repair TYpe is :' ||
642 l_Repair_Type_Id ) ;
643 End If;
644 End If;
645 Begin
646 Select incident_status_id Into l_inc_status_Id
647 From cs_incident_statuses_vl
648 Where incident_subtype = C_SR_SubType_INC
649 And seeded_flag = C_YES
650 And trunc(sysdate) between trunc(nvl(start_date_active,sysdate)) and trunc(nvl(end_date_active,sysdate))
651 And status_code = C_Status_Open ;
652 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
653 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Status_Id_value',
654 'Seeded SR status was found and its value is :' ||l_inc_status_Id );
655 End If;
656 Exception
657 When No_Data_Found Then
658 --l_inc_status_id := FND_PROFILE.value('CSD_OM_DEFAULT_SR_STATUS');
659 FND_PROFILE.Get('CSD_OM_DEFAULT_SR_STATUS',l_Inc_Status_Id);
660 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
661 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Status_Id_value',
662 'Seeded SR status was not found, so getting value from profile option and its value is :' ||l_inc_status_Id );
663 End If;
664 End;
665 --- Check if any of the defaulted values for required columns of SR Record type has null value.
666 If (l_inc_status_Id Is NULL ) Then
667 Fnd_Message.Set_Name('CSD','CSD_SR_REC_TYPE_REQ_COL_NULL');
668 Fnd_Message.Set_Token('COLUMN_NAME',C_Col_SR_Status );
669 Fnd_Msg_Pub.Add;
670 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
671 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.SR_Status_Validation','SR Status is NULL' ) ;
672 End If;
673 --- Message will say: 'SR Status, required column for SR record Type is null';
674 x_return_status := FND_API.G_RET_STS_ERROR ;
675 Raise FND_API.G_EXC_ERROR ;
676 Else
677 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
678 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.SR_Status_Validation','SR Status was found' ) ;
679 End If;
680 End If;
681 --- If Return status is error then raise exception and stop processing.
682 If x_return_status = FND_API.G_RET_STS_ERROR Then
683 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
684 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Repair_Status_Validation',
685 'Return Status is :' || x_return_status );
686 End If;
687 Raise FND_API.G_EXC_ERROR ;
688 End If;
689 Begin
690 Select repair_mode,Name
691 into l_repair_mode,l_Repair_Type_Name
692 from csd_repair_types_vl
693 where repair_type_id = l_Repair_Type_Id ;
694 -- Check if Repair Mode is Null If Repair Mode is null then raise exception
695 If l_Repair_Mode Is Null Then
696 Fnd_Message.Set_Name('CSD','CSD_REPAIR_MODE_IS_NULL');
697 Fnd_Message.Set_Token('REPAIR_TYPE',l_Repair_Type_Name );
698 Fnd_Msg_Pub.Add;
699 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
700 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Repair_Type_Validation','Repair Mode is Null for repair type :' || l_Repair_Type_Name);
701 End If;
702 X_Return_Status := Fnd_API.G_Ret_Sts_Error ;
703 Raise FND_API.G_EXC_ERROR ;
704 End If;
705 Exception
706 When No_Data_FOund Then
707 Fnd_Message.Set_Name('CSD','CSD_INVALID_REPAIR_TYPE');
708 Fnd_Msg_Pub.Add;
709 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
710 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Repair_Type_Validation','Repair Type Id is invalid');
711 End If;
712 X_Return_Status := Fnd_API.G_Ret_Sts_Error ;
713 Raise FND_API.G_EXC_ERROR ;
714 End ;
715 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
716 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Repair_Mode_Value',
717 'Return Mode is :' || l_repair_mode );
718 End If;
719 --- Open main cursor
720 Open Get_SRandRO_from_IRandISO_In(p_ISO_Header_Id_in , p_Req_Header_Id_IN ) ;
721 Fetch Get_SRandRO_from_IRandISO_In Into l_IRandISO_In_Rec ;
722 If Get_SRandRO_from_IRandISO_In%ROWCOUNT = 0 Then
723 --- Raise Exception and stop processing
724 Fnd_Message.Set_Name('CSD','CSD_IR_IO_REC_NOT_FOUND');
725 Fnd_Message.Set_Token('REQ_HDR_ID', p_Req_Header_Id_IN);
726 Fnd_Message.Set_Token('SO_HDR_ID', P_ISO_header_id_in);
727 Fnd_Msg_Pub.Add;
728 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
729 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Records_Validation','no records are found for move in') ;
730 End If;
731 Close Get_SRandRO_from_IRandISO_In ;
732 Raise FND_API.G_EXC_ERROR ;
733 Elsif Get_SRandRO_from_IRandISO_In%ROWCOUNT > 1 Then
734 --- Check if more then one records(IO1/IR1) are found in the cursor result set
735 --- if so stop processing and raise error
736 --- Raise Exception and stop processing
737 Fnd_Message.Set_Name('CSD','CSD_IR_IO_MANY_REC_FOUND');
738 Fnd_Message.Set_Token('REQ_HDR_ID', p_Req_Header_Id_IN);
739 Fnd_Message.Set_Token('SO_HDR_ID', P_ISO_header_id_in);
740 Fnd_Msg_Pub.Add;
741 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
742 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Records_Validation','too many move in records are found' ) ;
743 End If;
744 Raise FND_API.G_EXC_ERROR ;
745 End If;
746 -- Get SR Work Summary information from message dictionary
747 Fnd_Message.Set_Name('CSD','CSD_SR_WORK_SUMMARY');
748 Fnd_Message.Set_Token('PRODUCT', l_IRandISO_In_Rec.Concatenated_Segments || ' ');
749 l_Inc_Work_Summary := Fnd_Message.Get;
750 -- Check if Move_In item is serial controlled or not
751 If l_IRandISO_In_Rec.Serial_Number_Control_code = 1 Then
752 l_IS_Move_In_Item_Serialized := 'N';
753 Else
754 l_IS_Move_In_Item_Serialized := 'Y' ;
755 End If;
756 -- Close main Cursor as the values are fetched into a record and there is no need to fetch again
757 Close Get_SRandRO_from_IRandISO_In ;
758
759 --- Open Second cursor to fetch IO2/IR2 records
760 Open Get_PrdTxn_from_IRandISO_Out(p_ISO_Header_Id_Out , p_Req_Header_Id_Out ) ;
761 Fetch Get_PrdTxn_from_IRandISO_Out Into l_IRandISO_Out_Rec ;
762 If Get_PrdTxn_from_IRandISO_Out %ROWCOUNT = 0 Then
763 --- Raise Exception and stop processing
764 Fnd_Message.Set_Name('CSD','CSD_IR_IO_REC_NOT_FOUND');
765 Fnd_Message.Set_Token('REQ_HDR_ID',p_Req_Header_Id_Out);
766 Fnd_Message.Set_Token('SO_HDR_ID',p_ISO_Header_Id_Out);
767 Fnd_Msg_Pub.Add;
768 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
769 Fnd_Log.String(Fnd_Log.Level_Procedure, C_Module || '.Records_Validation','No move out records are found' ) ;
770 End If;
771 Close Get_PrdTxn_from_IRandISO_Out ;
772 Raise FND_API.G_EXC_ERROR ;
773 Elsif Get_PrdTxn_from_IRandISO_Out%ROWCOUNT > 1 Then
774 --- Check if more then one records(IO2/IR2) are found in the cursor result set
775 --- if so stop processing and raise error
776 --- Raise Exception and stop processing
777 Fnd_Message.Set_Name('CSD','CSD_IR_IO_MANY_REC_FOUND');
778 Fnd_Message.Set_Token('REQ_HDR_ID', p_Req_Header_Id_Out);
779 Fnd_Message.Set_Token('SO_HDR_ID', p_ISO_Header_Id_Out);
780 Fnd_Msg_Pub.Add;
781 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
782 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Records_Validation','Too many move out records are found' ) ;
783 End If;
784 Raise FND_API.G_EXC_ERROR ;
785 End If;
786 -- Check if Move_Out item is serial controlled or not
787 If l_IRandISO_Out_Rec.Serial_Number_Control_code = 1 Then
788 l_IS_Move_Out_Item_Serialized := 'N';
789 Else
790 l_IS_Move_Out_Item_Serialized := 'Y' ;
791 End If;
792 -- Close Second Cursor as the values are fetched into a record and there is no need to fetch again
793 Close Get_PrdTxn_from_IRandISO_Out ;
794 -- Check if Move_in and Move_Out Items serial number control code attributes are similar or not.
795 If l_IS_Move_In_Item_Serialized <> l_IS_Move_Out_Item_Serialized Then
796 --- Raise Exception and stop processing
797 l_Item_In := l_IRandISO_In_Rec.Concatenated_Segments ;
798 l_Item_Out := l_IRandISO_Out_Rec.Concatenated_Segments ;
799 Fnd_Message.Set_Name('CSD','CSD_SR_NUM_ATTR_DIFFER');
800 Fnd_Message.Set_Token('ITEM_IN', l_Item_in);
801 Fnd_Message.Set_Token('ITEM_OUT', l_Item_Out);
802 Fnd_Msg_Pub.Add;
803 If Fnd_Log.Level_Procedure >= Fnd_Log.G_Current_Runtime_Level Then
804 Fnd_Log.String(Fnd_Log.Level_Procedure,C_Module || '.Serial_Control_Validation','Serial number attributes are different for items on IO1 and IO2 lines' ) ;
805 End If;
806 Raise FND_API.G_EXC_ERROR ;
807 End If;
808 --Get the bill to site use Id
809 -- Changed from In To Out since SR should be created for IO2 not for IO1
810 Get_PartySiteId (
811 p_site_use_type => C_Site_Use_Type_Bill_To ,
812 p_cust_site_use_id => l_IRandISO_Out_Rec.bill_to_site_use_id,
813 x_party_site_use_id => l_bill_to_party_site_use_id ,
814 x_party_id => l_bill_to_party_id ,
815 x_party_site_id => l_bill_to_party_site_id ,
816 x_return_status => l_return_status ,
817 x_msg_count => l_msg_count,
818 x_msg_data => l_msg_data);
819
820 If Not(l_return_status = FND_API.G_RET_STS_SUCCESS) Then
821 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
822 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || '.Records_Validation',
823 'Error while calling procedure : Get_PartySIteId for site use type Bill To');
824 End If;
825 -- Raise Exception and stop processing
826 Raise FND_API.G_EXC_ERROR;
827 Else
828 Null;
829 End If;
830
831 ---Get the ship to site use Id
832 -- Changed from In To Out since SR should be created for IO2 not for IO1
833 Get_PartySiteId (
834 x_return_status => l_return_status,
835 x_msg_count => l_msg_count,
836 x_msg_data => l_msg_data,
837 p_site_use_type => C_Site_Use_Type_Ship_To ,
838 p_cust_site_use_id => l_IRandISO_Out_Rec.ship_to_site_use_id,
839 x_party_site_use_id => l_ship_to_party_site_use_id,
840 x_party_id => l_ship_to_party_id ,
841 x_party_site_id => l_ship_to_party_site_id );
842
843 If Not(x_return_status = FND_API.G_RET_STS_SUCCESS) Then
844 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
845 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || '.Records_Validation',
846 'Error while calling procedure : Get_PartySIteId for site use type Ship To');
847 End If;
848 -- Raise Exception and stop processing
849 Raise FND_API.G_EXC_ERROR;
850 Else
851 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
852 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Records_Validation',
853 'Error while calling procedure : Get_PartySIteId for site use type Ship To');
854 End If;
855 End If;
856 -- Initialize the SR record values
857 CS_SERVICEREQUEST_PUB.initialize_rec(l_service_request_rec);
858
859 l_service_request_rec.request_date := sysdate;
860 l_service_request_rec.type_id := l_inc_type_id;
861 l_service_request_rec.status_id := l_inc_status_id;
862 l_service_request_rec.severity_id := l_inc_severity_id;
863 l_service_request_rec.urgency_id := l_inc_urgency_id;
864 l_service_request_rec.owner_id := l_sr_owner_id ;
865 l_service_request_rec.summary := l_inc_work_summary;
866 l_service_request_rec.caller_type := l_IRandISO_Out_Rec.party_type;
867 l_service_request_rec.customer_id := l_IRandISO_Out_Rec.party_id;
868 l_service_request_rec.inventory_item_id := l_IRandISO_In_Rec.inventory_item_id;
869 l_service_request_rec.inventory_org_id := cs_std.get_item_valdn_orgzn_id;
870 --l_service_request_rec.purchase_order_num := C1.purchase_order_num;
871 l_service_request_rec.bill_to_site_use_id := l_bill_to_party_site_use_id;
872 l_service_request_rec.bill_to_party_id := l_bill_to_party_id;
873 l_service_request_rec.bill_to_site_id := l_bill_to_party_site_id;
874 l_service_request_rec.ship_to_site_use_id := l_ship_to_party_site_use_id;
875 l_service_request_rec.ship_to_party_id := l_ship_to_party_id;
876 l_service_request_rec.ship_to_site_id := l_ship_to_party_site_id;
877 l_service_request_rec.account_id := l_IRandISO_Out_Rec.cust_account_id;
878 l_service_request_rec.cust_po_number := l_IRandISO_In_Rec.purchase_order_num;
879 l_service_request_rec.sr_creation_channel := 'AUTOMATIC'; --- Since this SR is created by API
880 l_service_request_rec.publish_flag := '';
881 l_service_request_rec.verify_cp_flag := 'N';
882 l_Service_Request_Rec.inv_item_revision := l_IRandISO_In_Rec.Item_Revision ;
883
884
885 -- Not creating contact for SR as it is optional
886 -- Call to Service Request API
887 CS_SERVICEREQUEST_PUB.Create_ServiceRequest(
888 p_api_version => 3.0,
889 p_init_msg_list => FND_API.G_TRUE,
890 p_commit => FND_API.G_FALSE,
891 x_return_status => l_return_status,
892 x_msg_count => l_msg_count,
893 x_msg_data => l_msg_data,
894 p_resp_appl_id => NULL,
895 p_resp_id => NULL,
896 p_user_id => fnd_global.user_id,
897 p_login_id => fnd_global.conc_login_id,
898 p_org_id => NULL,
899 p_request_id => NULL,
900 p_request_number => NULL,
901 p_service_request_rec => l_service_request_rec,
902 p_notes => l_notes_table,
903 p_contacts => l_contacts_table,
904 p_auto_assign => C_NO ,
905 x_request_id => l_incident_id,
906 x_request_number => l_incident_number,
907 x_interaction_id => ln_interaction_id,
908 x_workflow_process_id => ln_workflow_id,
909 x_individual_owner => l_individual_owner,
910 x_group_owner => l_group_owner,
911 x_individual_type => l_individual_type );
912
913 x_Return_Status := l_Return_Status ;
914 If Not(x_return_status = FND_API.G_RET_STS_SUCCESS) Then
915 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
916 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || '.Create_ServiceRequest',
917 'Error : While calling procedure CS_SERVICEREQUEST_PUB.Create_ServiceRequest');
918 End If;
919 Raise FND_API.G_EXC_ERROR ;
920 Else
921 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
922 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Create_ServiceRequest',
923 'Sucsess : While calling procedure CS_SERVICEREQUEST_PUB.Create_ServiceRequest');
924 End If;
925 End If;
926 --- Check if Item is serialized item
927 Begin
928 Select 'Y'
929 into l_serialized_flag
930 from mtl_system_items
931 where inventory_item_id = l_IRandISO_In_Rec.inventory_item_id
932 and organization_id = l_IRandISO_In_Rec.source_organization_id
933 and serial_number_control_code <> 1;
934 Exception
935 When No_Data_Found then
936 l_serialized_flag := C_NO;
937 End;
938 ---- Assign values Repair Order Record
939 l_rep_line_rec.Incident_Id := l_incident_id ;
940 l_rep_line_rec.Inventory_Item_Id := l_IRandISO_In_Rec.inventory_item_id;
941 l_rep_line_rec.Unit_Of_Measure := l_IRandISO_In_Rec.order_quantity_uom;
942 l_rep_line_rec.Repair_Type_Id := l_repair_type_id ;
943 l_rep_line_rec.Repair_Mode := l_repair_mode ;
944 l_rep_line_rec.Status := C_Status_Open_Code ;
945 l_rep_line_rec.Status_Reason_Code := NULL;
946 l_rep_line_rec.Date_Closed := NULL;
947 l_rep_line_rec.Approval_Required_Flag := C_NO;
948 l_rep_line_rec.Approval_Status := NULL;
949 l_rep_line_rec.Quantity := l_IRandISO_In_Rec.quantity ;
950 l_rep_line_rec.Quantity_In_WIP := NULL;
951 l_rep_line_rec.Quantity_Rcvd := NULL;
952 l_rep_line_rec.Quantity_Shipped := NULL;
953 l_rep_line_rec.Repair_Group_Id := NULL;
954 l_rep_line_rec.RO_TXN_STATUS := C_RO_Txn_Status_Booked ;
955 l_rep_line_rec.Serial_Number := NULL;
956 l_rep_line_rec.Repair_Number := NULL;
957 l_rep_line_rec.PROMISE_DATE := P_need_by_date; --ER:3391950
958
959 l_Rep_Line_Rec.Item_Revision := l_IRandISO_In_Rec.Item_Revision ;
960 l_Rep_Line_Rec.Price_list_Header_Id := l_IRandISO_In_Rec.Price_List_Id ;
961 -- l_rep_line_rec.object_version_number := NULL;
962 l_rep_line_rec.currency_code := l_IRandISO_In_Rec.currency_code;
963 -- Check if ISO1 item and ISO2 items are same. If they are different
964 -- assign ISO2 item to supercession_inv_item_id
965 --bug# 4000602 saupadhy 11/05/2004 Pass Default Repair Org Id to Rep_Line_rec.
966 l_Rep_Line_Rec.Resource_Group := l_Default_RO_Org_Id ;
967 /*
968 If l_IRandISO_In_Rec.Inventory_item_id <> l_IRandISO_Out_Rec.Inventory_Item_Id Then
969 l_Rep_Line_Rec.Supercession_Inv_Item_Id := l_IRandISO_Out_Rec.Inventory_Item_Id ;
970 Else
971 l_Rep_Line_Rec.Supercession_Inv_Item_Id := Null;
972
973 End If;
974 */
975 --bug#6692459 always stored the move out item_id to supercession_inv_item_id on csd_repairs table
976 --always stored the move out quantity to the repair_yield_quantity on csd_repairs table
977 l_Rep_Line_Rec.Supercession_Inv_Item_Id := l_IRandISO_Out_Rec.Inventory_Item_Id;
978 l_Rep_Line_Rec.repair_yield_quantity := l_IRandISO_Out_Rec.quantity;
979
980
981 If l_Serialized_Flag = C_Yes Then
982
983 l_rep_line_rec.Quantity := 1 ;
984
985 ---Create RO as many as quantity column value
986 For I in 1.. l_IRandISO_In_Rec.quantity LOOP
987
988 -- Fix for bug#5839636
989 Open get_req_org_id (l_IRandISO_In_Rec.Req_Header_Id,
990 l_IRandISO_In_Rec.Req_Line_Id);
991 Fetch get_req_org_id INTO l_Rep_Line_Rec.inventory_org_id;
992 Close get_req_org_id;
993
994 csd_repairs_pvt.Create_Repair_Order(
995 P_Api_Version_Number => 1.0 ,
996 P_Init_Msg_List => FND_API.G_FALSE,
997 P_Commit => FND_API.G_FALSE,
998 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
999 p_REPAIR_LINE_ID => NULL,
1000 P_REPLN_Rec => l_Rep_Line_Rec ,
1001 X_REPAIR_LINE_ID => l_Repair_Line_Id,
1002 X_REPAIR_NUMBER => l_Repair_Number,
1003 X_Return_Status => l_Return_Status,
1004 X_Msg_Count => l_Msg_Count,
1005 X_Msg_Data => l_Msg_Data ) ;
1006 If l_Return_Status <> FND_API.G_RET_STS_SUCCESS Then
1007 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1008 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || '.Create_Repair_Order',
1009 'Error : While calling procedure CS_SERVICEREQUEST_PUB.Create_Repair_Order');
1010 End If;
1011 Raise FND_API.G_EXC_ERROR ;
1012 Else
1013 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
1014 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || '.Create_Repair_Order',
1015 'Success : While calling procedure CS_SERVICEREQUEST_PUB.Create_Repair_Order');
1016 End If;
1017 End If;
1018 -- Insert into Product Transactions table , Insert a row for Action Type Move-In
1019 l_Product_Trxn_Id := NULL ; -- This is just to make sure that new product trasaction id generated
1020 -- It is better to define a new procedure to handle insertions for internal repair orders in csd_process_pvt
1021 -- Then calling table handler directly in group API.
1022 csd_product_transactions_pkg.Insert_Row(
1023 px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id, -- find out if this variable needs to be assigned a value
1024 p_REPAIR_LINE_ID => l_Repair_Line_Id ,
1025 p_ESTIMATE_DETAIL_ID => NULL,
1026 p_ACTION_TYPE => C_ACTION_TYPE_MOVE_IN,
1027 p_ACTION_CODE => C_ACTION_CODE_DEFECTIVES,
1028 p_LOT_NUMBER => NULL,
1029 p_SUB_INVENTORY => l_IRandISO_In_Rec.Source_SubInventory,
1030 p_INTERFACE_TO_OM_FLAG => C_Yes,
1031 p_BOOK_SALES_ORDER_FLAG => C_Yes,
1032 p_RELEASE_SALES_ORDER_FLAG => NULL,
1033 p_SHIP_SALES_ORDER_FLAG => NULL,
1034 p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED ,
1035 p_PROD_TXN_CODE => NULL, -- Need to identify what value to be passed
1036 p_LAST_UPDATE_DATE => Sysdate ,
1037 p_CREATION_DATE => Sysdate,
1038 p_LAST_UPDATED_BY => Fnd_Global.User_Id,
1039 p_CREATED_BY => Fnd_Global.User_Id,
1040 p_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id,
1041 p_ATTRIBUTE1 => NULL ,
1042 p_ATTRIBUTE2 => NULL ,
1043 p_ATTRIBUTE3 => NULL ,
1044 p_ATTRIBUTE4 => NULL ,
1045 p_ATTRIBUTE5 => NULL ,
1046 p_ATTRIBUTE6 => NULL ,
1047 p_ATTRIBUTE7 => NULL ,
1048 p_ATTRIBUTE8 => NULL ,
1049 p_ATTRIBUTE9 => NULL ,
1050 p_ATTRIBUTE10 => NULL ,
1051 p_ATTRIBUTE11 => NULL ,
1052 p_ATTRIBUTE12 => NULL ,
1053 p_ATTRIBUTE13 => NULL ,
1054 p_ATTRIBUTE14 => NULL ,
1055 p_ATTRIBUTE15 => NULL ,
1056 p_CONTEXT => NULL ,
1057 p_OBJECT_VERSION_NUMBER => 1,
1058 p_Req_Header_Id => l_IRandISO_In_Rec.Req_Header_Id,
1059 p_Req_Line_Id => l_IRandISO_In_Rec.Req_Line_Id,
1060 p_Order_Header_Id => l_IRandISO_In_Rec.Order_Header_Id,
1061 p_Order_Line_Id => l_IRandISO_In_Rec.Line_Id ,
1062 p_Prd_Txn_Qty_Received => 0,
1063 p_Prd_Txn_Qty_Shipped => 0 ,
1064 p_Source_Serial_Number => NULL,
1065 p_Source_Instance_Id => Null,
1066 p_Non_Source_Serial_Number => NULL,
1067 p_Non_Source_Instance_Id => Null,
1068 p_Sub_Inventory_Rcvd => Null,
1069 p_Lot_Number_Rcvd => Null,
1070 p_Locator_Id => Null,
1071 p_picking_rule_id => Null,
1072 P_PROJECT_ID => Null,
1073 P_TASK_ID => Null,
1074 P_UNIT_NUMBER => Null);
1075 -- Add for R12 pickrule id change.Vijay.
1076
1077 -- Following code will not be raised but once I move above code to pvt file then this check will be required
1078 If l_Return_Status <> FND_API.G_RET_STS_SUCCESS Then
1079 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1080 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'Create_InternalRO.Insert_Row',
1081 'Error : While calling procedure csd_product_transactions_pkg.Insert_Row for Defectives');
1082 End If;
1083 Raise FND_API.G_EXC_ERROR ;
1084 Else
1085 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
1086 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || 'Create_InternalRO.Insert_Row',
1087 'Success : While calling procedure csd_product_transactions_pkg.Insert_Row for Defectives');
1088 End If;
1089 End If;
1090
1091 --- Insert into Product Transactions table , Insert a row for Action Type Move-Out
1092 l_Product_Trxn_Id := NULL ; -- This is just to make sure that new product trasaction id generated
1093 csd_product_transactions_pkg.Insert_Row(
1094 px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id,
1095 p_REPAIR_LINE_ID => l_Repair_Line_Id ,
1096 p_ESTIMATE_DETAIL_ID => NULL,
1097 p_ACTION_TYPE => C_ACTION_TYPE_MOVE_OUT,
1098 p_ACTION_CODE => C_ACTION_CODE_USABLES,
1099 p_LOT_NUMBER => NULL,
1100 p_SUB_INVENTORY => l_IRandISO_Out_Rec.Destination_SubInventory,
1101 p_INTERFACE_TO_OM_FLAG => C_YEs,
1102 p_BOOK_SALES_ORDER_FLAG => C_YEs,
1103 p_RELEASE_SALES_ORDER_FLAG => NULL,
1104 p_SHIP_SALES_ORDER_FLAG => NULL,
1105 p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED,
1106 p_PROD_TXN_CODE => NULL , -- Need to identify what value to be passed
1107 p_LAST_UPDATE_DATE => Sysdate ,
1108 p_CREATION_DATE => Sysdate,
1109 p_LAST_UPDATED_BY => Fnd_Global.User_Id,
1110 p_CREATED_BY => Fnd_Global.User_Id,
1111 p_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id,
1112 p_ATTRIBUTE1 => NULL ,
1113 p_ATTRIBUTE2 => NULL ,
1114 p_ATTRIBUTE3 => NULL ,
1115 p_ATTRIBUTE4 => NULL ,
1116 p_ATTRIBUTE5 => NULL ,
1117 p_ATTRIBUTE6 => NULL ,
1118 p_ATTRIBUTE7 => NULL ,
1119 p_ATTRIBUTE8 => NULL ,
1120 p_ATTRIBUTE9 => NULL ,
1121 p_ATTRIBUTE10 => NULL ,
1122 p_ATTRIBUTE11 => NULL ,
1123 p_ATTRIBUTE12 => NULL ,
1124 p_ATTRIBUTE13 => NULL ,
1125 p_ATTRIBUTE14 => NULL ,
1126 p_ATTRIBUTE15 => NULL ,
1127 p_CONTEXT => NULL ,
1128 p_OBJECT_VERSION_NUMBER => 1,
1129 p_Req_Header_Id => l_IRandISO_Out_Rec.Req_Header_Id,
1130 p_Req_Line_Id => l_IRandISO_Out_Rec.Req_Line_Id,
1131 p_Order_Header_Id => l_IRandISO_Out_Rec.Order_Header_Id,
1132 p_Order_Line_Id => l_IRandISO_Out_Rec.Line_Id ,
1133 p_Prd_Txn_Qty_Received => NULL, -- changing it from 0
1134 p_Prd_Txn_Qty_Shipped => 0 ,
1135 p_Source_Serial_Number => NULL,
1136 p_Source_Instance_Id => Null,
1137 p_Non_Source_Serial_Number => NULL,
1138 p_Non_Source_Instance_Id => Null,
1139 p_Sub_Inventory_Rcvd => Null,
1140 p_Lot_Number_Rcvd => Null,
1141 p_Locator_Id => Null,
1142 p_picking_rule_id => Null,
1143 P_PROJECT_ID => Null,
1144 P_TASK_ID => Null,
1145 P_UNIT_NUMBER => Null);
1146 -- Add for R12 pickrule id change.Vijay.
1147
1148 If l_Return_Status <> FND_API.G_RET_STS_SUCCESS Then
1149 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1150 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
1151 'Error : While calling procedure csd_product_transactions_pkg.Insert_Row for Defectives');
1152 End If;
1153 Raise FND_API.G_EXC_ERROR ;
1154 Else
1155 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
1156 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || 'Create_InternalRO.Insert_Row',
1157 'Success : While calling procedure csd_product_transactions_pkg.Insert_Row for Usables');
1158 End If;
1159 End If;
1160
1161 End Loop ;
1162 Else
1163
1164 l_rep_line_rec.Quantity := l_IRandISO_In_Rec.quantity ;
1165
1166 -- Fix for bug#5839636
1167 Open get_req_org_id (l_IRandISO_In_Rec.Req_Header_Id,
1168 l_IRandISO_In_Rec.Req_Line_Id);
1169 Fetch get_req_org_id INTO l_Rep_Line_Rec.inventory_org_id;
1170 Close get_req_org_id;
1171
1172 ---Create Repair Order
1173 csd_repairs_pvt.Create_Repair_Order(
1174 P_Api_Version_Number => 1.0 ,
1175 P_Init_Msg_List => Fnd_Api.G_False,
1176 P_Commit => Fnd_Api.G_False,
1177 p_validation_level => Fnd_Api.G_Valid_Level_Full,
1178 p_Repair_Line_ID => NULL,
1179 P_Repln_Rec => l_Rep_Line_Rec ,
1180 X_Repair_Line_ID => l_Repair_Line_Id,
1181 X_Repair_Number => l_Repair_Number,
1182 X_Return_Status => l_Return_Status,
1183 X_Msg_Count => l_Msg_Count,
1184 X_Msg_Data => l_Msg_Data ) ;
1185 If l_Return_Status <> FND_API.G_RET_STS_SUCCESS Then
1186 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1187 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'Create_Repair_Order',
1188 'Error : While calling procedure csd_repairs_pvt.Create_Repair_Order ');
1189 End If;
1190 Raise FND_API.G_EXC_ERROR ;
1191 Else
1192 If Fnd_Log.Level_Statement >= Fnd_Log.G_Current_Runtime_Level Then
1193 Fnd_Log.String(Fnd_Log.Level_Statement,C_Module || 'Create_Repair_Order',
1194 'Success : While calling procedure csd_repairs_pvt.Create_Repair_Order ');
1195 End If;
1196 End If;
1197 l_Product_Trxn_Id := NULL ; -- This is just to make sure that new product trasaction id generated
1198 -- Insert into Product Transactions table , Insert a row for Action Type Move-In
1199 csd_product_transactions_pkg.Insert_Row(
1200 px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id,
1201 p_REPAIR_LINE_ID => l_Repair_Line_Id ,
1202 p_ESTIMATE_DETAIL_ID => NULL,
1203 p_ACTION_TYPE => C_ACTION_TYPE_MOVE_IN,
1204 p_ACTION_CODE => C_ACTION_CODE_DEFECTIVES,
1205 p_LOT_NUMBER => NULL,
1206 p_SUB_INVENTORY => l_IRandISO_In_Rec.Source_SubInventory,
1207 p_INTERFACE_TO_OM_FLAG => C_Yes,
1208 p_BOOK_SALES_ORDER_FLAG => C_Yes,
1209 p_RELEASE_SALES_ORDER_FLAG => NULL,
1210 p_SHIP_SALES_ORDER_FLAG => NULL,
1211 p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED,
1212 p_PROD_TXN_CODE => NULL,-- Need to identify what value to be passed
1213 p_LAST_UPDATE_DATE => Sysdate ,
1214 p_CREATION_DATE => Sysdate,
1215 p_LAST_UPDATED_BY => Fnd_Global.User_Id,
1216 p_CREATED_BY => Fnd_Global.User_Id,
1217 p_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id,
1218 p_ATTRIBUTE1 => NULL ,
1219 p_ATTRIBUTE2 => NULL ,
1220 p_ATTRIBUTE3 => NULL ,
1221 p_ATTRIBUTE4 => NULL ,
1222 p_ATTRIBUTE5 => NULL ,
1223 p_ATTRIBUTE6 => NULL ,
1224 p_ATTRIBUTE7 => NULL ,
1225 p_ATTRIBUTE8 => NULL ,
1226 p_ATTRIBUTE9 => NULL ,
1227 p_ATTRIBUTE10 => NULL ,
1228 p_ATTRIBUTE11 => NULL ,
1229 p_ATTRIBUTE12 => NULL ,
1230 p_ATTRIBUTE13 => NULL ,
1231 p_ATTRIBUTE14 => NULL ,
1232 p_ATTRIBUTE15 => NULL ,
1233 p_CONTEXT => NULL ,
1234 p_OBJECT_VERSION_NUMBER => 1,
1235 p_Req_Header_Id => l_IRandISO_In_Rec.Req_Header_Id,
1236 p_Req_Line_Id => l_IRandISO_In_Rec.Req_Line_Id,
1237 p_Order_Header_Id => l_IRandISO_In_Rec.Order_Header_Id,
1238 p_Order_Line_Id => l_IRandISO_In_Rec.Line_Id ,
1239 p_Prd_Txn_Qty_Received => 0,
1240 p_Prd_Txn_Qty_Shipped => 0 ,
1241 p_Source_Serial_Number => NULL,
1242 p_Source_Instance_Id => Null,
1243 p_Non_Source_Serial_Number => NULL,
1244 p_Non_Source_Instance_Id => Null,
1245 p_Sub_Inventory_Rcvd => Null,
1246 p_Lot_Number_Rcvd => Null,
1247 p_Locator_Id => Null,
1248 p_picking_rule_id => Null,
1249 P_PROJECT_ID => Null,
1250 P_TASK_ID => Null,
1251 P_UNIT_NUMBER => Null);
1252 -- Add for R12 pickrule id change.Vijay.
1253
1254 If l_Return_Status <> FND_API.G_RET_STS_SUCCESS Then
1255 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1256 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
1257 'Error : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Defectives');
1258 End If;
1259 Raise FND_API.G_EXC_ERROR ;
1260 Else
1261 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1262 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
1263 'Success : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Defectives');
1264 End If;
1265 End If;
1266
1267 -- Insert into Product Transactions table , Insert a row for Action Type Move-Out
1268 l_Product_Trxn_Id := NULL ; -- This is just to make sure that new product trasaction id generated
1269 csd_product_transactions_pkg.Insert_Row(
1270 px_PRODUCT_TRANSACTION_ID => l_Product_Trxn_Id,
1271 p_REPAIR_LINE_ID => l_Repair_Line_Id ,
1272 p_ESTIMATE_DETAIL_ID => NULL,
1273 p_ACTION_TYPE => C_ACTION_TYPE_MOVE_OUT,
1274 p_ACTION_CODE => C_ACTION_CODE_USABLES,
1275 p_LOT_NUMBER => NULL,
1276 p_SUB_INVENTORY => l_IRandISO_Out_Rec.Destination_SubInventory,
1277 p_INTERFACE_TO_OM_FLAG => C_Yes,
1278 p_BOOK_SALES_ORDER_FLAG => C_Yes,
1279 p_RELEASE_SALES_ORDER_FLAG => NULL,
1280 p_SHIP_SALES_ORDER_FLAG => NULL,
1281 p_PROD_TXN_STATUS => C_PROD_TXN_STATUS_BOOKED,
1282 p_PROD_TXN_CODE => NULL , -- Need to identify what value to be passed
1283 p_LAST_UPDATE_DATE => Sysdate ,
1284 p_CREATION_DATE => Sysdate,
1285 p_LAST_UPDATED_BY => Fnd_Global.User_Id,
1286 p_CREATED_BY => Fnd_Global.User_Id,
1287 p_LAST_UPDATE_LOGIN => Fnd_Global.Login_Id,
1288 p_ATTRIBUTE1 => NULL ,
1289 p_ATTRIBUTE2 => NULL ,
1290 p_ATTRIBUTE3 => NULL ,
1291 p_ATTRIBUTE4 => NULL ,
1292 p_ATTRIBUTE5 => NULL ,
1293 p_ATTRIBUTE6 => NULL ,
1294 p_ATTRIBUTE7 => NULL ,
1295 p_ATTRIBUTE8 => NULL ,
1296 p_ATTRIBUTE9 => NULL ,
1297 p_ATTRIBUTE10 => NULL ,
1298 p_ATTRIBUTE11 => NULL ,
1299 p_ATTRIBUTE12 => NULL ,
1300 p_ATTRIBUTE13 => NULL ,
1301 p_ATTRIBUTE14 => NULL ,
1302 p_ATTRIBUTE15 => NULL ,
1303 p_CONTEXT => NULL ,
1304 p_OBJECT_VERSION_NUMBER => 1,
1305 p_Req_Header_Id => l_IRandISO_Out_Rec.Req_Header_Id,
1306 p_Req_Line_Id => l_IRandISO_Out_Rec.Req_Line_Id,
1307 p_Order_Header_Id => l_IRandISO_Out_Rec.Order_Header_Id,
1308 p_Order_Line_Id => l_IRandISO_Out_Rec.Line_Id ,
1309 p_Prd_Txn_Qty_Received => NULL, -- changing it from 0
1310 p_Prd_Txn_Qty_Shipped => 0 ,
1311 p_Source_Serial_Number => NULL,
1312 p_Source_Instance_Id => Null,
1313 p_Non_Source_Serial_Number => NULL,
1314 p_Non_Source_Instance_ID => Null,
1315 p_Sub_Inventory_Rcvd => Null,
1316 p_Lot_Number_Rcvd => Null,
1317 p_Locator_Id => Null,
1318 p_picking_rule_id => Null,
1319 P_PROJECT_ID => Null,
1320 P_TASK_ID => Null,
1321 P_UNIT_NUMBER => Null);
1322 -- Add for R12 pickrule id change.Vijay.
1323
1324 If l_Return_Status <> FND_API.G_RET_STS_SUCCESS Then
1325 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1326 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
1327 'Error : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Usables');
1328 End If;
1329 Raise FND_API.G_EXC_ERROR ;
1330 Else
1331 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1332 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'csd_product_transactions_pkg.Insert_Row',
1333 'Error : While calling procedure csd_product_transactions_pkg.Insert_Row 2 for Usables');
1334 End If;
1335 End If;
1336
1337
1338 End If;
1339
1340 -- Standard check of p_commit.
1341 IF FND_API.To_Boolean( p_commit ) THEN
1342 COMMIT WORK;
1343 END IF;
1344 If Fnd_Log.Level_Error >= Fnd_Log.G_Current_Runtime_Level Then
1345 Fnd_Log.String(Fnd_Log.Level_Error,C_Module || 'End',
1346 'End of procedure : Create_InternalRO');
1347 End If;
1348 Exception
1349 When FND_API.G_EXC_UNEXPECTED_ERROR Then
1350 -- If there is an error then rollback all database transactions for this API
1351 Rollback To Create_InternalRO;
1352 --- Standard call to get error messages if count is Null
1353 FND_MSG_PUB.Count_And_Get
1354 (p_count => x_msg_count,
1355 p_data => x_msg_data ) ;
1356 x_return_status := FND_API.G_RET_STS_ERROR ;
1357
1358 When FND_API.G_EXC_ERROR Then
1359 -- If there is an error then rollback all database transactions for this API
1360 Rollback To Create_InternalRO;
1361 --- Standard call to get error messages if count is Null
1362 FND_MSG_PUB.Count_And_Get
1363 (p_count => x_msg_count,
1364 p_data => x_msg_data );
1365 x_return_status := FND_API.G_RET_STS_ERROR ;
1366
1367 When Others Then
1368 -- If there is an error then rollback all database transactions for this API
1369 Rollback To Create_InternalRO;
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1371 IF FND_MSG_PUB.Check_Msg_Level
1372 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1373 THEN
1374 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME ,c_api_name );
1375 END IF;
1376 FND_MSG_PUB.Count_And_Get(p_count =>x_msg_count,
1377 p_data => x_msg_data );
1378
1379 End Create_InternalRO ;
1380
1381 End CSD_Refurbish_IRO_GRP ;
[Home] [Help]