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