[Home] [Help]
PACKAGE BODY: APPS.GMP_APS_OUTPUT_PKG
Source
1 PACKAGE BODY gmp_aps_output_pkg as
2 /* $Header: GMPOUTIB.pls 120.4.12000000.3 2007/07/24 12:41:23 kbanddyo ship $ */
3
4 /*=========================================================================
5 | PROCEDURE NAME |
6 | insert_gmp_iterface |
7 | |
8 | TYPE |
9 | public |
10 | |
11 | DESCRIPTION |
12 | The following procedure inserts into gmp_aps_output_tbl table, |
13 | Pull out the Information from wip_job_schedule_interface. |
14 | Input Parameters |
15 | p_group_id - Group Id |
16 | |
17 | Output Parameters |
18 | None |
19 | |
20 | HISTORY Rajesh Patangya on 10/08/99 |
21 | Rajesh Patangya on 11/02/99 Item_id from fm_form_eff |
22 | 11/23/99 - Sridhar added errbuf,retcode to the insert_gmp_interface |
23 | - Procedure |
24 | 02/24/00 - Sridhar added Cancel flag to the insert_gmp_interface |
25 | - Procedure |
26 | - Added insert statement for Cancelled Orders and modified |
27 | - insert statement for Re-Scheduled Orders - Bug# 1210500 |
28 | 14-AUG-01 Sridhar Changed per the discussion in the bug : 1880303 |
29 | Status_type APS feedback program updates this as following |
30 | NULL - For Reschduled_job |
31 | 1 - For New job |
32 | 7 For cancelled Job |
33 | 21-JAN-02 - Sridhar Modified the Insert statement with new GMD Tables |
34 | |
35 | 05-JAN-05 - Sowmya, B4084230. New column Firm_flag also populated in the|
36 | gmp_aps_output_dtl table from WIP_JOB_SCHEDULE_INTERFACE |
37 | 08-21-06 - Rajesh B5454215 Added parameter of value '0' (Batch Type) |
38 | when calling GMPRELAP |
39 | 12-07-07 - Kaushek B6167305 Added new profile option for implementing |
40 | suggestions as 'FPO' whereby default it is set to'BATCH' |
41 ==========================================================================*/
42
43 PROCEDURE insert_gmp_interface( errbuf out NOCOPY varchar2,
44 retcode out NOCOPY number,
45 p_group_id IN NUMBER) IS
46
47 delete_new_flag NUMBER; /* Delete flag for new batch */
48 delete_rsch_flag NUMBER; /* Delete flag for rescheduled batch */
49 delete_cancel_flag NUMBER; /* Delete flag for Cancel batch */
50
51 G_log_text VARCHAR2(1000);
52 X_conc_id NUMBER;
53 l_profile NUMBER; /* Hold Profile value for Implement APS Suggestions */
54 l_cons NUMBER;
55 lv_result BOOLEAN;
56 ERROR_SUBMITTING_REQUEST EXCEPTION;
57 firm_batch_profile NUMBER; /* B5897392 Implement Suggestions as firm */
58 batch_fpo_profile NUMBER; /* B6167305 Profile-Implement Suggestions as BATCH or FPO */
59
60 BEGIN
61
62 /* 1. Select only OPM rows from WIP_INTERFACE TABLE
63 2. Join those rows to GMP_APS_FORM_EFF to get OPM eff id
64 3. We are using group id as process id in sync with WIP tables . (Abhay)
65 4. Processed_ind from sequence. (matt)
66 5. Change alternate_bom_designator to bom_reference_id. (matt)
67 */
68
69 delete_new_flag := 0;
70 delete_rsch_flag := 0;
71 delete_cancel_flag := 0;
72 l_profile := 0;
73 firm_batch_profile := NVL(FND_PROFILE.VALUE('GMP_IMPLEMENT_FIRM_BATCH'),1);
74 batch_fpo_profile := 0; /* B6167305 By default Batch */
75
76 BEGIN -- For New Orders
77
78 /* B2104059 GMP-APS ENHANCEMENT FOR GMD FORMULA SECURITY FUNCTIONALITY */
79 /* Disable the security */
80
81 BEGIN
82 gmd_p_fs_context.set_additional_attr ;
83 END;
84
85 SELECT gmp_process_upd_id_s.nextval INTO l_cons from dual;
86
87 INSERT INTO gmp_aps_output_tbl (
88 PROCESS_ID,
89 INVENTORY_ITEM_ID, -- for R12.0
90 ORGANIZATION_CODE,
91 ORGANIZATION_ID,
92 BATCH_ID,
93 WAREHOUSE_CODE,
94 EFFECTIVITY_ID,
95 PLAN_QUANTITY,
96 PLAN_START_DATE,
97 PLAN_END_DATE,
98 ACTION_TYPE,
99 PROCESSED_IND,
100 HEADER_ID,
101 SCHEDULING_METHOD,
102 FIRM_FLAG , /*B4084230*/
103 LAST_UPDATE_LOGIN,
104 LAST_UPDATED_BY,
105 LAST_UPDATE_DATE,
106 CREATION_DATE,
107 CREATED_BY ,
108 REQUIRED_COMPLETION_DATE , /* B3710615 12.0 */
109 ORDER_PRIORITY /* B3710615 12.0 */
110 )
111 SELECT
112 p_group_id ,
113 grv.inventory_item_id, -- For R12.0
114 w.organization_code, /* B2164593 */
115 w.organization_id,
116 to_number(NULL),
117 gfe.whse_code,
118 grv.recipe_validity_rule_id, /* new GMD Column 1992371 */
119 w.start_quantity,
120 w.first_unit_start_date,
121 w.last_unit_completion_date,
122 w.load_type,
123 l_cons, /* PENDING - B2874323 */
124 w.header_id,
125 DECODE(gfe.routing_id,null,2,w.scheduling_method), /* B3119256 */
126 -- B4664966 implement as firm only if ASCP and Profile says firm
127 DECODE(w.firm_planned_flag,1,firm_batch_profile,w.firm_planned_flag), /*B4084230*/
128 w.last_update_login,
129 w.last_updated_by,
130 w.last_update_date,
131 w.creation_date,
132 w.created_by ,
133 w.due_date , /* B3710615 12.0 */
134 w.priority /* B4039225 for 12.0 */
135 FROM
136 wip_job_schedule_interface w,
137 gmp_form_eff gfe,
138 gmd_recipe_validity_rules grv /* B1992371 */
139 WHERE
140 w.group_id = p_group_id
141 AND w.load_type = 1
142 AND w.process_status = 1 /* Unreleased */
143 AND nvl(w.status_type,0 ) = 1 /* New Batch */
144 AND gfe.aps_fmeff_id = w.bom_reference_id
145 AND gfe.fmeff_id = grv.recipe_validity_rule_id (+) /*1992371 */
146 AND gfe.ORGANIZATION_ID = w.organization_id -- For R12.0
147 UNION ALL /* B2874323 */
148 SELECT
149 p_group_id ,
150 mtl.inventory_item_id, -- for R12.0
151 w.organization_code,
152 w.organization_id,
153 to_number(NULL),
154 to_char(NULL),
155 to_number(NULL), -- BOM reference_id is NULL
156 w.start_quantity,
157 w.first_unit_start_date,
158 w.last_unit_completion_date,
159 w.load_type,
160 l_cons,
161 w.header_id,
162 2, /* Scheduling method is taken as NULL here */
163 -- B4664966 implement as firm only if ASCP and Profile says firm
164 DECODE(w.firm_planned_flag,1,firm_batch_profile,w.firm_planned_flag), /*B4084230*/
165 w.last_update_login,
166 w.last_updated_by,
167 sysdate,
168 sysdate,
169 w.created_by ,
170 w.due_date , /* B3710615 12.0 */
171 w.priority /* B4039225 */
172 FROM
173 wip_job_schedule_interface w,
174 mtl_system_items mtl
175 WHERE
176 w.group_id = p_group_id
177 AND w.load_type = 1
178 AND w.process_status = 1
179 AND nvl(w.status_type,0 ) = 1
180 AND w.bom_reference_id IS NULL
181 AND w.primary_item_id = mtl.inventory_item_id
182 AND w.organization_id = mtl.organization_id ;
183
184 delete_new_flag := 1 ;
185
186 EXCEPTION
187 WHEN no_data_found THEN
188 errbuf := 'No Data Found Sql Error:' ||to_char(sqlcode);
189 retcode := 1; /* Warning */
190 delete_new_flag := 1 ;
191
192 WHEN others THEN
193 errbuf := 'Insert Failed Sql Error:' ||to_char(sqlcode);
194 retcode := 1; /* Warning */
195 NULL ;
196 END;
197 COMMIT;
198
199 /* For Rescheduled Orders
200 Load_type incidcates whether to create new or update a job
201 (also what type of job)
202 Status_type APS feedback program updates this as following
203 NULL - For Reschduled_job
204 1 - For New job
205 7 For cancelled Job
206 Therefore the check for quantity is being removed
207 (earlier 0 quantity indicated cancellation of the job
208 */
209
210 BEGIN
211 INSERT INTO gmp_aps_output_tbl (
212 PROCESS_ID,
213 INVENTORY_ITEM_ID, -- For R12.0
214 ORGANIZATION_CODE,
215 ORGANIZATION_ID,
216 BATCH_ID,
217 WAREHOUSE_CODE,
218 EFFECTIVITY_ID,
219 PLAN_QUANTITY,
220 PLAN_START_DATE,
221 PLAN_END_DATE,
222 ACTION_TYPE,
223 PROCESSED_IND,
224 HEADER_ID,
225 SCHEDULING_METHOD,
226 FIRM_FLAG, /*B4084230*/
227 LAST_UPDATE_LOGIN,
228 LAST_UPDATED_BY,
229 LAST_UPDATE_DATE,
230 CREATION_DATE,
231 CREATED_BY ,
232 REQUIRED_COMPLETION_DATE , /* B3710615 12.0 */
233 ORDER_PRIORITY /* B3710615 12.0 */
234 )
235 SELECT
236 p_group_id ,
237 to_number(NULL), -- For R12.0
238 w.organization_code,
239 w.organization_id,
240 w.wip_entity_id,
241 to_char(NULL) ,
242 to_number(NULL) ,
243 w.start_quantity,
244 w.first_unit_start_date,
245 w.last_unit_completion_date,
246 w.load_type,
247 gmp_process_upd_id_s.nextval, /* PENDING */
248 w.header_id,
249 w.scheduling_method,
250 -- B4664966 implement as firm only if ASCP and Profile says firm
251 DECODE(w.firm_planned_flag,1,firm_batch_profile,w.firm_planned_flag), /*B4084230*/
252 w.last_update_login,
253 w.last_updated_by,
254 w.last_update_date,
255 w.creation_date,
256 w.created_by ,
257 w.due_date , /* B3710615 12.0 */
258 w.priority /* B4039225 */
259 FROM
260 wip_job_schedule_interface w,
261 gme_batch_header gbh /* 1992371 */
262 WHERE
263 w.group_id = p_group_id
264 AND w.load_type = 3 /* Update Discrete Job */
265 AND nvl(w.status_type,0 ) <> 7 /* Rescheduled Batch */
266 AND ((w.net_quantity is NULL) or (w.net_quantity <> 0 ))
267 AND w.process_status = 1
268 AND w.wip_entity_id = gbh.batch_id
269 AND w.organization_id = gbh.organization_id ; -- For 12.0
270
271 delete_rsch_flag := 1 ;
272 EXCEPTION
273 WHEN no_data_found THEN
274 errbuf := ' No Data Found Sql Error:' ||to_char(sqlcode);
275 retcode := 1; /* Warning */
276 delete_rsch_flag := -1 ;
277
278 WHEN others THEN
279 errbuf := 'Insert failed Sql Error:' ||to_char(sqlcode);
280 retcode := 1; /* Warning */
281 NULL ;
282 END;
283 COMMIT;
284
285 BEGIN -- For Cancelled Orders
286 INSERT INTO gmp_aps_output_tbl (
287 PROCESS_ID,
288 INVENTORY_ITEM_ID, -- For R12.0
289 ORGANIZATION_CODE,
290 ORGANIZATION_ID,
291 BATCH_ID,
292 WAREHOUSE_CODE,
293 EFFECTIVITY_ID,
294 PLAN_QUANTITY,
295 PLAN_START_DATE,
296 PLAN_END_DATE,
297 ACTION_TYPE,
298 PROCESSED_IND,
299 HEADER_ID,
300 SCHEDULING_METHOD,
301 FIRM_FLAG, /*B4084230*/
302 LAST_UPDATE_LOGIN,
303 LAST_UPDATED_BY,
304 LAST_UPDATE_DATE,
305 CREATION_DATE,
306 CREATED_BY ,
307 REQUIRED_COMPLETION_DATE , /* B3710615 12.0 */
308 ORDER_PRIORITY /* B3710615 12.0 */
309 )
310 SELECT
311 p_group_id ,
312 to_number(NULL), -- For R12.0
313 w.organization_code,
314 w.organization_id,
315 w.wip_entity_id,
316 to_char(NULL) ,
317 to_number(NULL) ,
318 w.start_quantity,
319 w.first_unit_start_date,
320 w.last_unit_completion_date,
321 -1, /* Inserting -1 into action_type to identify Cancellation */
322 gmp_process_upd_id_s.nextval, /* PENDING */
323 w.header_id,
324 w.scheduling_method,
325 -- B4664966 implement as firm only if ASCP and Profile says firm
326 DECODE(w.firm_planned_flag,1,firm_batch_profile,w.firm_planned_flag), /*B4084230*/
327 w.last_update_login,
328 w.last_updated_by,
329 w.last_update_date,
330 w.creation_date,
331 w.created_by ,
332 w.due_date , /* B3710615 12.0 */
333 w.priority /* B4039225 */
334 FROM
335 wip_job_schedule_interface w,
336 gme_batch_header gbh /* 1992371 */
337 WHERE
338 w.group_id = p_group_id
339 AND w.load_type = 3 /* Update Discrete job */
340 AND nvl(w.status_type,0 ) = 7 /* Cancelled Batch */
341 AND w.process_status = 1
342 AND w.wip_entity_id = gbh.batch_id
343 AND w.organization_id = gbh.organization_id ; -- For 12.0
344
345 delete_cancel_flag := 1 ;
346
347
348 EXCEPTION
349 WHEN no_data_found THEN
350 errbuf := ' No Data Found Sql Error:' ||to_char(sqlcode);
351 retcode := 1; /* Warning */
352 delete_cancel_flag := 1 ;
353
354 WHEN others THEN
355 errbuf := 'Insert failed Sql Error:' ||to_char(sqlcode);
356 retcode := 1; /* Warning */
357 NULL ;
358 END;
359 COMMIT;
360
361 -- Available details in wip
362 BEGIN
363 INSERT INTO gmp_aps_output_dtl (
364 GROUP_ID ,
365 WIP_ENTITY_ID ,
366 ORGANIZATION_ID ,
367 OPERATION_SEQ_NUM ,
368 RESOURCE_SEQ_NUM ,
369 RESOURCE_ID_OLD ,
370 RESOURCE_ID_NEW ,
371 USAGE_RATE_OR_AMOUNT ,
372 SCHEDULED_FLAG ,
373 ASSIGNED_UNITS ,
374 UOM_CODE ,
375 START_DATE ,
376 COMPLETION_DATE ,
377 INVENTORY_ITEM_ID_OLD ,
378 INVENTORY_ITEM_ID_NEW ,
379 QUANTITY_PER_ASSEMBLY ,
380 WIP_SUPPLY_TYPE ,
381 DATE_REQUIRED ,
382 REQUIRED_QUANTITY ,
383 QUANTITY_ISSUED ,
384 LOAD_TYPE ,
385 SUBSTITUTION_TYPE ,
386 LAST_UPDATE_DATE ,
387 LAST_UPDATED_BY ,
388 CREATION_DATE ,
389 CREATED_BY ,
390 LAST_UPDATE_LOGIN ,
391 PARENT_HEADER_ID ,
392 FIRST_UNIT_START_DATE ,
393 LAST_UNIT_COMPLETION_DATE ,
394 MINIMUM_TRANSFER_QUANTITY ,
395 ATTRIBUTE_CATEGORY ,
396 ATTRIBUTE1 ,
397 ATTRIBUTE2 ,
398 ATTRIBUTE3 ,
399 ATTRIBUTE4 ,
400 ATTRIBUTE5 ,
401 ATTRIBUTE6 ,
402 ATTRIBUTE7 ,
403 ATTRIBUTE8 ,
404 ATTRIBUTE9 ,
405 ATTRIBUTE10 ,
406 SCHEDULE_SEQ_NUM ,
407 SUBSTITUTE_GROUP_NUM ,
408 REPLACEMENT_GROUP_NUM ,
409 SETUP_ID , -- B3710615 12.0
410 GROUP_SEQUENCE_ID , -- B3710615 12.0
411 GROUP_SEQUENCE_NUMBER , -- B3710615 12.0
412 CHARGE_NUMBER , -- B3710615 12.0
413 RESOURCE_INSTANCE_NUMBER , -- B3710615 12.0
414 FIRM_FLAG , -- B3710615 12.0
415 PARENT_SEQ_NUM , -- B3710615 12.0
416 RESOURCE_INSTANCE_ID -- B3710615 12.0
417 )
418 SELECT
419 wdi.GROUP_ID ,
420 wdi.WIP_ENTITY_ID ,
421 wdi.ORGANIZATION_ID ,
422 wdi.OPERATION_SEQ_NUM ,
423 wdi.RESOURCE_SEQ_NUM ,
424 wdi.RESOURCE_ID_OLD ,
425 wdi.RESOURCE_ID_NEW ,
426 wdi.USAGE_RATE_OR_AMOUNT ,
427 wdi.SCHEDULED_FLAG ,
428 wdi.ASSIGNED_UNITS ,
429 wdi.UOM_CODE ,
430 wdi.START_DATE ,
431 wdi.COMPLETION_DATE ,
432 wdi.INVENTORY_ITEM_ID_OLD ,
433 wdi.INVENTORY_ITEM_ID_NEW ,
434 wdi.QUANTITY_PER_ASSEMBLY ,
435 wdi.WIP_SUPPLY_TYPE ,
436 wdi.DATE_REQUIRED ,
437 wdi.REQUIRED_QUANTITY ,
438 wdi.QUANTITY_ISSUED ,
439 wdi.LOAD_TYPE ,
440 wdi.SUBSTITUTION_TYPE ,
441 wdi.LAST_UPDATE_DATE ,
442 wdi.LAST_UPDATED_BY ,
443 wdi.CREATION_DATE ,
444 wdi.CREATED_BY ,
445 wdi.LAST_UPDATE_LOGIN ,
446 wdi.PARENT_HEADER_ID ,
447 wdi.FIRST_UNIT_START_DATE ,
448 wdi.LAST_UNIT_COMPLETION_DATE ,
449 wdi.MINIMUM_TRANSFER_QUANTITY ,
450 wdi.ATTRIBUTE_CATEGORY ,
451 wdi.ATTRIBUTE1 ,
452 wdi.ATTRIBUTE2 ,
453 wdi.ATTRIBUTE3 ,
454 wdi.ATTRIBUTE4 ,
455 wdi.ATTRIBUTE5 ,
456 wdi.ATTRIBUTE6 ,
457 wdi.ATTRIBUTE7 ,
458 wdi.ATTRIBUTE8 ,
459 wdi.ATTRIBUTE9 ,
460 wdi.ATTRIBUTE10 ,
461 wdi.SCHEDULE_SEQ_NUM ,
462 wdi.SUBSTITUTE_GROUP_NUM ,
463 wdi.REPLACEMENT_GROUP_NUM ,
464 -- B3710615 12.0
465 wdi.SETUP_ID ,
466 wdi.GROUP_SEQUENCE_ID ,
467 wdi.GROUP_SEQUENCE_NUMBER ,
468 wdi.CHARGE_NUMBER ,
469 gri.INSTANCE_NUMBER ,
470 wdi.FIRM_FLAG ,
471 wdi.PARENT_SEQ_NUM ,
472 wdi.RESOURCE_INSTANCE_ID
473 FROM WIP_JOB_DTLS_INTERFACE wdi ,
474 GMP_RESOURCE_INSTANCES gri
475 WHERE
476 wdi.group_id = p_group_id
477 AND wdi.resource_id_new = gri.resource_id (+)
478 AND wdi.resource_instance_id = gri.instance_id (+)
479 AND wdi.process_status = 1
480 AND wdi.load_type in ('1','2','3','4','9','10') ;
481
482 EXCEPTION
483 WHEN no_data_found THEN
484 errbuf := ' No Data Found (detail): ' ||to_char(sqlcode);
485 retcode := 1; /* Warning */
486 delete_cancel_flag := 1 ;
487 delete_rsch_flag := 1 ;
488 delete_new_flag := 1 ;
489
490 WHEN others THEN
491 errbuf := 'Detail Insert failed: ' ||to_char(sqlcode);
492 retcode := 1; /* Warning */
493 NULL ;
494 END;
495
496 /* Following Lines delete the rows wip_job_schedule_interface after the rows are
497 inserted in gmp_aps_output_tbl */
498 BEGIN
499
500 IF delete_new_flag = 1 THEN
501 DELETE wip_job_schedule_interface
502 WHERE group_id = p_group_id
503 AND process_status = 1
504 AND load_type = 1 ;
505 DELETE wip_job_dtls_interface
506 WHERE group_id = p_group_id
507 AND process_status = 1 ;
508 END IF;
509
510 IF delete_rsch_flag = 1 THEN
511 DELETE wip_job_schedule_interface
512 WHERE group_id = p_group_id
513 AND process_status = 1
514 AND nvl(status_type,0 ) <> 7 /* Rescheduled Batch */
515 AND load_type = 3 ;
516 DELETE wip_job_dtls_interface
517 WHERE group_id = p_group_id
518 AND process_status = 1 ;
519 END IF;
520
521 IF delete_cancel_flag = 1 THEN
522 DELETE wip_job_schedule_interface
523 WHERE group_id = p_group_id
524 AND process_status = 1
525 AND nvl(status_type,0 ) = 7 /* Cancelled Batch */
526 AND load_type = 3 ;
527
528 DELETE wip_job_dtls_interface
529 WHERE group_id = p_group_id
530 AND process_status = 1 ;
531 END IF;
532
533 EXCEPTION
534 WHEN no_data_found THEN
535 errbuf := ' No Data Found (detail): ' ||to_char(sqlcode);
536 retcode := 1; /* Warning */
537
538 WHEN others THEN
539 errbuf := 'WIP deletion failed: ' ||to_char(sqlcode);
540 retcode := 1; /* Warning */
541 NULL ;
542 END;
543 COMMIT;
544
545 /* B3191962 - Start Calling the Package for Implementing the Auto Planning
546 Suggestions. This Package gets called only when the user sets the
547 GMP_AUTO_IMPLEMENT_APS_SUGG Profile to Yes
548 The Parameters to the GMPARELP Package plant_code,fitem_no,titem_no,
549 fdate, and tdate are all passed as NULL .. when automatic
550 */
551 BEGIN
552 l_profile := FND_PROFILE.VALUE('GMP_AUTO_IMPLEMENT_APS_SUGG');
553 IF l_profile = 1 THEN
554 /* B6167305 Start */
555 batch_fpo_profile := NVL(FND_PROFILE.VALUE('GMP_IMPLEMENT_SUGG_FPO_BATCH'),0);
556 /* B6167305 End */
557 lv_result := FND_REQUEST.SET_MODE(TRUE);
558 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Submitting the Req ');
559
560 -- RDP B5454215 - Added 0 when calling the reqauest.
561 -- Make Batch as defult value since there is no user input
562 X_conc_id := FND_REQUEST.SUBMIT_REQUEST
563 ('GMP', -- Application
564 'GMPRELAP', -- Conc Pgm Short Name
565 NULL, -- Description
566 sysdate, -- Start Time
567 FALSE, -- Subrequest
568 NULL, -- Plant Code
569 NULL, -- FItem Number
570 NULL, -- To Item Number
571 NULL, -- From Date
572 NULL, -- To Date
573 batch_fpo_profile -- Order Type Batch/FPO /* B6167305 */
574 );
575
576 FND_FILE.PUT_LINE(FND_FILE.LOG,' Submitted the Req X_conc_id -> '||X_conc_id);
577 --
578 IF X_conc_id = 0 THEN
579 RAISE ERROR_SUBMITTING_REQUEST;
580 ELSE
581 FND_MESSAGE.SET_NAME('GMP','MR_REQ_SUBMITTED');
582 FND_MESSAGE.SET_TOKEN('CONC_ID', X_conc_id);
583 FND_FILE.PUT_LINE ( FND_FILE.LOG,'-'||FND_MESSAGE.GET);
584 COMMIT ;
585 END IF;
586 ELSE
587 FND_FILE.PUT_LINE(FND_FILE.LOG,'Profile implement APS Suggestions is NOT turned ON, Use concurrent program or Use the GMP forms to implement APS suggestions ');
588 END IF;
589
590 EXCEPTION
591 WHEN ERROR_SUBMITTING_REQUEST THEN
592 G_log_text := FND_MESSAGE.GET;
593 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error submitting concurrent Request '||G_log_text);
594 retcode:=2; /* Error */
595 WHEN others THEN
596 errbuf := 'Call to Perform Auto release failed: ' ||sqlerrm;
597 retcode := 1; /* Warning */
598 END;
599
600 END insert_gmp_interface;
601
602 /*=========================================================================
603 | PROCEDURE NAME |
604 | retrieve_item_cost |
605 | |
606 | TYPE |
607 | public |
608 | |
609 | DESCRIPTION |
610 | |
611 | This function calls the costing function to get the item cost |
612 | |
613 | Input Parameters |
614 | |
615 | Output Parameters |
616 | |
617 | HISTORY |
618 | M Craig 12-Nov- 99 |
619 | 01-Apr-2005 Rajesh Patangya Modifed the item cost call for Release 12.0 |
620 | 25-Oct-2005 Abhay B4638839 No need to pass cost method etc , also widen |
621 | cost method size. |
622 ==========================================================================*/
623 FUNCTION retrieve_item_cost(
624 pitem_id IN NUMBER,
625 porgn_id IN NUMBER)
626 RETURN NUMBER
627 IS
628
629 item_cost NUMBER ;
630 ret_var NUMBER ;
631 v_item_cost NUMBER ;
632 cmpntcls_id NUMBER ;
633 analysis_code VARCHAR2(4) ;
634 gl_cost_method VARCHAR2(10) ;
635 num_rows NUMBER ;
636 v_init_msg_list VARCHAR2(2000) ;
637 v_return_status VARCHAR2(50) ;
638 v_msg_count NUMBER ;
639 v_msg_data VARCHAR2(2000);
640
641 v_item_id mtl_system_items.inventory_item_id%TYPE;
642 v_orgn_id mtl_system_items.organization_id%TYPE;
643
644 /* cursor to get the company(operating units) and the cost method */
645 /* Per the inventory convergence inputs from costing team
646 Sukarna and Uday - there is no need to pass cost method */
647
648 -- HW B4905324 - Removed the commented code so it will not be flagged
649 -- again as a performance issue
650
651 BEGIN
652
653 item_cost := 0;
654 ret_var := 0;
655 v_item_cost := 0;
656 cmpntcls_id := NULL;
657 analysis_code := NULL;
658 gl_cost_method := NULL;
659 num_rows := 0;
660 v_item_id := pitem_id;
661 v_orgn_id := porgn_id;
662 v_init_msg_list := FND_API.G_FALSE ;
663 v_return_status := NULL ;
664 v_msg_count := 0 ;
665 v_msg_data := NULL ;
666
667 /* call the costing function */
668 ret_var := gmf_cmcommon.Get_Process_Item_Cost(
669 1.0 , /* p_api_version */
670 v_init_msg_list ,
671 v_return_status ,
672 v_msg_count ,
673 v_msg_data ,
674 v_item_id ,
675 v_orgn_id ,
676 sysdate, /* Cost as on date */
677 1, /* 1 = total cost */
678 gl_cost_method ,
679 cmpntcls_id, /* cost_component_class_id */
680 analysis_code, /* cost_analysis_code */
681 item_cost, /* total cost */
682 num_rows); /* no of detail rows return */
683 dbms_output.put_line ('Cost I got was '|| item_cost ||'***') ;
684 IF item_cost > 0 THEN
685 v_item_cost := item_cost;
686 END IF;
687
688 RETURN v_item_cost;
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 RETURN 0;
693
694 END retrieve_item_cost;
695
696 /* ==========================================================================
697 * | PROCEDURE NAME |
698 * | retrieve_price_list |
699 * | |
700 * | TYPE |
701 * | public |
702 * | |
703 * | DESCRIPTION |
704 * | |
705 * | This function locates the price list for the item at the specified org |
706 * | |
707 * | Input Parameters |
708 * | |
709 * | Output Parameters |
710 * | |
711 * | HISTORY |
712 * | M Craig 08-Feb-2000 B1200400 |
713 * | |
714 * ==========================================================================*/
715 FUNCTION retrieve_price_list(
716 pitem_id IN NUMBER,
717 porgn_id IN NUMBER)
718 RETURN NUMBER
719 IS
720
721 BEGIN
722
723 RETURN 0 ;
724
725 EXCEPTION
726 WHEN OTHERS THEN
727 RETURN 0;
728
729 END retrieve_price_list;
730
731 END gmp_aps_output_pkg;