[Home] [Help]
PACKAGE BODY: APPS.OKE_FORM_DD250
Source
1 PACKAGE BODY OKE_FORM_DD250 AS
2 /* $Header: OKEMIRRB.pls 120.5 2005/07/14 16:07:55 ausmani noship $ */
3
4 --
5 -- Global Declarations
6 --
7 G_PKG_NAME VARCHAR2(30) := 'OKE_FORM_DD250';
8 g_module CONSTANT VARCHAR2(250) := 'oke.plsql.oke_form_dd20.';
9
10 --
11 -- Private Procedures and Functions
12 --
13
14 --
15 -- Raise Business Event to generate XML
16 --
17 PROCEDURE Raise_Business_Event
18 ( P_Contract_Number IN VARCHAR2
19 , P_Order_Number IN VARCHAR2
20 , P_Shipment_Number IN VARCHAR2
21 , P_Form_Header_ID IN NUMBER
22 ) IS
23
24 MapCode VARCHAR2(30) := 'OKE_DD250_DLF10_OUT';
25 TxnType VARCHAR2(30) := 'ECX';
26 EventName VARCHAR2(80) := 'oracle.apps.oke.forms.DD250.Generate';
27
28 ParamList wf_parameter_list_t := wf_parameter_list_t();
29 l_org_id NUMBER; -- for MOAC
30
31 cursor c_org is
32 select authoring_org_id
33 from oke_k_headers_v
34 where k_header_id =P_form_Header_id;
35
36
37 BEGIN
38 --
39 -- Building Parameter List
40 --
41 OPEN c_org;
42 FETCH c_org INTO l_org_id;
43 CLOSE c_org;
44
45 wf_event.AddParameterToList( p_name => 'ECX_MAP_CODE'
46 , p_value => MapCode
47 , p_parameterList => ParamList );
48
49 wf_event.AddParameterToList( p_name => 'ECX_TRANSACTION_TYPE'
50 , p_value => TxnType
51 , p_parameterList => ParamList );
52
53 wf_event.AddParameterToList( p_name => 'ECX_DOCUMENT_ID'
54 , p_value => P_Form_Header_ID
55 , p_parameterList => ParamList );
56
57 wf_event.AddParameterToList( p_name => 'ECX_PARAMETER1'
58 , p_value => P_Contract_Number
59 , p_parameterList => ParamList );
60
61 wf_event.AddParameterToList( p_name => 'ECX_PARAMETER2'
62 , p_value => P_Order_Number
63 , p_parameterList => ParamList );
64
65 wf_event.AddParameterToList( p_name => 'ECX_PARAMETER3'
66 , p_value => P_Shipment_Number
67 , p_parameterList => ParamList );
68
69 wf_event.AddParameterToList( p_name => 'ECX_PARAMETER4'
70 , p_value => to_char(sysdate , 'DDMONRRHH24MISS')
71 , p_parameterList => ParamList );
72
73 wf_event.AddParameterToList( p_name => 'ECX_PARAMETER5'
74 , p_value => FND_GLOBAL.User_Name
75 , p_parameterList => ParamList );
76
77 wf_event.AddParameterToList( p_name => 'ORG_ID'
78 , p_value => l_org_ID
79 , p_parameterList => ParamList );
80
81
82 IF ( NVL( FND_PROFILE.VALUE('AFLOG_ENABLED') , 'N' ) = 'Y' ) THEN
83 wf_event.AddParameterToList( p_name => 'ECX_DEBUG_LEVEL'
84 , p_value => '3'
85 , p_parameterList => ParamList );
86 END IF;
87
88 --
89 -- Raise Event
90 --
91 wf_event.Raise( p_event_name => EventName
92 , p_event_key => to_char(sysdate , 'YYYYMMDD HH24MISS')
93 , p_parameters => ParamList );
94
95 ParamList.DELETE;
96
97 EXCEPTION
98 WHEN OTHERS THEN
99 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
100 FND_MSG_PUB.add_exc_msg
101 ( p_pkg_name => G_PKG_NAME
102 , p_procedure_name => 'RAISE_BUSINESS_EVENT' );
103 END IF;
104 Raise;
105
106 END Raise_Business_Event;
107
108
109 --
110 -- Assign Shipment Number based on DFARS
111 --
112 FUNCTION Shipment_Number
113 ( P_K_Header_ID IN NUMBER
114 , P_Inv_Org_ID IN NUMBER
115 , P_Delivery_ID IN NUMBER
116 , P_Ship_From_Loc_ID IN NUMBER
117 ) RETURN VARCHAR2 IS
118
119 ShipNumPfx VARCHAR2(30);
120 LastShipNum VARCHAR2(30);
121 NextShipNum VARCHAR2(30);
122
123 CURSOR ShipNumPrefix IS
124 SELECT rpad(Organization_Code , 3 , 'X')
125 FROM mtl_parameters
126 WHERE organization_id = P_Inv_Org_ID;
127
128 CURSOR ShipNum IS
129 SELECT MAX(Form_Header_Number)
130 FROM oke_k_form_headers
131 WHERE k_header_id = P_K_Header_ID
132 AND print_form_code = 'DD250'
133 AND substr(form_header_number , 1 , 3 ) = ShipNumPfx;
134
135 BEGIN
136
137 fnd_file.put_line(fnd_file.log , '... Assigning new shipment number');
138
139 --
140 -- Getting prefix from user extension. If none, derive it from org
141 --
142 ShipNumPfx := OKE_FORM_DD250_EXT.Override_Shipment_Prefix
143 ( P_K_Header_ID => P_K_Header_ID
144 , P_Delivery_ID => P_Delivery_ID
145 , P_Inv_Org_ID => P_Inv_Org_ID
146 , P_Ship_From_Loc_ID => P_Ship_From_Loc_ID );
147
148 if ( ShipNumPfx is NULL ) then
149 OPEN ShipNumPrefix;
150 FETCH ShipNumPrefix INTO ShipNumPfx;
151 CLOSE ShipNumPrefix;
152 end if;
153
154 fnd_file.put_line(fnd_file.log , 'Shipment Prefix = ' || ShipNumPfx);
155
156 OPEN ShipNum;
157 FETCH ShipNum INTO LastShipNum;
158 CLOSE ShipNum;
159
160 fnd_file.put_line(fnd_file.log , 'Last Shipment Num = ' || LastShipNum);
161
162 IF ( LastShipNum IS NULL ) THEN
163 --
164 -- This is the first DD250 for this contract and org
165 --
166 NextShipNum := ShipNumPfx || '0001';
167
168 ELSE
169 --
170 -- Need to create new number, increment numeric portion (char 4 to 7) by 1
171 --
172 NextShipNum := ShipNumPfx ||
173 lpad( to_number( substr( LastShipNum , 4 , 4 ) ) + 1
174 , 4 , '0' );
175
176 END IF;
177
178 fnd_file.put_line(fnd_file.log , 'Next Shipment Num = ' || NextShipNum);
179
180 RETURN ( NextShipNum );
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 Raise;
185 END Shipment_Number;
186
187
188 --
189 -- Name : Create_DD250
190 -- Pre-reqs : None
191 -- Function : This procedure creates a copy of DD250
192 --
193 --
194 -- Parameters :
195 -- IN : P_COMMIT VARCHAR2
196 -- P_HEADER_REC HDR_REC_TYPE
197 -- P_LINE_TBL LINE_TBL_TYPE
198 -- OUT : X_RETURN_STATUS VARCHAR2
199 -- X_MSG_COUNT NUMBER
200 -- X_MSG_DATA VARCHAR2
201 --
202 -- Returns : None
203 --
204
205 PROCEDURE Create_DD250
206 ( P_Commit IN VARCHAR2
207 , P_Hdr_Rec IN Hdr_Rec_Type
208 , P_Line_Tbl IN Line_Tbl_Type
209 , X_Msg_Count OUT NOCOPY NUMBER
210 , X_Msg_Data OUT NOCOPY VARCHAR2
211 , X_Return_Status OUT NOCOPY VARCHAR2
212 ) IS
213
214 l_hdr_rec Hdr_Rec_Type;
215 l_line_tbl Line_Tbl_Type;
216
217 pfh_rec OKE_PRINT_FORM_PUB.PFH_Rec_Type;
218 pfl_tbl OKE_PRINT_FORM_PUB.PFL_Tbl_Type;
219 i NUMBER;
220
221 CURSOR knum1
222 ( C_K_Header_ID NUMBER
223 ) IS
224 SELECT EK.K_Header_ID Contract_Header_ID
225 , CK2.Contract_Number Contract_Number
226 , DECODE( EK.BOA_ID ,
227 NULL , NULL ,
228 CK.Contract_Number ) Order_Number
229 FROM okc_k_headers_all_b CK2
230 , okc_k_headers_all_b CK
231 , oke_k_headers EK
232 WHERE EK.K_Header_ID = C_K_Header_ID
233 AND CK.ID = EK.K_Header_ID
234 AND CK2.ID = NVL(EK.BOA_ID , EK.K_Header_ID);
235
236 CURSOR knum2
237 ( C_K_Header VARCHAR2
238 , C_K_Type VARCHAR2
239 , C_Buy_Or_Sell VARCHAR2
240 ) IS
241 SELECT EK.K_Header_ID Contract_Header_ID
242 , CK2.Contract_Number Contract_Number
243 , DECODE( EK.BOA_ID ,
244 NULL , NULL ,
245 CK.Contract_Number ) Order_Number
246 FROM okc_k_headers_all_b CK2
247 , okc_k_headers_all_b CK
248 , oke_k_headers EK
249 WHERE EK.K_Number_Disp = C_K_Header
250 AND EK.K_Type_Code = C_K_Type
251 AND CK.ID = EK.K_Header_ID
252 AND CK.Buy_Or_Sell = C_Buy_Or_Sell
253 AND CK2.ID = NVL(EK.BOA_ID , EK.K_Header_ID);
254
255 ContractNum VARCHAR2(120);
256 OrderNum VARCHAR2(120);
257 ContractHdrID NUMBER;
258
259 CURSOR ShipmentNum
260 ( C_K_Header_ID NUMBER
261 , C_Shipment_Num VARCHAR2
262 ) IS
263 SELECT Form_Header_ID
264 FROM oke_k_form_headers
265 WHERE k_header_id = C_K_Header_ID
266 AND print_form_code = 'DD250'
267 AND form_header_number = C_Shipment_Num;
268
269 FormHdrID NUMBER := NULL;
270
271 progress NUMBER;
272
273 BEGIN
274
275 progress := 10;
276
277 IF ( p_hdr_rec.Contract_Header_ID IS NOT NULL ) THEN
278
279 OPEN knum1 ( p_hdr_rec.Contract_Header_ID );
280 FETCH knum1 INTO ContractHdrID, ContractNum , OrderNum;
281 CLOSE knum1;
282
283 ELSE
284
285 OPEN knum2 ( p_hdr_rec.Contract_Number
286 , p_hdr_rec.K_Type_Code
287 , p_hdr_rec.Buy_Or_Sell );
288 FETCH knum2 INTO ContractHdrID, ContractNum , OrderNum;
289 CLOSE knum2;
290
291 END IF;
292
293 --
294 -- Calling User Extension to override any collected data
295 --
296 progress := 15;
297
298 OKE_FORM_DD250_EXT.Override_Form_Data
299 ( P_K_Header_ID => ContractHdrID
300 , P_Delivery_ID => l_hdr_rec.Reference1
301 , P_Hdr_Rec => p_hdr_rec
302 , P_Line_Tbl => p_line_tbl
303 , X_Hdr_Rec => l_hdr_rec
304 , X_Line_Tbl => l_line_tbl
305 );
306
307 --
308 -- Preparing Form Header Record
309 --
310 progress := 20;
311
312 pfh_rec.Print_Form_Code := 'DD250';
313 pfh_rec.Form_Header_Number := l_hdr_rec.Shipment_Number;
314 pfh_rec.Form_Date := sysdate;
315 pfh_rec.Contract_Header_ID := ContractHdrID;
316 pfh_rec.status_code := 'CREATED';
317 pfh_rec.text01 := ContractNum;
318 pfh_rec.text02 := OrderNum;
319 pfh_rec.text03 := l_hdr_rec.Shipment_Number;
320 pfh_rec.text04 := l_hdr_rec.Bill_Of_Lading;
321 pfh_rec.text06 := l_hdr_rec.Discount_Terms;
322 IF ( substr(l_hdr_rec.Acceptance_Point,1,1) IN ( 'S' , 'D' ) ) THEN
323 pfh_rec.text07 := substr(l_hdr_rec.Acceptance_Point , 1 , 1);
324 END IF;
325 pfh_rec.text08 := l_hdr_rec.Contractor;
326 pfh_rec.text09 := l_hdr_rec.Contractor_Code;
327 pfh_rec.text10 := l_hdr_rec.Customer;
328 pfh_rec.text11 := l_hdr_rec.Customer_Code;
329 pfh_rec.text12 := l_hdr_rec.Ship_From;
330 pfh_rec.text13 := l_hdr_rec.Ship_From_Code;
331 IF ( substr(l_hdr_rec.FOB,1,1) IN ( 'S' , 'O' , 'D' ) ) THEN
332 pfh_rec.text14 := substr(l_hdr_rec.FOB , 1 , 1);
333 END IF;
334 pfh_rec.text15 := l_hdr_rec.Paid_By;
335 pfh_rec.text16 := l_hdr_rec.Paid_By_Code;
336 pfh_rec.text17 := l_hdr_rec.Ship_To;
337 pfh_rec.text18 := l_hdr_rec.Ship_To_Code;
338 pfh_rec.text19 := l_hdr_rec.Mark_For;
339 pfh_rec.text20 := l_hdr_rec.Mark_For_Code;
340 -- Bug 3268438 - check if Acceptance Method is a valid value
341 IF (l_hdr_rec.Acceptance_Method IN ('GOVTREP','FASTPAY','EDI','WAWF','COC','INTERNAL') ) THEN
342 pfh_rec.text21 := l_hdr_rec.Acceptance_Method;
343 END IF;
344 IF ( substr(l_hdr_rec.Inspection_Point,1,1) IN ( 'S' , 'D' , 'N' ) ) THEN
345 pfh_rec.text22 := substr(l_hdr_rec.Inspection_Point , 1 , 1);
346 END IF;
347 pfh_rec.text23 := l_hdr_rec.Weight_UOM_Code;
348 pfh_rec.text24 := l_hdr_rec.Volume_UOM_Code;
349 pfh_rec.text26 := l_hdr_rec.Ship_Method;
350 pfh_rec.date01 := l_hdr_rec.Shipment_Date;
351 pfh_rec.number01 := l_hdr_rec.Gross_Weight;
352 pfh_rec.number02 := l_hdr_rec.Net_Weight;
353 pfh_rec.number03 := l_hdr_rec.Volume;
354 pfh_rec.number04 := NULL; /* Number of Containers */
355 pfh_rec.Reference1 := l_hdr_rec.Reference1;
356 pfh_rec.Reference2 := l_hdr_rec.Reference2;
357 pfh_rec.Reference3 := l_hdr_rec.Reference3;
358 pfh_rec.Reference4 := l_hdr_rec.Reference4;
359 pfh_rec.Reference5 := l_hdr_rec.Reference5;
360
361 i := l_line_tbl.FIRST;
362
363 LOOP
364
365 progress := 30;
366
367 pfl_tbl(i).Form_Line_Number := i;
368 pfl_tbl(i).text01 := l_line_tbl(i).Line_Number;
369 pfl_tbl(i).text02 := l_line_tbl(i).Item_Number;
370 pfl_tbl(i).text03 := l_line_tbl(i).UOM;
371 pfl_tbl(i).text04 := l_line_tbl(i).Natl_Stock_Number;
372 pfl_tbl(i).text05 := l_line_tbl(i).Item_Description;
373 pfl_tbl(i).text06 := l_line_tbl(i).Line_Description;
374 pfl_tbl(i).text07 := l_line_tbl(i).Line_Comments;
375 pfl_tbl(i).number01 := l_line_tbl(i).Shipped_Quantity;
376 pfl_tbl(i).number02 := l_line_tbl(i).Unit_Price;
377 pfl_tbl(i).number03 := l_line_tbl(i).Amount;
378 pfl_tbl(i).reference1 := l_line_tbl(i).Reference1;
379 pfl_tbl(i).reference2 := l_line_tbl(i).Reference2;
380 pfl_tbl(i).reference3 := l_line_tbl(i).Reference3;
381 pfl_tbl(i).reference4 := l_line_tbl(i).Reference4;
382 pfl_tbl(i).reference5 := l_line_tbl(i).Reference5;
383
384 EXIT WHEN i = l_line_tbl.LAST;
385 i := l_line_tbl.NEXT(i);
386
387 END LOOP;
388
389 progress := 40;
390
391 --
392 -- Check to see if DD250 record exists for this shipment
393 --
394 OPEN ShipmentNum( l_hdr_rec.Contract_Header_ID
395 , l_hdr_rec.Shipment_Number );
396 FETCH ShipmentNum INTO FormHdrID;
397 CLOSE ShipmentNum;
398
399 IF ( FormHdrID IS NULL ) THEN
400 --
401 -- Form does not exist, create a new one
402 --
403 progress := 50;
404
405 OKE_PRINT_FORM_PUB.Create_Print_Form
406 ( p_api_version => 1.0
407 , p_commit => FND_API.G_FALSE
408 , p_init_msg_list => FND_API.G_FALSE
409 , x_msg_count => X_Msg_Count
410 , x_msg_data => X_Msg_Data
411 , x_return_status => X_Return_Status
412 , p_header_rec => pfh_rec
413 , p_line_tbl => pfl_tbl
414 , x_form_header_id => FormHdrID
415 );
416
417 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422
423 ELSE
424 --
425 -- Form exist, update it
426 --
427 progress := 60;
428
429 pfh_rec.Form_Header_ID := FormHdrID;
430
431 progress := 70;
432
433 OKE_PRINT_FORM_PUB.Update_Print_Form
434 ( p_api_version => 1.0
435 , p_commit => FND_API.G_FALSE
436 , p_init_msg_list => FND_API.G_FALSE
437 , x_msg_count => X_Msg_Count
438 , x_msg_data => X_Msg_Data
439 , x_return_status => X_Return_Status
440 , p_header_rec => pfh_rec
441 , p_line_tbl => pfl_tbl
442 );
443
444 IF ( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
446 ELSIF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
447 RAISE FND_API.G_EXC_ERROR;
448 END IF;
449
450 END IF;
451
452 progress := 80;
453
454 pfl_tbl.DELETE;
455
456 progress := 90;
457
458 Raise_Business_Event( P_Contract_Number => ContractNum
459 , P_Order_Number => OrderNum
460 , P_Shipment_Number => l_hdr_rec.Shipment_Number
461 , P_Form_Header_ID => FormHdrID
462 );
463
464 fnd_file.put_line(fnd_file.log , '... Business Event raised');
465
466 EXCEPTION
467 WHEN FND_API.G_EXC_ERROR THEN
468 x_return_status := FND_API.G_RET_STS_ERROR;
469 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
470 , p_data => x_msg_data );
471
472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
475 , p_data => x_msg_data );
476
477 WHEN OTHERS THEN
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
480 FND_MSG_PUB.add_exc_msg
481 ( p_pkg_name => G_PKG_NAME
482 , p_procedure_name => 'CREATE_DD250(' || progress || ')');
483 END IF;
484 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
485 , p_data => x_msg_data );
486
487 END Create_DD250;
488
489
490 --
491 -- Name : Create_DD250_From_Delivery
492 -- Pre-reqs : run as concurrent request
493 -- Function : This procedure creates a copy of DD250 for a delivery
494 --
495 --
496 -- Parameters :
497 -- IN : P_DELIVERY_ID NUMBER
498 -- OUT : X_RETURN_STATUS VARCHAR2
499 -- X_MSG_COUNT NUMBER
500 -- X_MSG_DATA VARCHAR2
501 --
502 -- Returns : None
503 --
504
505 PROCEDURE Create_DD250_From_Delivery
506 ( P_Delivery_ID IN NUMBER
507 , X_Msg_Count OUT NOCOPY NUMBER
508 , X_Msg_Data OUT NOCOPY VARCHAR2
509 , X_Return_Status OUT NOCOPY VARCHAR2
510 ) IS
511
512 CURSOR FrmHdr IS
513 SELECT DISTINCT
514 DTL.Source_Header_ID K_Header_ID
515 , DTL.Source_Line_ID Deliverable_ID
516 , DTL.Organization_ID Inv_Org_ID
517 , DTL.Ship_To_Location_ID Ship_To_Loc
518 , DTL.Ship_From_Location_ID Ship_From_Loc
519 , DLV.FOB_Code FOB_Code
520 , DLV.Gross_Weight Gross_Weight
521 , DLV.Net_Weight Net_Weight
522 , DLV.Weight_UOM_Code Weight_UOM_Code
523 , DLV.Volume Volume
524 , DLV.Volume_UOM_Code Volume_UOM_Code
525 FROM wsh_delivery_details DTL
526 , wsh_delivery_assignments_v ASGN
527 , wsh_new_deliveries DLV
528 WHERE DLV.delivery_id = P_Delivery_ID
529 AND ASGN.delivery_id = DLV.delivery_id
530 AND DTL.delivery_detail_id = ASGN.delivery_detail_id
531 AND DTL.source_code = 'OKE'
532 ORDER BY 1, 2, 4;
533
534 FrmHdrRec FrmHdr%RowType;
535
536
537 CURSOR FrmLine
538 ( C_Delivery_ID NUMBER
539 , C_Inv_Org_ID NUMBER
540 , C_K_Hdr_ID NUMBER
541 , C_Ship_To NUMBER
542 ) IS
543 SELECT DTL.Source_Line_ID Deliverable_ID
544 , DTL.Inventory_Item_ID Item_ID
545 , DTL.Item_Description Item_Description
546 , DTL.Shipped_Quantity Shipped_Qty
547 , DTL.Serial_Number Serial_Num
548 FROM wsh_delivery_details DTL
549 , wsh_delivery_assignments_v ASGN
550 WHERE ASGN.delivery_id = C_Delivery_ID
551 AND DTL.Organization_ID = C_Inv_Org_ID
552 AND DTL.Source_Header_ID = C_K_Hdr_ID
553 -- AND DTL.Ship_To_Location_ID = C_Ship_To
554 AND DTL.delivery_detail_id = ASGN.delivery_detail_id
555 AND DTL.source_code = 'OKE';
556
557 CURSOR PrintForm
558 ( C_K_Header_ID NUMBER
559 ) IS
560 SELECT 1
561 FROM oke_k_print_forms
562 WHERE k_header_id = C_K_Header_ID
563 AND print_form_code = 'DD250';
564
565 CURSOR ShipmentNum
566 ( C_K_Header_ID NUMBER
567 , C_Delivery_ID NUMBER
568 , C_Inv_Org_ID NUMBER
569 ) IS
570 SELECT Form_Header_Number
571 FROM oke_k_form_headers
572 WHERE k_header_id = C_K_Header_ID
573 AND print_form_code = 'DD250'
574 AND reference1 = C_Delivery_ID
575 AND reference2 = C_Inv_Org_ID;
576
577 CURSOR LineInfo
578 ( C_Deliverable_ID NUMBER
579 ) IS
580 SELECT L.Line_Number Line_Number
581 , I.Item_Number Item_Number
582 , L.NSN_Number Natl_Stock_Number
583 , I.Description Item_Description
584 , L.Line_Description Line_Description
585 , L.Comments Line_Comments
586 , nvl( D.Unit_Price , L.Unit_Price ) Unit_Price
587 , nvl( D.UOM_Code , L.UOM_Code ) UOM_Code
588 FROM oke_k_deliverables_b D
589 , oke_k_lines_v L
590 , mtl_item_flexfields I
591 WHERE D.Deliverable_ID = C_Deliverable_ID
592 AND L.K_Line_ID = D.K_Line_ID
593 AND I.Organization_ID (+) = D.Ship_From_Org_ID
594 AND I.Inventory_Item_ID (+) = D.Item_ID;
595
596 LineInfoRec LineInfo%rowtype;
597
598 CURSOR BillLading IS
599 SELECT WDI.Sequence_Number BOL_Number
600 -- , WDI.BOL_Issue_Office BOL_Issue_Office
601 -- , WDI.BOL_Issued_By BOL_Issued_By
602 -- , WDI.BOL_Date_Issued BOL_Date_Issued
603 FROM wsh_document_instances WDI
604 , wsh_delivery_legs WDL
605 WHERE WDL.Delivery_ID = P_Delivery_ID
606 AND WDI.Entity_ID = WDL.Delivery_Leg_ID
607 AND WDI.Entity_Name = 'WSH_DELIVERY_LEGS'
608 AND WDI.Status <> 'CANCELLED'
609 ORDER BY WDL.Sequence_Number;
610
611 BillLadingRec BillLading%RowType;
612
613 CURSOR OrgAddr ( C_Org_ID NUMBER ) IS
614 SELECT rpad(nvl(Org.Name,' ') , 80 , ' ') || /* Name */
615 rpad(nvl(Loc.Address_Line_1,' ') , 80 , ' ') || /* Address1 */
616 rpad(nvl(Loc.Address_Line_2,' ') , 80 , ' ') || /* Address2 */
617 rpad(nvl(Loc.Address_Line_3,' ') , 80 , ' ') || /* Address3 */
618 rpad(' ' , 80 , ' ') || /* Address4 */
619 rpad(nvl(Loc.Town_Or_City,' ') , 80 , ' ') || /* City */
620 rpad(nvl(Loc.Region_1,' ') , 80 , ' ') || /* County? */
621 rpad(nvl(Loc.Region_2,' ') , 80 , ' ') || /* State */
622 rpad(nvl(Loc.Region_3,' ') , 80 , ' ') || /* Province? */
623 rpad(nvl(Loc.Postal_Code,' ') , 80 , ' ') || /* Postal_Code */
624 rpad(nvl(Loc.Country,' ') , 80 , ' ') /* Country_Code */
625 Address
626 FROM hr_locations Loc
627 , hr_organization_units Org
628 WHERE Loc.Location_ID = Org.Location_ID
629 AND Org.Organization_ID = C_Org_ID;
630
631 CURSOR PartyAddr ( C_Cust_Acct_ID NUMBER ) IS
632 SELECT rpad(nvl(p.Party_Name,' ') , 80 , ' ') || /* Name */
633 rpad(nvl(p.Address1,' ') , 80 , ' ') || /* Address1 */
634 rpad(nvl(p.Address2,' ') , 80 , ' ') || /* Address2 */
635 rpad(nvl(p.Address3,' ') , 80 , ' ') || /* Address3 */
636 rpad(nvl(p.Address4,' ') , 80 , ' ') || /* Address4 */
637 rpad(nvl(p.City,' ') , 80 , ' ') || /* City */
638 rpad(nvl(p.County,' ') , 80 , ' ') || /* County */
639 rpad(nvl(p.State,' ') , 80 , ' ') || /* State */
640 rpad(nvl(p.Province,' ') , 80 , ' ') || /* Province */
641 rpad(nvl(p.Postal_Code,' ') , 80 , ' ') || /* Postal_Code */
642 rpad(nvl(p.Country,' ') , 80 , ' ') /* Country_Code */
643 Address
644 FROM hz_cust_accounts c
645 , hz_parties p
646 WHERE c.cust_account_id = C_Cust_Acct_ID
647 AND p.party_id = c.party_id;
648
649 CURSOR CustSiteAddr ( C_Site_ID NUMBER ) IS
650 SELECT rpad(nvl(Party_Name,' ') , 80 , ' ') || /* Name */
651 rpad(nvl(Address1,' ') , 80 , ' ') || /* Address1 */
652 rpad(nvl(Address2,' ') , 80 , ' ') || /* Address2 */
653 rpad(nvl(Address3,' ') , 80 , ' ') || /* Address3 */
654 rpad(nvl(Address4,' ') , 80 , ' ') || /* Address4 */
655 rpad(nvl(City,' ') , 80 , ' ') || /* City */
656 rpad(nvl(County,' ') , 80 , ' ') || /* County */
657 rpad(nvl(State,' ') , 80 , ' ') || /* State */
658 rpad(nvl(Province,' ') , 80 , ' ') || /* Province */
659 rpad(nvl(Postal_Code,' ') , 80 , ' ') || /* Postal_Code */
660 rpad(nvl(Country,' ') , 80 , ' ') /* Country_Code */
661 Address
662 , Location_ID
663 , Site_Use_Code
664 FROM oke_cust_site_uses_v
665 WHERE ID1 = C_Site_ID;
666
667 ContractorAddrRec OrgAddr%RowType;
668 AdminByAddrRec CustSiteAddr%RowType;
669 ShipFrmAddrRec OrgAddr%RowType;
670 ShipToAddrRec CustSiteAddr%RowType;
671 BillToAddrRec CustSiteAddr%RowType;
672 MarkForAddrRec CustSiteAddr%RowType;
673
674 CURSOR PartySite
675 ( C_Deliverable_ID NUMBER
676 , C_Role_Code VARCHAR2 ) IS
677 SELECT pr.jtot_object1_code Object_Code
678 , pr.object1_id1 ID1
679 , pr.code
680 , pr.facility
681 FROM okc_k_party_roles_b pr
682 , oke_k_deliverables_b kd
683 , ( select cle_id , cle_id_ascendant , level_sequence
684 from okc_ancestrys
685 union all
686 select id , id , 99999 from okc_k_lines_b ) a
687 WHERE kd.deliverable_id = C_Deliverable_ID
688 AND pr.rle_code = C_Role_Code
689 AND pr.dnz_chr_id = kd.k_header_id
690 AND a.cle_id = kd.k_line_id
691 AND ( ( pr.cle_id IS NULL AND a.cle_id = a.cle_id_ascendant )
692 OR pr.cle_id = a.cle_id_ascendant )
693 ORDER BY DECODE(pr.cle_id , null , 0 , a.level_sequence) DESC;
694
695 ContractorRec PartySite%RowType;
696 AdminByRec PartySite%RowType;
697 BillToRec PartySite%RowType;
698 MarkForRec PartySite%RowType;
699
700 CURSOR CageCode
701 ( C_Deliverable_ID NUMBER
702 , C_Party_ID VARCHAR2
703 , C_Role_Code VARCHAR2 ) IS
704 SELECT pr.code
705 FROM okc_k_party_roles_b pr
706 , oke_k_deliverables_b kd
707 , ( select cle_id , cle_id_ascendant , level_sequence
708 from okc_ancestrys
709 union all
710 select id , id , 99999 from okc_k_lines_b ) a
711 WHERE kd.deliverable_id = C_Deliverable_ID
712 AND pr.rle_code = C_Role_Code
713 AND pr.dnz_chr_id = kd.k_header_id
714 AND a.cle_id = kd.k_line_id
715 AND ( pr.cle_id IS NULL AND a.cle_id = a.cle_id_ascendant )
716 AND pr.object1_id1 = C_Party_ID
717 ORDER BY DECODE(pr.cle_id , null , 0 , a.level_sequence) DESC;
718
719 ContractorCode CageCode%RowType;
720 AdminByCode CageCode%RowType;
721 BillToCode CageCode%RowType;
722 MarkForCode CageCode%RowType;
723
724
725 CURSOR ShipSite
726 ( C_Deliverable_ID NUMBER
727 , C_Role_Code VARCHAR2
728 , C_ID1 NUMBER ) IS
729 SELECT pr.jtot_object1_code Object_Code
730 , pr.object1_id1 ID1
731 , pr.facility
732 FROM okc_k_party_roles_b pr
733 , oke_k_deliverables_b kd
734 , ( select cle_id , cle_id_ascendant , level_sequence
735 from okc_ancestrys
736 union all
737 select id , id , 99999 from okc_k_lines_b ) a
738 WHERE kd.deliverable_id = C_Deliverable_ID
739 AND pr.rle_code = C_Role_Code
740 AND pr.object1_id1 = C_ID1
741 AND pr.dnz_chr_id = kd.k_header_id
742 AND a.cle_id = kd.k_line_id
743 AND ( ( pr.cle_id IS NULL AND a.cle_id = a.cle_id_ascendant )
744 OR pr.cle_id = a.cle_id_ascendant )
745 ORDER BY DECODE(pr.cle_id , null , 0 , a.level_sequence) DESC;
746
747 ShipFromRec ShipSite%RowType;
748 ShipToRec ShipSite%RowType;
749
750
751 CURSOR ShipCode
752 ( C_Deliverable_ID NUMBER
753 , C_Role_Code VARCHAR2
754 , C_ID1 NUMBER ) IS
755 SELECT pr.code
756 FROM okc_k_party_roles_b pr
757 , oke_k_deliverables_b kd
758 , ( select cle_id , cle_id_ascendant , level_sequence
759 from okc_ancestrys
760 union all
761 select id , id , 99999 from okc_k_lines_b ) a
762 WHERE kd.deliverable_id = C_Deliverable_ID
763 AND pr.rle_code = C_Role_Code
764 AND pr.object1_id1 = C_ID1
765 AND pr.dnz_chr_id = kd.k_header_id
766 AND a.cle_id = kd.k_line_id
767 AND pr.code is not null
768 AND ( ( pr.cle_id IS NULL AND a.cle_id = a.cle_id_ascendant )
769 OR pr.cle_id = a.cle_id_ascendant )
770 ORDER BY DECODE(pr.cle_id , null , 0 , a.level_sequence) DESC;
771
772 ShipFromCode ShipCode%RowType;
773 ShipToCode ShipCode%RowType;
774
775 CURSOR TermValue
776 ( C_Deliverable_ID NUMBER
777 , C_Term_Code VARCHAR2 ) IS
778 SELECT kt.term_value_pk1 Code
779 , OKE_UTILS.Get_Term_Values
780 ( kt.term_code , kt.term_value_pk1
781 , kt.term_value_pk2 , 'MEANING' ) Name
782 FROM oke_k_terms kt
783 , oke_k_deliverables_b kd
784 , ( select cle_id , cle_id_ascendant , level_sequence
785 from okc_ancestrys
786 union all
787 select id , id , 99999 from okc_k_lines_b ) a
788 WHERE kd.deliverable_id = C_Deliverable_ID
789 AND kt.term_code = C_Term_Code
790 AND kt.k_header_id = kd.k_header_id
791 AND a.cle_id = kd.k_line_id
792 AND ( ( kt.k_line_id IS NULL AND a.cle_id = a.cle_id_ascendant )
793 OR kt.k_line_id = a.cle_id_ascendant )
794 ORDER BY DECODE(kt.k_line_id , null , 0 , a.level_sequence) DESC;
795
796 AccptMethodRec TermValue%RowType;
797 AccptPointRec TermValue%RowType;
798 InspPointRec TermValue%RowType;
799 ShipMethodRec TermValue%RowType;
800 DiscTermsRec TermValue%RowType;
801
802 CURSOR ShipFrom
803 ( C_K_Header_ID NUMBER
804 , C_Ship_From_Org NUMBER ) IS
805 SELECT Code
806 FROM okc_k_party_roles_b
807 WHERE rle_code = 'SHIP_FROM'
808 AND jtot_object1_code = 'OKX_INVENTORY'
809 AND object1_id1 = C_Ship_From_Org
810 AND dnz_chr_id = C_K_Header_ID;
811
812 --ShipFromCode VARCHAR2(30);
813
814 CURSOR ShipTo
815 ( C_Ship_To_Loc NUMBER ) IS
816 SELECT ID1
817 FROM oke_cust_site_uses_v
818 WHERE location_id = C_Ship_To_Loc
819 AND site_use_code = 'SHIP_TO';
820
821 ShipToLoc ShipTo%RowType;
822
823 hdr_rec Hdr_Rec_Type;
824 line_tbl Line_Tbl_Type;
825 FormHdrID NUMBER;
826 i NUMBER;
827
828 BEGIN
829
830 fnd_file.put_line(fnd_file.log , '... Begin DD250 generation');
831
832 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
833
834 --
835 -- First of all, fetch delivery level information
836 --
837 OPEN BillLading;
838 FETCH BillLading INTO BillLadingRec;
839 CLOSE BillLading;
840
841 fnd_file.put_line(fnd_file.log , '... Fetching Bill of Lading information');
842
843 -- FOR FrmHdrRec IN FrmHdr LOOP
844
845 OPEN FrmHdr;
846 FETCH FrmHdr INTO FrmHdrRec;
847
848 IF FrmHdr%NOTFOUND THEN
849 fnd_file.put_line(fnd_file.log , 'No data found for DD250.');
850 CLOSE FrmHdr;
851
852 ELSE
853
854 CLOSE FrmHdr;
855
856 -- Lock the record, to preventing duplicate Shipment Number
857 update oke_k_print_forms
858 set last_update_date = sysdate
859 WHERE k_header_id = FrmHdrRec.K_Header_ID
860 AND print_form_code = 'DD250';
861
862 --
863 -- Check for previously created DD250 for the same delivery
864 --
865 fnd_file.put_line(fnd_file.log , '... Check for previously created DD250');
866
867 hdr_rec.Shipment_Number := NULL;
868 OPEN ShipmentNum( FrmHdrRec.K_Header_ID
869 , P_Delivery_ID
870 , FrmHdrRec.Inv_Org_ID );
871 FETCH ShipmentNum INTO hdr_rec.Shipment_Number;
872 CLOSE ShipmentNum;
873
874 --
875 -- If existing copy not found, assign new Shipment Number
876 --
877 IF ( hdr_rec.Shipment_Number IS NULL ) THEN
878 hdr_rec.Shipment_Number := Shipment_Number
879 ( FrmHdrRec.K_Header_ID
880 , FrmHdrRec.Inv_Org_ID
881 , P_Delivery_ID
882 , FrmHdrRec.Ship_From_Loc );
883 ELSE
884 fnd_file.put_line(fnd_file.log , '... Shipment Number ' ||
885 hdr_rec.Shipment_Number || ' located');
886 END IF;
887
888 --
889 -- Fetch Contract Parties Information
890 --
891
892 --
893 -- First, fetch customer information
894 --
895 OPEN PartySite( FrmHdrRec.Deliverable_ID , 'ADMIN_BY' );
896 FETCH PartySite INTO AdminByRec;
897 CLOSE PartySite;
898
899 IF (AdminByRec.Code IS NULL AND AdminByRec.ID1 IS NOT NULL) THEN
900 OPEN CageCode( FrmHdrRec.Deliverable_ID , AdminByRec.ID1, 'ADMIN_BY');
901 FETCH CageCode INTO AdminByCode;
902 CLOSE CageCode;
903 AdminByRec.Code := AdminByCode.Code;
904 END IF;
905
906 fnd_file.put_line(fnd_file.log , '... Fetching Admin By information');
907 fnd_file.put_line(fnd_file.log , '==> ' || AdminByRec.Object_Code || ' / ' ||
908 AdminByRec.Code || ' / ' ||
909 AdminByRec.ID1);
910
911 IF ( AdminByRec.Object_Code = 'OKE_CUST_KADMIN' ) THEN
912 OPEN CustSiteAddr( AdminByRec.ID1 );
913 FETCH CustSiteAddr INTO AdminByAddrRec;
914 CLOSE CustSiteAddr;
915 fnd_file.put_line(fnd_file.log , '==> ' || rtrim(substr(AdminByAddrRec.Address , 1 , 80)));
916 END IF;
917
918 --
919 -- Fetch Contractor Information
920 --
921 OPEN PartySite( FrmHdrRec.Deliverable_ID , 'CONTRACTOR' );
922 FETCH PartySite INTO ContractorRec;
923 CLOSE PartySite;
924
925 IF (ContractorRec.Code IS NULL AND ContractorRec.ID1 IS NOT NULL) THEN
926 OPEN CageCode( FrmHdrRec.Deliverable_ID , ContractorRec.ID1, 'CONTRACTOR');
927 FETCH CageCode INTO ContractorCode;
928 CLOSE CageCode;
929 ContractorRec.Code := ContractorCode.Code;
930 END IF;
931
932 fnd_file.put_line(fnd_file.log , '... Fetching contractor information');
933 fnd_file.put_line(fnd_file.log , '==> ' || ContractorRec.Object_Code || ' / ' ||
934 ContractorRec.Code || ' / ' ||
935 ContractorRec.ID1);
936
937 IF ( ContractorRec.Object_Code = 'OKX_OPERUNIT' OR ContractorRec.Object_Code = 'OKX_INVENTORY' ) THEN
938 OPEN OrgAddr( ContractorRec.ID1 );
939 FETCH OrgAddr INTO ContractorAddrRec;
940 CLOSE OrgAddr;
941 fnd_file.put_line(fnd_file.log , '==> ' || rtrim(substr(ContractorAddrRec.Address , 1 , 80)));
942 END IF;
943
944 --
945 -- Ship From Organization
946 --
947 OPEN ShipSite( FrmHdrRec.Deliverable_ID , 'SHIP_FROM', FrmHdrRec.Inv_Org_ID );
948 FETCH ShipSite INTO ShipFromRec;
949 CLOSE ShipSite;
950
951 OPEN ShipCode( FrmHdrRec.Deliverable_ID , 'SHIP_FROM', FrmHdrRec.Inv_Org_ID );
952 FETCH ShipCode INTO ShipFromCode;
953 CLOSE ShipCode;
954
955 fnd_file.put_line(fnd_file.log , '... Fetching ship from information');
956 fnd_file.put_line(fnd_file.log , '==> ' || ShipFromRec.Object_Code || ' / ' ||
957 ShipFromCode.Code || ' / ' ||
958 ShipFromRec.ID1 || ' / ' || FrmHdrRec.Inv_Org_ID );
959
960 OPEN OrgAddr( FrmHdrRec.Inv_Org_ID );
961 FETCH OrgAddr INTO ShipFrmAddrRec;
962 CLOSE OrgAddr;
963 fnd_file.put_line(fnd_file.log , '==> ' || rtrim(substr(ShipFrmAddrRec.Address , 1 , 80)));
964
965 --
966 -- Ship To Location
967 --
968 OPEN ShipTo ( FrmHdrRec.Ship_To_Loc );
969 FETCH ShipTo INTO ShipToLoc;
970 CLOSE ShipTo;
971
972 OPEN ShipSite( FrmHdrRec.Deliverable_ID , 'SHIP_TO', ShipToLoc.ID1 );
973 FETCH ShipSite INTO ShipToRec;
974 CLOSE ShipSite;
975
976 OPEN ShipCode( FrmHdrRec.Deliverable_ID , 'SHIP_TO', ShipToLoc.ID1 );
977 FETCH ShipCode INTO ShipToCode;
978 CLOSE ShipCode;
979
980 fnd_file.put_line(fnd_file.log , '... Fetching ship to information');
981 fnd_file.put_line(fnd_file.log , '==> ' || ShipToRec.Object_Code || ' / ' ||
982 ShipToCode.Code || ' / ' ||
983 ShipToRec.ID1);
984
985 --
986 -- We use the Ship To in the shipping request to derive the address
987 -- We match the Ship To to Ship To role to get the DoDAAD code
988 --
989 IF ( ShipToLoc.ID1 IS NOT NULL ) THEN
990 OPEN CustSiteAddr( ShipToLoc.ID1 );
991 FETCH CustSiteAddr INTO ShipToAddrRec;
992 CLOSE CustSiteAddr;
993 fnd_file.put_line(fnd_file.log , '==> ' || rtrim(substr(ShipToAddrRec.Address , 1 , 80)));
994 END IF;
995
996 --
997 -- Mark For Location
998 --
999 OPEN PartySite( FrmHdrRec.Deliverable_ID , 'MARK_FOR' );
1000 FETCH PartySite INTO MarkForRec;
1001 CLOSE PartySite;
1002
1003 IF (MarkForRec.Code IS NULL AND MarkForRec.ID1 IS NOT NULL) THEN
1004 OPEN CageCode( FrmHdrRec.Deliverable_ID , MarkForRec.ID1, 'MARK_FOR');
1005 FETCH CageCode INTO MarkForCode;
1006 CLOSE CageCode;
1007 MarkForRec.Code := MarkForCode.Code;
1008 END IF;
1009
1010 fnd_file.put_line(fnd_file.log , '... Fetching mark for information');
1011 fnd_file.put_line(fnd_file.log , '==> ' || MarkForRec.Object_Code || ' / ' ||
1012 MarkForRec.Code || ' / ' ||
1013 MarkForRec.ID1);
1014
1015 IF ( MarkForRec.Object_Code = 'OKE_MARKFOR' ) THEN
1016 OPEN CustSiteAddr( MarkForRec.ID1 );
1017 FETCH CustSiteAddr INTO MarkForAddrRec;
1018 CLOSE CustSiteAddr;
1019 fnd_file.put_line(fnd_file.log , '==> ' || rtrim(substr(MarkForAddrRec.Address , 1 , 80)));
1020 END IF;
1021
1022 --
1023 -- Bill To Location
1024 --
1025 OPEN PartySite( FrmHdrRec.Deliverable_ID , 'BILL_TO' );
1026 FETCH PartySite INTO BillToRec;
1027 CLOSE PartySite;
1028
1029 IF (BillToRec.Code IS NULL AND BillToRec.ID1 IS NOT NULL) THEN
1030 OPEN CageCode( FrmHdrRec.Deliverable_ID , BillToRec.ID1, 'BILL_TO');
1031 FETCH CageCode INTO BillToCode;
1032 CLOSE CageCode;
1033 BillToRec.Code := BillToCode.Code;
1034 END IF;
1035
1036 fnd_file.put_line(fnd_file.log , '... Fetching bill to information');
1037 fnd_file.put_line(fnd_file.log , '==> ' || BillToRec.Object_Code || ' / ' ||
1038 BillToRec.Code || ' / ' ||
1039 BillToRec.ID1);
1040
1041 IF ( BillToRec.Object_Code = 'OKE_BILLTO' ) THEN
1042 OPEN CustSiteAddr( BillToRec.ID1 );
1043 FETCH CustSiteAddr INTO BillToAddrRec;
1044 CLOSE CustSiteAddr;
1045 fnd_file.put_line(fnd_file.log , '==> ' || rtrim(substr(BillToAddrRec.Address , 1 , 80)));
1046 END IF;
1047
1048 --
1049 -- Fetching other Terms and Conditions
1050 --
1051 fnd_file.put_line(fnd_file.log , '... Fetching various terms');
1052
1053 OPEN TermValue( FrmHdrRec.Deliverable_ID , 'ACCEPTANCE_METHOD' );
1054 FETCH TermValue INTO AccptMethodRec;
1055 CLOSE TermValue;
1056
1057 fnd_file.put_line(fnd_file.log , 'Acceptance Method = ' || AccptMethodRec.Code);
1058
1059 OPEN TermValue( FrmHdrRec.Deliverable_ID , 'ACCEPTANCE_POINT' );
1060 FETCH TermValue INTO AccptPointRec;
1061 CLOSE TermValue;
1062
1063 fnd_file.put_line(fnd_file.log , 'Acceptance Point = ' || AccptPointRec.Code);
1064
1065 OPEN TermValue( FrmHdrRec.Deliverable_ID , 'INSPECTION_POINT' );
1066 FETCH TermValue INTO InspPointRec;
1067 CLOSE TermValue;
1068
1069 fnd_file.put_line(fnd_file.log , 'Inspection Point = ' || InspPointRec.Code);
1070
1071 OPEN TermValue( FrmHdrRec.Deliverable_ID , 'OB_SHIP_METHOD' );
1072 FETCH TermValue INTO ShipMethodRec;
1073 CLOSE TermValue;
1074
1075 fnd_file.put_line(fnd_file.log , 'Ship Method = ' || ShipMethodRec.Code);
1076
1077 OPEN TermValue( FrmHdrRec.Deliverable_ID , 'RA_PAYMENT_TERMS' );
1078 FETCH TermValue INTO DiscTermsRec;
1079 CLOSE TermValue;
1080
1081 fnd_file.put_line(fnd_file.log , 'Payment Terms = ' || DiscTermsRec.Name);
1082
1083 --
1084 -- Preparing Form Header Record
1085 --
1086 hdr_rec.Contract_Header_ID := FrmHdrRec.K_Header_ID;
1087 hdr_rec.Reference1 := P_Delivery_ID;
1088 hdr_rec.Reference2 := FrmHdrRec.Inv_Org_ID;
1089 hdr_rec.Reference3 := NULL /* FrmHdrRec.Deliverable_ID */;
1090 hdr_rec.Reference4 := NULL;
1091 hdr_rec.Reference5 := NULL;
1092 hdr_rec.Shipment_Date := sysdate;
1093 hdr_rec.Bill_Of_Lading := BillLadingRec.BOL_Number;
1094 hdr_rec.Ship_Method := ShipMethodRec.Code;
1095 hdr_rec.Discount_Terms := DiscTermsRec.Name;
1096 hdr_rec.Acceptance_Method := AccptMethodRec.Code;
1097 hdr_rec.Acceptance_Point := AccptPointRec.Code;
1098 hdr_rec.Inspection_Point := InspPointRec.Code;
1099 hdr_rec.Customer := AdminByAddrRec.Address;
1100 hdr_rec.Customer_Code := AdminByRec.Code;
1101 hdr_rec.Contractor := ContractorAddrRec.Address;
1102 hdr_rec.Contractor_Code := ContractorRec.Code;
1103 hdr_rec.Ship_From := ShipFrmAddrRec.Address;
1104 hdr_rec.Ship_From_Code := ShipFromCode.Code;
1105 hdr_rec.FOB := FrmHdrRec.FOB_Code;
1106 hdr_rec.Paid_By := BillToAddrRec.Address;
1107 hdr_rec.Paid_By_Code := BillToRec.Code;
1108 hdr_rec.Ship_To := ShipToAddrRec.Address;
1109 --
1110 -- Only use Code from Party information if it matches shipping information
1111 --
1112 -- IF ( ShipToRec.ID1 = ShipToLoc.ID1 AND
1113 -- ShipToRec.Object_Code = 'OKE_SHIPTO' ) THEN
1114 hdr_rec.Ship_To_Code := ShipToCode.Code;
1115 -- END IF;
1116 hdr_rec.Mark_For := MarkForAddrRec.Address;
1117 hdr_rec.Mark_For_Code := MarkForRec.Code;
1118 hdr_rec.Gross_Weight := FrmHdrRec.Gross_Weight;
1119 hdr_rec.Net_Weight := FrmHdrRec.Net_Weight;
1120 hdr_rec.Weight_UOM_Code := FrmHdrRec.Weight_UOM_Code;
1121 hdr_rec.Volume := FrmHdrRec.Volume;
1122 hdr_rec.Volume_UOM_Code := FrmHdrRec.Volume_UOM_Code;
1123
1124 i := 0;
1125
1126 fnd_file.put_line(fnd_file.log , fnd_global.newline || '... Processing Line Information');
1127
1128 FOR FrmLineRec IN FrmLine ( P_Delivery_ID
1129 , FrmHdrRec.Inv_Org_ID
1130 , FrmHdrRec.K_Header_ID
1131 , FrmHdrRec.Ship_To_Loc ) LOOP
1132
1133 i := i + 1;
1134
1135 OPEN LineInfo( FrmLineRec.Deliverable_ID );
1136 FETCH LineInfo INTO LineInfoRec;
1137 CLOSE LineInfo;
1138
1139 fnd_file.put_line(fnd_file.log , 'CLIN = ' || LineInfoRec.Line_Number);
1140 fnd_file.put_line(fnd_file.log , 'Desc = ' || FrmLineRec.Item_Description);
1141 fnd_file.put_line(fnd_file.log , 'UOM = ' || LineInfoRec.UOM_Code);
1142 fnd_file.put_line(fnd_file.log , 'Qty = ' || FrmLineRec.Shipped_Qty);
1143 fnd_file.put_line(fnd_file.log , 'Price = ' || LineInfoRec.Unit_Price);
1144
1145 line_tbl(i).Line_Number := LineInfoRec.Line_Number;
1146 line_tbl(i).Item_Number := LineInfoRec.Item_Number;
1147 line_tbl(i).Natl_Stock_Number := LineInfoRec.Natl_Stock_Number;
1148 line_tbl(i).Item_Description := LineInfoRec.Item_Description;
1149 line_tbl(i).Line_Description := LineInfoRec.Line_Description;
1150 line_tbl(i).Line_Comments := LineInfoRec.Line_Comments;
1151 line_tbl(i).UOM := LineInfoRec.UOM_Code;
1152 line_tbl(i).Shipped_Quantity := FrmLineRec.Shipped_Qty;
1153 line_tbl(i).Unit_Price := LineInfoRec.Unit_Price;
1154 line_tbl(i).Amount := LineInfoRec.Unit_Price *
1155 FrmLineRec.Shipped_Qty;
1156 line_tbl(i).Reference1 := FrmLineRec.Deliverable_ID;
1157 line_tbl(i).Reference2 := NULL;
1158 line_tbl(i).Reference3 := NULL;
1159 line_tbl(i).Reference4 := NULL;
1160 line_tbl(i).Reference5 := NULL;
1161
1162 END LOOP;
1163
1164 fnd_file.put_line(fnd_file.log , '... Inserting into OKE_K_FORM_HEADERS');
1165
1166 Create_DD250
1167 ( P_Commit => FND_API.G_FALSE
1168 , P_Hdr_Rec => hdr_rec
1169 , P_Line_Tbl => line_tbl
1170 , X_Msg_Count => X_Msg_Count
1171 , X_Msg_Data => X_Msg_Data
1172 , X_Return_Status => X_Return_Status
1173 );
1174
1175 -- END LOOP;
1176
1177 fnd_file.put_line(fnd_file.log , 'DD250 created.');
1178
1179 END IF;
1180
1181 END Create_DD250_From_Delivery;
1182
1183
1184 --
1185 -- Name : Create_DD250_Conc
1186 -- Pre-reqs : run as concurrent request
1187 -- Function : This procedure creates a copy of DD250 for a delivery
1188 --
1189 --
1190 -- Parameters :
1191 -- IN : P_DELIVERY_ID NUMBER
1192 -- OUT : ERRBUF VARCHAR2
1193 -- RETCODE NUMBER
1194 --
1195 -- Returns : None
1196 --
1197
1198 PROCEDURE Create_DD250_Conc
1199 ( ErrBuf OUT NOCOPY VARCHAR2
1200 , RetCode OUT NOCOPY NUMBER
1201 , P_Delivery_ID IN NUMBER
1202 , P_Unused01 IN VARCHAR2
1203 , P_Unused02 IN VARCHAR2
1204 , P_Unused03 IN VARCHAR2
1205 , P_Unused04 IN VARCHAR2
1206 , P_Unused05 IN VARCHAR2
1207 , P_Unused06 IN VARCHAR2
1208 , P_Unused07 IN VARCHAR2
1209 , P_Unused08 IN VARCHAR2
1210 , P_Unused09 IN VARCHAR2
1211 , P_Unused10 IN VARCHAR2
1212 , P_Unused11 IN VARCHAR2
1213 , P_Unused12 IN VARCHAR2
1214 , P_Unused13 IN VARCHAR2
1215 , P_Unused14 IN VARCHAR2
1216 , P_Unused15 IN VARCHAR2
1217 , P_Unused16 IN VARCHAR2
1218 , P_Unused17 IN VARCHAR2
1219 , P_Unused18 IN VARCHAR2
1220 , P_Unused19 IN VARCHAR2
1221 , P_Unused20 IN VARCHAR2
1222 , P_Unused21 IN VARCHAR2
1223 , P_Unused22 IN VARCHAR2
1224 , P_Unused23 IN VARCHAR2
1225 , P_Unused24 IN VARCHAR2
1226 , P_Unused25 IN VARCHAR2
1227 , P_Unused26 IN VARCHAR2
1228 , P_Unused27 IN VARCHAR2
1229 , P_Unused28 IN VARCHAR2
1230 , P_Unused29 IN VARCHAR2
1231 , P_Unused30 IN VARCHAR2
1232 , P_Unused31 IN VARCHAR2
1233 , P_Unused32 IN VARCHAR2
1234 , P_Unused33 IN VARCHAR2
1235 , P_Unused34 IN VARCHAR2
1236 , P_Unused35 IN VARCHAR2
1237 , P_Unused36 IN VARCHAR2
1238 , P_Unused37 IN VARCHAR2
1239 , P_Unused38 IN VARCHAR2
1240 , P_Unused39 IN VARCHAR2
1241 , P_Unused40 IN VARCHAR2
1242 , P_Unused41 IN VARCHAR2
1243 , P_Unused42 IN VARCHAR2
1244 , P_Unused43 IN VARCHAR2
1245 , P_Unused44 IN VARCHAR2
1246 , P_Unused45 IN VARCHAR2
1247 , P_Unused46 IN VARCHAR2
1248 , P_Unused47 IN VARCHAR2
1249 , P_Unused48 IN VARCHAR2
1250 , P_Unused49 IN VARCHAR2
1251 , P_Unused50 IN VARCHAR2
1252 , P_Unused51 IN VARCHAR2
1253 , P_Unused52 IN VARCHAR2
1254 , P_Unused53 IN VARCHAR2
1255 , P_Unused54 IN VARCHAR2
1256 , P_Unused55 IN VARCHAR2
1257 , P_Unused56 IN VARCHAR2
1258 , P_Unused57 IN VARCHAR2
1259 , P_Unused58 IN VARCHAR2
1260 , P_Unused59 IN VARCHAR2
1261 , P_Unused60 IN VARCHAR2
1262 , P_Unused61 IN VARCHAR2
1263 , P_Unused62 IN VARCHAR2
1264 , P_Unused63 IN VARCHAR2
1265 , P_Unused64 IN VARCHAR2
1266 , P_Unused65 IN VARCHAR2
1267 , P_Unused66 IN VARCHAR2
1268 , P_Unused67 IN VARCHAR2
1269 , P_Unused68 IN VARCHAR2
1270 , P_Unused69 IN VARCHAR2
1271 , P_Unused70 IN VARCHAR2
1272 , P_Unused71 IN VARCHAR2
1273 , P_Unused72 IN VARCHAR2
1274 , P_Unused73 IN VARCHAR2
1275 , P_Unused74 IN VARCHAR2
1276 , P_Unused75 IN VARCHAR2
1277 , P_Unused76 IN VARCHAR2
1278 , P_Unused77 IN VARCHAR2
1279 , P_Unused78 IN VARCHAR2
1280 , P_Unused79 IN VARCHAR2
1281 , P_Unused80 IN VARCHAR2
1282 , P_Unused81 IN VARCHAR2
1283 , P_Unused82 IN VARCHAR2
1284 , P_Unused83 IN VARCHAR2
1285 , P_Unused84 IN VARCHAR2
1286 , P_Unused85 IN VARCHAR2
1287 , P_Unused86 IN VARCHAR2
1288 , P_Unused87 IN VARCHAR2
1289 , P_Unused88 IN VARCHAR2
1290 , P_Unused89 IN VARCHAR2
1291 , P_Unused90 IN VARCHAR2
1292 , P_Unused91 IN VARCHAR2
1293 , P_Unused92 IN VARCHAR2
1294 , P_Unused93 IN VARCHAR2
1295 , P_Unused94 IN VARCHAR2
1296 , P_Unused95 IN VARCHAR2
1297 , P_Unused96 IN VARCHAR2
1298 , P_Unused97 IN VARCHAR2
1299 , P_Unused98 IN VARCHAR2
1300 , P_Unused99 IN VARCHAR2
1301 ) IS
1302
1303 Error_Buf VARCHAR2(2000);
1304 Msg_Count NUMBER;
1305 Msg_Data VARCHAR2(2000);
1306 Return_Status VARCHAR2(1);
1307 i INTEGER;
1308
1309 BEGIN
1310
1311 Create_DD250_From_Delivery( P_Delivery_ID , Msg_Count , Msg_Data , Return_Status);
1312
1313 IF ( Return_Status = FND_API.G_RET_STS_SUCCESS ) THEN
1314 RetCode := 0;
1315 ELSE
1316
1317 RetCode := 2;
1318
1319 for i in 1..Msg_Count loop
1320 Error_Buf := fnd_msg_pub.get( p_msg_index => i
1321 , p_encoded => fnd_api.g_false );
1322 fnd_message.set_name('OKE' , 'OKE_API_ERROR_MULTI');
1323 fnd_message.set_token( 'CURR' , i);
1324 fnd_message.set_token( 'TOTAL' , Msg_Count);
1325 fnd_message.set_token( 'TEXT', Error_Buf );
1326 fnd_file.put_line( fnd_file.log , fnd_message.get );
1327 end loop;
1328
1329 END IF;
1330
1331 EXCEPTION
1332 WHEN OTHERS THEN
1333 RetCode := 2;
1334 Errbuf := sqlerrm;
1335
1336 END Create_DD250_Conc;
1337
1338 END OKE_FORM_DD250;