[Home] [Help]
PACKAGE BODY: APPS.CSP_REPAIR_PO_GRP
Source
1 PACKAGE BODY CSP_REPAIR_PO_GRP AS
2 /* $Header: cspgrpob.pls 120.19.12020000.5 2013/04/12 20:21:58 vidsrini ship $ */
3 -- Purpose: To create Repair execution
4 -- Start of Comments
5 -- Package name : CSP_REPAIR_PO_GRP
6 -- Purpose : This package creates Repair Purchase Order Requisition and Reservation of defective parts.
7 -- History : 09-June-2005, Arul Joseph.
8 -- NOTE :
9 -- End of Comments
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_REPAIR_PO_GRP';
12 G_FILE_NAME CONSTANT VARCHAR2(30) := 'cspvprqb.pls';
13
14 PROCEDURE CREATE_REPAIR_PO
15 (p_api_version IN NUMBER
16 ,p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
17 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
18 ,P_repair_supplier_id IN NUMBER
19 ,P_repair_supplier_org_id IN NUMBER
20 ,P_repair_program IN VARCHAR2
21 ,P_dest_organization_id IN NUMBER
22 ,P_source_organization_id IN NUMBER
23 ,P_repair_to_item_id IN NUMBER
24 ,P_quantity IN NUMBER
25 ,P_need_by_date IN DATE
26 ,P_defective_parts_tbl IN CSP_REPAIR_PO_GRP.defective_parts_tbl_Type
27 ,P_Shipping_method IN varchar2 DEFAULT FND_API.G_MISS_CHAR
28 ,x_requisition_header_id OUT NOCOPY NUMBER
29 ,x_return_status OUT NOCOPY VARCHAR2
30 ,x_msg_count OUT NOCOPY NUMBER
31 ,x_msg_data OUT NOCOPY VARCHAR2
32 )
33 IS
34 l_api_version_number CONSTANT NUMBER := 1.0;
35 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REPAIR_PO';
36 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(2000);
39 l_commit VARCHAR2(1) := FND_API.G_FALSE;
40 l_user_id NUMBER;
41 l_login_id NUMBER;
42 l_today DATE;
43 EXCP_USER_DEFINED EXCEPTION;
44 l_check_existence NUMBER;
45
46 l_defective_parts_rec CSP_REPAIR_PO_GRP.defective_parts_rec_type;
47 l_defective_parts_tbl CSP_REPAIR_PO_GRP.defective_parts_tbl_Type;
48
49 l_reservation_rec CSP_SCH_INT_PVT.RESERVATION_REC_TYP;
50 l_out_reservation_rec CSP_REPAIR_PO_GRP.out_reserve_rec_type;
51 l_out_reservation_tbl CSP_REPAIR_PO_GRP.out_reserve_tbl_type;
52 x_reservation_id NUMBER;
53
54 l_header_rec CSP_PARTS_REQUIREMENT.header_rec_type;
55 l_line_rec CSP_PARTS_REQUIREMENT.Line_rec_type;
56 l_lines_tbl CSP_PARTS_REQUIREMENT.Line_Tbl_type;
57 l_repair_po_header_id NUMBER ;
58
59 L_repair_supplier_org_id NUMBER;
60 L_VENDOR_NAME Varchar2(240);
61 L_SOURCE_ORGANIZATION_NAME Varchar2(240);
62 L_REPAIR_ORGANIZATION_NAME Varchar2(240);
63 L_DEST_ORGANIZATION_NAME Varchar2(240);
64 ln_count NUMBER;
65 X_item_number VARCHAR2(40);
66
67 X_PRIMARY_UOM_CODE VARCHAR2(3);
68 l_REVISION_QTY_CONTROL_CODE NUMBER;
69 l_REVISION VARCHAR2(3);
70 X_ITEM_DESCRIPTION VARCHAR2(240);
71 l_ship_to_location_id NUMBER;
72 l_shipping_method_code VARCHAR2(30);
73 l_supplier_org_location_id NUMBER;
74
75 l_MEANING VARCHAR2(80);
76 l_LOOKUP_CODE NUMBER;
77 P_NOTE_ID NUMBER;
78 P_NOTE LONG;
79 X_Rowid VARCHAR2(2000);
80 X_document_id NUMBER;
81 X_media_id NUMBER;
82
83 x_repair_po_header_id NUMBER;
84 x_order_header_id NUMBER;
85 x_requisition_number VARCHAR2(20);
86
87 x_requisition_line_id NUMBER;
88 x_order_line_id NUMBER;
89 x_line_num NUMBER;
90 x_poreq_line_reservation_id NUMBER;
91 x_repair_po_line_id NUMBER;
92 l_repair_po_line_id NUMBER;
93 L_DEMAND_SOURCE_LINE_ID NUMBER;
94 BEGIN
95
96 SAVEPOINT CREATE_REPAIR_PO_PUB;
97
98 /*----------- Initialize Message List ------------ */
99 IF fnd_api.to_boolean(P_Init_Msg_List) THEN
100 FND_MSG_PUB.initialize;
101 END IF;
102
103 /*----------- Standard call to check for call compatibility --------- */
104 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
105 p_api_version,
106 l_api_name,
107 G_PKG_NAME) THEN
108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
109 END IF;
110
111 /* ------- Initialize Return Status --------- */
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113
114 l_defective_parts_tbl := p_defective_parts_tbl;
115
116 /* -------- User and Login Information -------- */
117 SELECT Sysdate INTO l_today FROM dual;
118 l_user_id := fnd_global.user_id;
119 l_login_id := fnd_global.login_id;
120
121 /* --------- Start Create Reservations for Defective Parts passed ----------- */
122 FOR I IN 1..l_defective_parts_tbl.COUNT
123 LOOP
124 l_defective_parts_rec := l_defective_parts_tbl(I);
125
126 l_reservation_rec.need_by_date := P_need_by_date;
127 l_reservation_rec.organization_id := P_source_organization_id; -- Defective Warehouse
128 l_reservation_rec.item_id := l_defective_parts_rec.defective_item_id;
129 l_reservation_rec.quantity_needed := l_defective_parts_rec.defective_quantity;
130 l_reservation_rec.sub_inventory_code := Null;
131 -- SELECT nvl(MAX(RESERVATION_ID),0) + 1 into l_demand_source_line_id FROM MTL_RESERVATIONS;
132 Select MTL_DEMAND_S.NEXTVAL INTO l_demand_source_line_id FROM DUAL;
133 l_reservation_rec.line_id := l_demand_source_line_id;
134
135 If l_defective_parts_rec.defective_quantity <= 0 then
136 FND_MESSAGE.SET_NAME ('CSP','CSP_INVALID_DEFECTIVE_QUANTITY');
137 FND_MSG_PUB.ADD;
138 RAISE EXCP_USER_DEFINED;
139 End if;
140
141 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
142 (P_source_organization_id
143 ,l_defective_parts_rec.defective_item_id
144 ,x_item_number
145 ,x_item_description
146 ,x_primary_uom_code
147 ,x_return_status
148 ,x_msg_data
149 ,x_msg_count
150 );
151 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
152 L_SOURCE_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_source_organization_id);
153 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_DEFECTORG');
154 FND_MESSAGE.SET_TOKEN ('DEFECTIVE_ORG_NAME', L_SOURCE_ORGANIZATION_NAME,TRUE);
155 FND_MSG_PUB.ADD;
156 RAISE EXCP_USER_DEFINED;
157 END IF;
158
159 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
160 (P_repair_supplier_org_id
161 ,l_defective_parts_rec.defective_item_id
162 ,x_item_number
163 ,x_item_description
164 ,x_primary_uom_code
165 ,x_return_status
166 ,x_msg_data
167 ,x_msg_count
168 );
169 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
170 L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
171 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFECTITEM_AT_REPAIRORG');
172 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_REPAIR_ORGANIZATION_NAME,TRUE);
173 FND_MSG_PUB.ADD;
174 RAISE EXCP_USER_DEFINED;
175 END IF;
176
177 l_reservation_rec.item_uom_code := X_PRIMARY_UOM_CODE;
178
179 x_reservation_id := csp_sch_int_pvt.create_reservation(
180 p_reservation_parts => l_reservation_rec,
181 x_return_status => l_return_status,
182 x_msg_data => l_msg_data
183 );
184
185 IF x_reservation_id = 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
186 RAISE EXCP_USER_DEFINED;
187 Elsif l_return_status = FND_API.G_RET_STS_SUCCESS THEN
188
189 /* This is for Insert the Reservation_id into CSP_REPAIR_PO_LINES table
190 all the reservation made for defective parts
191 */
192
193 l_out_reservation_rec.need_by_date := l_reservation_rec.need_by_date;
194 l_out_reservation_rec.organization_id := l_reservation_rec.organization_id; ---- Defective Warehouse
195 l_out_reservation_rec.item_id := l_reservation_rec.item_id; ---- Defective Item
196 l_out_reservation_rec.quantity_needed := l_reservation_rec.quantity_needed;
197 l_out_reservation_rec.sub_inventory_code := Null;
198 l_out_reservation_rec.line_id := Null;
199 l_out_reservation_rec.item_uom_code := l_reservation_rec.item_uom_code;
200
201 l_out_reservation_rec.reservation_id := x_reservation_id;
202
203 l_out_reservation_tbl(I) := l_out_reservation_rec;
204
205 If L_SOURCE_ORGANIZATION_NAME is null then
206 L_SOURCE_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_source_organization_id);
207 End if;
208
209 If p_note_id is null then
210 P_NOTE:= 'ITEM '||
211 'DESCRIPTION '||
212 'QUANTITY '||
213 'ORGANIZATION '||
214 fnd_global.newline||
215 x_item_number||' '||
216 x_item_description||' '||
217 l_reservation_rec.quantity_needed||' '||
218 L_SOURCE_ORGANIZATION_NAME;
219 Else
220 P_NOTE:= P_NOTE||
221 fnd_global.newline||
222 x_item_number||' '||
223 x_item_description||' '||
224 l_reservation_rec.quantity_needed||' '||
225 L_SOURCE_ORGANIZATION_NAME;
226 End if;
227
228 End if;
229 END LOOP;
230
231 ----- End Create Reservations for Defective Parts passed -----------
232
233
234 ----- Start Create PO_REQUISITION for the Repair to Item -----------
235 --------------------------------------------
236 /*Validate the Repair supplier*/
237 --------------------------------------------
238 Begin
239 Select VENDOR_NAME
240 into l_VENDOR_NAME
241 from po_vendors
242 where vendor_id = P_repair_supplier_id
243 --and nvl(start_date_active,sysdate) <= nvl(end_date_active,sysdate);
244 and NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
245 and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
246 Exception
247 When No_Data_Found then
248 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_VALID_REPAIR_SUPPLIER');
249 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ID',to_char(P_repair_supplier_id), TRUE);
250 FND_MSG_PUB.ADD;
251 RAISE EXCP_USER_DEFINED;
252 End;
253
254 ---------------------------------------------------------------
255 /* Get Repair supplier Organization Id based on Vendor Id */
256 ---------------------------------------------------------------
257 If P_repair_supplier_org_id is null then
258 Begin
259 Select organization_id
260 into L_repair_supplier_org_id ----> P_repair_supplier_org_id
261 from hr_organization_information
262 where ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
263 and ORG_INFORMATION3 = P_repair_supplier_id; ---> 1159 (Vendor Id parameter)
264 Exception
265 When No_Data_Found then
266 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIR_SUPPLIER_ORG');
267 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_NAME', l_VENDOR_NAME, TRUE);
268 --(OR) No organization is linked with the repair supplier Id.
269 FND_MSG_PUB.ADD;
270 RAISE EXCP_USER_DEFINED;
271 End;
272 End if;
273
274 /*
275 Begin
276 SELECT LOCATION_ID
277 INTO l_supplier_org_location_id
278 FROM HR_ORGANIZATION_UNITS
279 WHERE ORGANIZATION_ID = L_repair_supplier_org_id;
280 Exception
281 When no_data_found then
282 l_supplier_org_location_id := Null;
283 End;
284 */
285
286 /*
287 -------------------------------------------------------
288 Get Vendor Id based on Repair supplier Organization Id
289 -------------------------------------------------------
290 Begin
291 select a.vendor_id
292 from po_vendors a, hr_organization_information b
293 where b.organization_id = 3201 --> P_repair_supplier_org_id (Repair supplier Org Id parameter)
294 and a.vendor_id = b.ORG_INFORMATION3
295 and b.ORG_INFORMATION_CONTEXT = 'Customer/Supplier Association'
296 and NVL(a.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
297 and NVL(a.END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
298 Exception
299 When no_data_found then
300 FND_MESSAGE.SET_NAME ('CSP','CSP_MISSING_PARAMETERS');
301 FND_MESSAGE.SET_TOKEN ('PARAMETER', 'Repair Supplier ID cannot be null', TRUE);
302 FND_MSG_PUB.ADD;
303 RAISE EXCP_USER_DEFINED;
304 End;
305 */
306
307 --------------------------------------------------
308 /* VALIDATION for P_repair_program parameter */
309 --------------------------------------------------
310
311 Begin
312 Select MEANING, LOOKUP_CODE
313 into l_MEANING, l_LOOKUP_CODE
314 from mfg_lookups
315 Where LOOKUP_TYPE = 'INV_REPAIR_PROGRAMS' --'MRP_REPAIR_PROGRAM_DEFINITIONS'
316 and ENABLED_FLAG = 'Y'
317 and NVL(START_DATE_ACTIVE,SYSDATE) <= SYSDATE
318 and NVL(END_DATE_ACTIVE,SYSDATE) >= SYSDATE
319 and lookup_code = p_repair_program;
320 Exception
321 When no_data_found then
322 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_VALID_REPAIR_PROGRAM');
323 FND_MSG_PUB.ADD;
324 RAISE EXCP_USER_DEFINED;
325 End;
326
327 select csp_repair_po_headers_s1.nextval
328 into l_repair_po_header_id
329 from dual;
330
331 /* In "CSP_PARTS_ORDER.PROCESS_PURCHASE_REQ" API inserting into
332 "PO_REQUISITIONS_INTERFACE_ALL" table SOURCE_TYPE_CODE as "VENDOR".
333 So this item must be Purchasing_enabled in both Repair supplier org and destination org. */
334
335 L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
336
337 If L_REPAIR_ORGANIZATION_NAME is null then
338 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIR_SUPPLIER_ORG');
339 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_NAME', l_VENDOR_NAME, TRUE);
340 --(OR) No organization is linked with the repair supplier Id.
341 FND_MSG_PUB.ADD;
342 RAISE EXCP_USER_DEFINED;
343
344 Elsif L_REPAIR_ORGANIZATION_NAME is not null then
345 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
346 (P_repair_supplier_org_id
347 ,P_repair_to_item_id
348 ,x_item_number
349 ,x_item_description
350 ,x_primary_uom_code
351 ,x_return_status
352 ,x_msg_data
353 ,x_msg_count
354 );
355 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
356 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIRITEM_AT_REPAIRORG');
357 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG_NAME', L_REPAIR_ORGANIZATION_NAME,TRUE);
358 FND_MSG_PUB.ADD;
359 RAISE EXCP_USER_DEFINED;
360 END IF;
361
362 Begin
363 SELECT LOCATION_ID
364 INTO l_supplier_org_location_id
365 FROM HR_ORGANIZATION_UNITS
366 WHERE ORGANIZATION_ID = nvl(P_repair_supplier_org_id,L_repair_supplier_org_id);
367 Exception
368 When no_data_found then
369 --l_supplier_org_location_id := Null;
370 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_SHIPFROM_LOCATION_ID');
371 FND_MESSAGE.SET_TOKEN ('REPAIR_SUPPLIER_ORG', L_REPAIR_ORGANIZATION_NAME, TRUE);
372 FND_MSG_PUB.ADD;
373 RAISE EXCP_USER_DEFINED;
374 End;
375
376 End if;
377
378 /* Find the Ship to Location for the Destination Warehouse and
379 a customer must be associated with this location in Purchasing*/
380
381 L_DEST_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_dest_organization_id);
382 If L_DEST_ORGANIZATION_NAME is null then
383 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_VALID_DEST_ORG');
384 FND_MESSAGE.SET_TOKEN ('DEST_ORG_ID', to_char(P_dest_organization_id), TRUE);
385 FND_MSG_PUB.ADD;
386 RAISE EXCP_USER_DEFINED;
387 Elsif L_DEST_ORGANIZATION_NAME is not null then
388
389 CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS
390 (P_dest_organization_id
391 ,P_repair_to_item_id
392 ,x_item_number
393 ,x_item_description
394 ,x_primary_uom_code
395 ,x_return_status
396 ,x_msg_data
397 ,x_msg_count
398 );
399 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
400 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_REPAIRITEM_AT_DESTORG');
401 FND_MESSAGE.SET_TOKEN ('DEST_ORG_NAME', L_DEST_ORGANIZATION_NAME,TRUE);
402 FND_MSG_PUB.ADD;
403 RAISE EXCP_USER_DEFINED;
404 END IF;
405
406 Begin
407 SELECT LOCATION_ID
408 INTO l_ship_to_location_id
409 FROM HR_ORGANIZATION_UNITS
410 WHERE ORGANIZATION_ID = P_dest_organization_id;
411 Exception
412 When no_data_found then
413 --l_ship_to_location_id := Null;
414 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_SHIPTO_LOCATION_ID');
415 FND_MESSAGE.SET_TOKEN ('DESTINATION_ORG', L_DEST_ORGANIZATION_NAME, TRUE);
416 FND_MSG_PUB.ADD;
417 RAISE EXCP_USER_DEFINED;
418 End;
419 End if;
420 -------- No shipping network between Org's with Default shipping method assigned ---------
421 if (p_shipping_method is null) or (p_shipping_method = fnd_api.g_miss_char) then
422 Begin
423 SELECT SHIP_METHOD
424 INTO l_shipping_method_code
425 FROM MTL_INTERORG_SHIP_METHODS
426 WHERE FROM_ORGANIZATION_ID = P_repair_supplier_org_id
427 AND TO_ORGANIZATION_ID = P_dest_organization_id
428 AND FROM_LOCATION_ID = l_supplier_org_location_id
429 AND TO_LOCATION_ID = l_ship_to_location_id
430 AND DEFAULT_FLAG = 1;
431 Exception
432 When no_data_found then
433 L_REPAIR_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_repair_supplier_org_id);
434 L_DEST_ORGANIZATION_NAME := GET_ORGANIZATION_NAME(P_dest_organization_id);
435 FND_MESSAGE.SET_NAME ('CSP','CSP_NO_DEFAULT_SHIPPING_METHOD');
436 FND_MESSAGE.SET_TOKEN ('FROM_ORG', L_REPAIR_ORGANIZATION_NAME, TRUE);
437 FND_MESSAGE.SET_TOKEN ('TO_ORG', L_DEST_ORGANIZATION_NAME, TRUE);
438 FND_MSG_PUB.ADD;
439 RAISE EXCP_USER_DEFINED;
440 End;
441 else
442 l_shipping_method_code := p_shipping_method;
443 end if ;
444 If nvl(P_quantity,0) <= 0 then
445 FND_MESSAGE.SET_NAME ('CSP','CSP_INVALID_REPAIRPO_NEED_QTY');
446 FND_MSG_PUB.ADD;
447 RAISE EXCP_USER_DEFINED;
448 End if;
449
450 If trunc(nvl(P_need_by_date,sysdate)) <= trunc(sysdate) then
451 FND_MESSAGE.SET_NAME ('CSP','CSP_INVALID_REPAIRPO_NEED_DATE');
452 FND_MSG_PUB.ADD;
453 RAISE EXCP_USER_DEFINED;
454 End if;
455
456 l_header_rec.ship_to_location_id := l_ship_to_location_id;
457 l_header_rec.need_by_date := P_need_by_date;
458 l_header_rec.dest_organization_id := P_dest_organization_id;
459 l_header_Rec.requirement_header_id := l_repair_po_header_id;
460 l_header_rec.operation := csp_parts_order.G_OPR_CREATE; -- 'CREATE';
461
462 l_line_rec.inventory_item_id := P_repair_to_item_id;
463 l_line_rec.item_description := X_ITEM_DESCRIPTION;
464 l_line_rec.unit_of_measure := X_PRIMARY_UOM_CODE;
465
466 l_line_rec.shipping_method_code := l_shipping_method_code;
467 l_line_rec.ordered_quantity := P_quantity;
468
469 --l_line_rec.dest_subinventory := Null;
470
471 /* This value is needed for Iternal requisition only */
472
473 l_line_rec.source_organization_id := nvl(P_repair_supplier_org_id,L_repair_supplier_org_id);
474 --l_line_rec.source_location_id := l_supplier_org_location_id;
475
476 /*Add these parameters in the CSP_PARTS_REQUIREMENT.Header_Rec_Type and pass the repair_supplier_id to this parameter */
477
478 l_header_rec.suggested_vendor_id := P_repair_supplier_id;
479 l_header_rec.SUGGESTED_VENDOR_NAME := l_VENDOR_NAME;
480
481 l_lines_tbl(1) := l_line_rec;
482
483 ---------------------------------------------------------------------
484 /*
485 Call to Create PO Requisition.
486 This API Inserts record into PO_REQUISITIONS_INTERFACE_ALL table
487 */
488 ---------------------------------------------------------------------
489
490 /* REQUISITION_TYPE is passed as "PURCHASE" in the follwoing API.
491 but this is not in valid values such as BLANKET,PLANNED,SCHEDULED and STANDARD
492 */
493
494 --dbms_output.put_line('First l_header_rec.requisition_header_id '||l_header_rec.requisition_header_id);
495
496 l_header_rec.called_from := 'REPAIR_EXECUTION';
497 l_header_rec.justification := l_LOOKUP_CODE ||' - '||l_MEANING;
498 l_header_rec.note_to_buyer := l_LOOKUP_CODE ||' - '||l_MEANING;
499
500 /*
501 select po_notes_s.nextval
502 into p_note_id
503 from dual;
504
505 Insert into PO_NOTES
506 (
507 PO_NOTE_ID
508 ,LAST_UPDATE_DATE
509 ,LAST_UPDATED_BY
510 ,LAST_UPDATE_LOGIN
511 ,CREATION_DATE
512 ,CREATED_BY
513 ,TITLE
514 ,USAGE_ID
515 ,NOTE_TYPE
516 ,START_DATE_ACTIVE
517 ,END_DATE_ACTIVE
518 ,REQUEST_ID
519 ,PROGRAM_APPLICATION_ID
520 ,PROGRAM_ID
521 ,PROGRAM_UPDATE_DATE
522 ,DOCUMENT_ID
523 ,APP_SOURCE_VERSION
524 ,NOTE
525 )
526 VALUES
527 (
528 p_note_id
529 ,SYSDATE
530 ,l_user_id
531 ,l_login_id
532 ,SYSDATE
533 ,l_user_id
534 ,'REPAIR AND RETURN: DEFECTIVE PARTS DETAILS'
535 ,3 -- 'Note to Buyer'
536 ,'S'
537 ,SYSDATE
538 ,NULL
539 ,NULL
540 ,NULL
541 ,NULL
542 ,NULL
543 ,NULL --p_document_id
544 ,'1.0'
545 ,p_note
546 );
547 */
548 fnd_documents_pkg.Insert_Row
549 (X_Rowid => X_Rowid,
550 X_document_id => X_document_id,
551 X_creation_date => SYSDATE,
552 X_created_by => l_user_id,
553 X_last_update_date => SYSDATE,
554 X_last_updated_by => l_user_id,
555 X_last_update_login => l_login_id,
556 X_datatype_id => 2, -- Longtext
557 X_category_id => 34, -- To Buyer
558 X_security_type => 4, -- None
559 X_security_id => NULL,
560 X_publish_flag => 'Y',
561 X_image_type => NULL,
562 X_storage_type => NULL, -- 1
563 X_usage_type => 'O', -- 'S'(Standard)
564 X_start_date_active => SYSDATE,
565 X_end_date_active => NULL,
566 X_request_id => NULL,
567 X_program_application_id => NULL,
568 X_program_id => NULL,
569 X_program_update_date => NULL,
570 X_language => USERENV('LANG'),
571 X_description => 'Repair Purchase Order Defective Parts Details: '||L_REPAIR_PO_HEADER_ID,
572 X_file_name => NULL,
573 X_media_id => X_media_id,
574 X_Attribute_Category => NULL,
575 X_Attribute1 => NULL,
576 X_Attribute2 => NULL,
577 X_Attribute3 => NULL,
578 X_Attribute4 => NULL,
579 X_Attribute5 => NULL,
580 X_Attribute6 => NULL,
581 X_Attribute7 => NULL,
582 X_Attribute8 => NULL,
583 X_Attribute9 => NULL,
584 X_Attribute10 => NULL,
585 X_Attribute11 => NULL,
586 X_Attribute12 => NULL,
587 X_Attribute13 => NULL,
588 X_Attribute14 => NULL,
589 X_Attribute15 => NULL,
590 X_create_doc => 'N');
591
592 INSERT INTO fnd_documents_long_text
593 (MEDIA_ID, LONG_TEXT)
594 VALUES(X_media_id, p_note);
595
596 l_header_rec.NOTE1_ID := Null; --p_note_id;
597 l_header_rec.NOTE1_TITLE := 'Repair Purchase Order Defective Parts Details: '||L_REPAIR_PO_HEADER_ID;
598
599 CSP_PARTS_ORDER.process_purchase_req
600 (P_API_VERSION => 1.0
601 ,P_INIT_MSG_LIST => 'F'
602 ,P_COMMIT => 'F'
603 ,px_header_rec => l_header_rec
604 ,px_line_table => l_lines_tbl
605 ,x_return_status => l_return_status
606 ,x_msg_count => l_msg_count
607 ,x_msg_data => l_msg_data
608 );
609
610 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
611 RAISE FND_API.G_EXC_ERROR;
612 End if;
613
614 /* Out values from the header record (l_header_rec) of PO Requisition Call */
615
616 x_repair_po_header_id := l_header_Rec.requirement_header_id;
617 x_requisition_header_id := l_header_rec.requisition_header_id;
618 x_order_header_id := l_header_rec.order_header_id;
619 x_requisition_number := l_header_rec.requisition_number;
620
621 --dbms_output.put_line('second l_header_rec.requisition_header_id '||l_header_rec.requisition_header_id);
622
623 /* Out values from the lines table (l_lines_tbl) of PO Requisition Call */
624
625 -- This Loop may not be necessary because there is always a single requisition line
626 FOR I IN 1..l_lines_tbl.COUNT
627 LOOP
628 l_line_rec := l_lines_tbl(I);
629 --l_line_rec := l_lines_tbl(1);
630
631 x_requisition_line_id := l_line_rec.requisition_line_id;
632 x_order_line_id := l_line_rec.order_line_id;
633 x_line_num := l_line_rec.line_num;
634 x_poreq_line_reservation_id := l_line_rec.reservation_id;
635 x_repair_po_line_id := l_line_rec.requirement_line_id;
636
637 /* Insert into CSP_REPAIR_PO_HEADERS table the Requisition Header Id with the Item details */
638
639 INSERT INTO CSP_REPAIR_PO_HEADERS
640 (REPAIR_PO_HEADER_ID
641 ,REQUISITION_HEADER_ID
642 ,PURCHASE_ORDER_HEADER_ID
643 ,INTERNAL_ORDER_HEADER_ID
644 ,WIP_ID
645 ,STATUS
646 ,INVENTORY_ITEM_ID
647 ,QUANTITY
648 ,DEST_ORGANIZATION_ID
649 ,NEED_BY_DATE
650 ,REQUISITION_NUMBER
651 ,REQUISITION_LINE_ID
652 ,ORDER_LINE_ID
653 ,LINE_NUM
654 ,POREQ_LINE_RESERVATION_ID
655 ,POREQ_LINE_ID
656 ,ERROR_MESSAGE
657 ,REPAIR_PROGRAM
658 ,PO_NUMBER
659 ,REPAIR_SUPPLIER_ID
660 ,REPAIR_SUPPLIER_ORG_ID
661 ,RECEIVED_QTY
662 --,SCRAP_QTY
663 --,ADJUSTED_QTY
664 )
665 VALUES
666 (
667 x_repair_po_header_id,
668 x_requisition_header_id , -- (+ 1 is stored in PO_REQUISITIONS_INTERFACE_ALL)
669 NULL,
670 NULL,
671 NULL,
672 '1',
673 l_line_rec.inventory_item_id,
674 l_line_rec.ordered_quantity,
675 l_header_rec.dest_organization_id,
676 l_header_rec.need_by_date,
677 x_requisition_number, --( REQ_NUMBER_SEGMENT1 from PO_REQUISITIONS_INTERFACE_ALL)
678 x_requisition_line_id,
679 x_order_line_id, ---- NULL
680 x_line_num, ---- NULL
681 x_poreq_line_reservation_id,---- NULL
682 x_repair_po_line_id, ---- NULL
683 NULL,
684 P_repair_program,
685 NULL,--PO_NUMBER
686 P_repair_supplier_id, --repair_supplier_id
687 P_repair_supplier_org_id, --Use "L_repair_supplier_org_id" if only P_repair_supplier_id is passed
688 NULL--received_qty
689 --NULL,--scrap_qty
690 --NULL--adjusted_qty
691 );
692 END LOOP;
693
694 --COMMIT;
695
696 /* Insert into CSP_REPAIR_PO_LINES table all the reservation made for defective parts */
697
698 FOR I IN 1..l_out_reservation_tbl.COUNT
699 LOOP
700 l_out_reservation_rec := l_out_reservation_tbl(I);
701
702 select csp_repair_po_lines_s1.nextval
703 into l_repair_po_line_id
704 from dual;
705
706 INSERT INTO CSP_REPAIR_PO_LINES
707 (
708 REPAIR_PO_LINE_ID
709 ,REPAIR_PO_HEADER_ID
710 ,DEFECTIVE_ORGANIZATION_ID
711 ,INVENTORY_ITEM_ID
712 ,QUANTITY
713 ,RESERVATION_ID
714 )
715 VALUES
716 (
717 l_repair_po_line_id,
718 x_repair_po_header_id,
719 l_out_reservation_rec.organization_id, ------- Defective Warehouse
720 l_out_reservation_rec.item_id, -------- Defective Item
721 l_out_reservation_rec.quantity_needed, -------- Defective Item Qty
722 l_out_reservation_rec.reservation_id -------- Reservation Id for the Defective Item
723 --P_need_by_date -------- Same as the need by date of Repair to item
724 );
725 END LOOP;
726 /*
727 IF (p_commit = FND_API.G_TRUE) THEN
728 COMMIT;
729 END IF;
730 */
731
732 IF FND_API.to_Boolean( p_commit ) THEN
733 COMMIT WORK;
734 END IF;
735
736
737 EXCEPTION
738 WHEN EXCP_USER_DEFINED THEN
739 Rollback to CREATE_REPAIR_PO_PUB;
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 fnd_msg_pub.count_and_get
742 (p_count => x_msg_count
743 ,p_data => x_msg_data);
744 WHEN FND_API.G_EXC_ERROR THEN
745 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
746 P_API_NAME => L_API_NAME
747 ,P_PKG_NAME => G_PKG_NAME
748 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
749 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
750 ,X_MSG_COUNT => X_MSG_COUNT
751 ,X_MSG_DATA => X_MSG_DATA
752 ,X_RETURN_STATUS => X_RETURN_STATUS);
753 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
754 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
755 P_API_NAME => L_API_NAME
756 ,P_PKG_NAME => G_PKG_NAME
757 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
758 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
759 ,X_MSG_COUNT => X_MSG_COUNT
760 ,X_MSG_DATA => X_MSG_DATA
761 ,X_RETURN_STATUS => X_RETURN_STATUS);
762 WHEN OTHERS THEN
763 Rollback to CREATE_REPAIR_PO_PUB;
764 FND_MESSAGE.SET_NAME('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
765 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
766 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
767 FND_MSG_PUB.ADD;
768 fnd_msg_pub.count_and_get
769 (p_count => x_msg_count
770 ,p_data => x_msg_data);
771 x_return_status := FND_API.G_RET_STS_ERROR;
772 END;
773
774 FUNCTION GET_ORGANIZATION_NAME
775 (P_dest_organization_id NUMBER
776 ) return VARCHAR2 IS
777 L_DEST_ORGANIZATION_NAME VARCHAR2(240);
778
779 Cursor org_cur(P_dest_organization_id Number) is
780 Select haou.name
781 from hr_all_organization_units haou
782 where haou.organization_id = p_dest_organization_id;
783 BEGIN
784
785 OPEN org_cur(P_dest_organization_id);
786 LOOP
787 FETCH org_cur INTO L_DEST_ORGANIZATION_NAME;
788 EXIT WHEN org_cur%NOTFOUND;
789 END LOOP;
790 CLOSE org_cur;
791
792 return L_DEST_ORGANIZATION_NAME;
793 EXCEPTION
794 WHEN OTHERS THEN
795 L_DEST_ORGANIZATION_NAME := NULL;
796 return L_DEST_ORGANIZATION_NAME;
797 END;
798
799 PROCEDURE GET_ITEM_DETAILS
800 (P_organization_id IN NUMBER
801 ,P_inventory_item_id IN NUMBER
802 ,x_item_number OUT NOCOPY VARCHAR2
803 ,x_item_description OUT NOCOPY VARCHAR2
804 ,x_primary_uom_code OUT NOCOPY VARCHAR2
805 ,x_return_status OUT NOCOPY VARCHAR2
806 ,x_msg_data OUT NOCOPY VARCHAR2
807 ,x_msg_count OUT NOCOPY NUMBER) IS
808
809 Cursor item_cur (P_organization_id Number,P_inventory_item_id Number) is
810 Select MSIK.concatenated_segments item_number,
811 MSIK.description item_description,
812 MSIK.primary_uom_code
813 From mtl_system_items_kfv MSIK
814 Where MSIK.organization_id = P_organization_id
815 and MSIK.inventory_item_id = P_inventory_item_id
816 and sysdate between nvl(MSIK.start_date_active,sysdate)
817 and nvl(MSIK.end_date_active,sysdate);
818
819 l_api_name VARCHAR2(60) := 'CSP_REPAIR_PO_GRP.GET_ITEM_DETAILS';
820
821 BEGIN
822 x_return_status := FND_API.G_RET_STS_SUCCESS;
823 OPEN item_cur(P_organization_id,P_inventory_item_id);
824 LOOP
825 FETCH item_cur INTO x_item_number,x_item_description,x_primary_uom_code;
826 EXIT WHEN item_cur%NOTFOUND;
827 END LOOP;
828 CLOSE item_cur;
829 IF x_item_number is NULL then
830 x_return_status := FND_API.G_RET_STS_ERROR;
831 END IF;
832 EXCEPTION
833 WHEN OTHERS THEN
834 FND_MESSAGE.SET_NAME('CSP','CSP_UNEXPECTED_EXEC_ERRORS');
835 FND_MESSAGE.SET_TOKEN('ROUTINE', l_api_name, TRUE);
836 FND_MESSAGE.SET_TOKEN('SQLERRM', sqlerrm, TRUE);
837 FND_MSG_PUB.ADD;
838 fnd_msg_pub.count_and_get
839 (p_count => x_msg_count
840 ,p_data => x_msg_data);
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
842 return;
843 END;
844
845
846 Procedure CREATE_CSP_SNAP_LOG IS
847
848 lv_dummy1 VARCHAR2(2000);
849 lv_dummy2 VARCHAR2(2000);
850 lv_inv_schema VARCHAR2(32);
851 lv_retval BOOLEAN;
852 v_applsys_schema VARCHAR2(200);
853
854 FUNCTION GET_SCHEMA_NAME( p_apps_id IN NUMBER)
855 RETURN VARCHAR2 IS
856 lv_schema VARCHAR2(30);
857 lv_prod_short_name VARCHAR2(30);
858 lv_retval boolean;
859 lv_dummy1 varchar2(32);
860 lv_dummy2 varchar2(32);
861 lv_is_new_ts_mode VARCHAR2(10);
862 BEGIN
863
864 ad_tspace_util.is_new_ts_mode(lv_is_new_ts_mode);
865 IF(upper(lv_is_new_ts_mode) = 'N') THEN
866 SELECT a.oracle_username
867 INTO lv_schema
868 FROM FND_ORACLE_USERID a,
869 FND_PRODUCT_INSTALLATIONS b
870 WHERE a.oracle_id = b.oracle_id
871 AND b.application_id = p_apps_id;
872
873 ELSE
874 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(p_apps_id);
875 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
876 END IF;
877
878 RETURN lv_schema;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 raise_application_error(-20001, 'Error getting the Schema : ' || sqlerrm);
883 END GET_SCHEMA_NAME;
884
885 PROCEDURE CREATE_SNAP_LOG( p_schema in VARCHAR2,
886 p_table in VARCHAR2,
887 p_applsys_schema IN VARCHAR2)
888 IS
889 v_sql_stmt VARCHAR2(6000);
890 BEGIN
891
892 v_sql_stmt:=
893 ' CREATE SNAPSHOT LOG ON '||p_schema ||'.'||p_table||' WITH ROWID ' ;
894
895 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
896 application_short_name => p_schema,
897 statement_type => AD_DDL.CREATE_TABLE,
898 statement => v_sql_stmt,
899 object_name => p_table);
900
901 EXCEPTION
902 WHEN OTHERS THEN
903
904 IF SQLCODE IN (-12000) THEN
905 /*Snapshot Log already EXISTS*/
906 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Snapshot Log on ' ||p_table||' already exists...');
907
908 ELSIF SQLCODE IN (-00942) THEN
909 /*Base Table does not exist*/
910 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Table '||p_table||' does not exist...');
911 ELSE
912 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
913 END IF;
914 END CREATE_SNAP_LOG; --create_snap Log
915
916
917 begin
918 lv_retval := FND_INSTALLATION.GET_APP_INFO(
919 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
920 lv_inv_schema := GET_SCHEMA_NAME(523);
921 CREATE_SNAP_LOG (lv_inv_schema,'CSP_REPAIR_PO_HEADERS',v_applsys_schema);
922 end CREATE_CSP_SNAP_LOG ;
923
924
925 Procedure create_csp_index (p_sql_stmt IN varchar2,p_object IN varchar2) is
926 lv_dummy1 VARCHAR2(2000);
927 lv_dummy2 VARCHAR2(2000);
928 lv_retval BOOLEAN;
929 v_applsys_schema VARCHAR2(200);
930 lv_prod_short_name VARCHAR2(30);
931
932 begin
933 lv_retval := FND_INSTALLATION.GET_APP_INFO(
934 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
935
936 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(523);
937 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
938 application_short_name => lv_prod_short_name,
939 statement_type => AD_DDL.CREATE_INDEX,
940 statement => p_sql_stmt,
941 object_name => p_object);
942
943
944 EXCEPTION
945 WHEN OTHERS THEN
946 FND_FILE.PUT_LINE(FND_FILE.LOG, SQLERRM);
947 RAISE;
948 end create_csp_index;
949
950
951
952
953 END CSP_REPAIR_PO_GRP;