[Home] [Help]
248:
249: BEGIN
250:
251: IF PG_DEBUG <> 0 THEN
252: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::pTopAtoLineId::'||to_char(pTopAtoLineId),1);
253: END IF;
254:
255: --
256: -- For each model item in all possible receiving orgs, call
258: --
259:
260: lStmtNumber := 20;
261: IF PG_DEBUG <> 0 THEN
262: oe_debug_pub.add('populate_plan_level: ' || 'before loop',2);
263: END IF;
264:
265:
266: FOR v_model_lines IN c_model_lines LOOP
266: FOR v_model_lines IN c_model_lines LOOP
267:
268:
269: IF PG_DEBUG <> 0 THEN
270: oe_debug_pub.add('populate_plan_level: ' || 'loop::item::'||to_char(v_model_lines.inventory_item_id)||
271: '::line_id::'||to_char(v_model_lines.line_id),2);
272: END IF;
273: lStmtNumber := 30;
274:
273: lStmtNumber := 30;
274:
275: IF v_model_lines.ato_line_id = v_model_lines.line_id THEN
276: IF PG_DEBUG <> 0 THEN
277: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id = line_id',2);
278: END IF;
279:
280:
281: lStmtNumber := 40;
285: where line_id = v_model_lines.line_id;
286:
287: lStmtNumber := 50;
288: IF PG_DEBUG <> 0 THEN
289: oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
290:
291: oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
292: '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
293: '::ship_from_org::'||to_char(lShipFromOrgId),2);
287: lStmtNumber := 50;
288: IF PG_DEBUG <> 0 THEN
289: oe_debug_pub.add('populate_plan_level: ' || 'before calling GAIO',2);
290:
291: oe_debug_pub.add('populate_plan_level: ' || 'line_id::'||to_char(v_model_lines.line_id)||
292: '::inv_id::'||to_char(v_model_lines.inventory_item_id)||
293: '::ship_from_org::'||to_char(lShipFromOrgId),2);
294: END IF;
295:
302: x_msg_data);
303:
304: IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
305: IF PG_DEBUG <> 0 THEN
306: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
307: END IF;
308: raise FND_API.G_EXC_UNEXPECTED_ERROR;
309:
310: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
308: raise FND_API.G_EXC_UNEXPECTED_ERROR;
309:
310: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
311: IF PG_DEBUG <> 0 THEN
312: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
313: END IF;
314: raise FND_API.G_EXC_ERROR;
315: END IF;
316: IF PG_DEBUG <> 0 THEN
313: END IF;
314: raise FND_API.G_EXC_ERROR;
315: END IF;
316: IF PG_DEBUG <> 0 THEN
317: oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
318: END IF;
319: ELSE
320: IF PG_DEBUG <> 0 THEN
321: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
317: oe_debug_pub.add('populate_plan_level: ' || 'after calling GAIO::lStatus::'||to_char(lStatus),2);
318: END IF;
319: ELSE
320: IF PG_DEBUG <> 0 THEN
321: oe_debug_pub.add('populate_plan_level: ' || 'ato_line_id <> line_id',2);
322: END IF;
323: lStmtNumber := 60;
324: lLineId := v_model_lines.line_id;
325: IF PG_DEBUG <> 0 THEN
322: END IF;
323: lStmtNumber := 60;
324: lLineId := v_model_lines.line_id;
325: IF PG_DEBUG <> 0 THEN
326: oe_debug_pub.add('populate_plan_level: ' || 'before PSO loop',2);
327: END IF;
328:
329: FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
330: IF PG_DEBUG <> 0 THEN
327: END IF;
328:
329: FOR v_parent_src_ogs IN c_parent_src_orgs LOOP
330: IF PG_DEBUG <> 0 THEN
331: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
332: to_char(v_parent_src_ogs.organization_id),2);
333:
334: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
335: to_char(v_model_lines.inventory_item_id),2);
330: IF PG_DEBUG <> 0 THEN
331: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::rcv org::'||
332: to_char(v_parent_src_ogs.organization_id),2);
333:
334: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
335: to_char(v_model_lines.inventory_item_id),2);
336:
337: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
338: to_char(v_model_lines.line_id),2);
333:
334: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::item id::'||
335: to_char(v_model_lines.inventory_item_id),2);
336:
337: oe_debug_pub.add('populate_plan_level: ' || 'in PSO loop::line id::'||
338: to_char(v_model_lines.line_id),2);
339: END IF;
340: lStmtNumber := 70;
341: lStatus := get_all_item_orgs(v_model_lines.line_id,
346: x_msg_data);
347:
348: IF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
349: IF PG_DEBUG <> 0 THEN
350: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with unexp error',1);
351: END IF;
352: raise FND_API.G_EXC_UNEXPECTED_ERROR;
353:
354: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
352: raise FND_API.G_EXC_UNEXPECTED_ERROR;
353:
354: ELSIF (lStatus <> 1) AND (x_return_status = FND_API.G_RET_STS_ERROR) THEN
355: IF PG_DEBUG <> 0 THEN
356: oe_debug_pub.add('populate_plan_level: ' || 'GAIO returned with exp error',1);
357: END IF;
358: raise FND_API.G_EXC_ERROR;
359: END IF;
360:
363:
364: END LOOP;
365:
366: IF PG_DEBUG <> 0 THEN
367: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
368:
369: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
370:
371: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
365:
366: IF PG_DEBUG <> 0 THEN
367: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
368:
369: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
370:
371: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
372: END IF;
373:
367: oe_debug_pub.add('populate_plan_level: ' || 'end of loop',1);
368:
369: oe_debug_pub.add('populate_plan_level: ' || 'printing out bcso :', 2);
370:
371: oe_debug_pub.add('populate_plan_level: ' || 'line_id model_item_id rcv_org_id org_id create_bom create_src_rules', 2);
372: END IF;
373:
374: FOR v_debug IN c_debug LOOP
375: IF PG_DEBUG <> 0 THEN
372: END IF;
373:
374: FOR v_debug IN c_debug LOOP
375: IF PG_DEBUG <> 0 THEN
376: oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
377: to_char(v_debug.model_item_id)||' '||
378: nvl(to_char(v_debug.rcv_org_id),null)||' '||
379: to_char(v_debug.organization_id)||' '||
380: nvl(v_debug.create_bom, null)||' '||
387: EXCEPTION
388:
389: when FND_API.G_EXC_UNEXPECTED_ERROR then
390: IF PG_DEBUG <> 0 THEN
391: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::unexp error::'||lStmtNumber||sqlerrm,1);
392: END IF;
393: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394: CTO_MSG_PUB.Count_And_Get
395: (p_msg_count => x_msg_count
398: return(0);
399:
400: when FND_API.G_EXC_ERROR then
401: IF PG_DEBUG <> 0 THEN
402: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::exp error::'||lStmtNumber||sqlerrm,1);
403: END IF;
404: x_return_status := FND_API.G_RET_STS_ERROR;
405: CTO_MSG_PUB.Count_And_Get
406: (p_msg_count => x_msg_count
408: return(0);
409:
410: when others then
411: IF PG_DEBUG <> 0 THEN
412: oe_debug_pub.add('populate_plan_level: ' || 'Populate_Src_Orgs::others::'||lStmtNumber||sqlerrm,1);
413: END IF;
414: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
416: FND_MSG_PUB.Add_Exc_Msg
567: Null;
568: END;
569:
570: IF PG_DEBUG <> 0 THEN
571: oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
572:
573: oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
574:
575: oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
569:
570: IF PG_DEBUG <> 0 THEN
571: oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
572:
573: oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
574:
575: oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
576: END IF;
577:
571: oe_debug_pub.add('populate_plan_level: ' || 'top ato line id::'||to_char(lTopAtoLineId),2);
572:
573: oe_debug_pub.add('populate_plan_level: ' || 'rcv org id::'||to_char(pRcvOrgId),2);
574:
575: oe_debug_pub.add('populate_plan_level: ' || 'model item id::'||to_char(pModelItemId),2);
576: END IF;
577:
578: -- Added by Renga Kannan on 08/23/01 for procuring configuration
579:
577:
578: -- Added by Renga Kannan on 08/23/01 for procuring configuration
579:
580: IF PG_DEBUG <> 0 THEN
581: oe_debug_pub.add('populate_plan_level: ' || 'Parent ATO line id = '||l_parent_ato_line_id,1);
582:
583: oe_debug_pub.add('populate_plan_level: ' || 'Parent source type = '||l_source_type,1);
584: END IF;
585:
579:
580: IF PG_DEBUG <> 0 THEN
581: oe_debug_pub.add('populate_plan_level: ' || 'Parent ATO line id = '||l_parent_ato_line_id,1);
582:
583: oe_debug_pub.add('populate_plan_level: ' || 'Parent source type = '||l_source_type,1);
584: END IF;
585:
586:
587:
592:
593: v_source_type_code := 'INTERNAL' ;
594:
595: IF PG_DEBUG <> 0 THEN
596: oe_debug_pub.add('populate_plan_level: ' || ' pc bom source type code = '|| v_source_type_code ,1);
597: END IF;
598: else
599:
600: select source_type_code
602: from oe_order_lines_all
603: where line_id = pLineId ;
604:
605: IF PG_DEBUG <> 0 THEN
606: oe_debug_pub.add('populate_plan_level: ' || ' non pc bom source type code = '|| v_source_type_code ,1);
607: END IF;
608:
609: end if ;
610:
608:
609: end if ;
610:
611: IF PG_DEBUG <> 0 THEN
612: oe_debug_pub.add('populate_plan_level: ' || 'source type code = '|| v_source_type_code ,1);
613: END IF;
614:
615:
616: lStmtNumber := 30;
632: -- will be copied
633: lStmtNumber := 35;
634: l_source_type := 4;
635: IF PG_DEBUG <> 0 THEN
636: oe_debug_pub.add('populate_plan_level: ' || ' This is part of Buy model... No need to look for sourcing...',1);
637: END IF;
638:
639:
640:
650:
651:
652: l_source_type := 6 ;
653: IF PG_DEBUG <> 0 THEN
654: oe_debug_pub.add('populate_plan_level: ' || ' This is part of Drop Ship model...',1);
655: END IF;
656:
657:
658:
660: ELSE
661:
662: IF lMrpAssignmentSet is null THEN
663: IF PG_DEBUG <> 0 THEN
664: oe_debug_pub.add('populate_plan_level: ' || 'Default assignment set is null',1);
665: END IF;
666:
667:
668: -- added by Renga Kannan on 08/21/01
683: and organization_id = pRcvOrgId;
684:
685:
686: IF PG_DEBUG <> 0 THEN
687: oe_debug_pub.add('populate_plan_level: ' || 'Make buy code:: 1 means make 2 means buy',1);
688:
689: oe_debug_pub.add('populate_plan_level: ' || 'Planning make buy code for this item is ='||to_char(l_make_buy_code),1);
690: END IF;
691:
685:
686: IF PG_DEBUG <> 0 THEN
687: oe_debug_pub.add('populate_plan_level: ' || 'Make buy code:: 1 means make 2 means buy',1);
688:
689: oe_debug_pub.add('populate_plan_level: ' || 'Planning make buy code for this item is ='||to_char(l_make_buy_code),1);
690: END IF;
691:
692: IF l_make_buy_code = 2 then
693: l_source_type := 3; ----- Buy Type
704:
705:
706: ELSE
707: IF PG_DEBUG <> 0 THEN
708: oe_debug_pub.add('populate_plan_level: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
709: END IF;
710:
711:
712: --
732: BEGIN
733:
734: lStmtNumber := 50;
735: IF PG_DEBUG <> 0 THEN
736: oe_debug_pub.add('populate_plan_level: ' || 'Getting source for rcv org::'||to_char(l_curr_RcvOrgId), 2);
737: END IF;
738:
739: -- Modified by Renga Kannan on 08/21/01 to honor the BUY sourcing and make_buy_code also
740: -- The where condition in the select statement for source_type is removed. The source_type
778:
779: end if ;
780:
781: IF PG_DEBUG <> 0 THEN
782: oe_debug_pub.add('populate_plan_level: ' || 'Source for this rcv org::'||to_char(l_curr_src_org),2);
783:
784: oe_debug_pub.add('populate_plan_level: ' || 'Source type = '||to_char(l_source_type),2);
785: END IF;
786:
780:
781: IF PG_DEBUG <> 0 THEN
782: oe_debug_pub.add('populate_plan_level: ' || 'Source for this rcv org::'||to_char(l_curr_src_org),2);
783:
784: oe_debug_pub.add('populate_plan_level: ' || 'Source type = '||to_char(l_source_type),2);
785: END IF;
786:
787: EXCEPTION
788:
811: IF l_sourcing_rule_count > 0 then
812:
813: -- multiple sources defined
814: IF PG_DEBUG <> 0 THEN
815: oe_debug_pub.add('populate_plan_level: ' || 'Multiple sources defined for model '||to_char(pModelItemId)
816: ||', org '||to_char(l_curr_RcvOrgId), 1);
817: END IF;
818:
819:
827: raise FND_API.G_EXC_ERROR;
828: ELSE
829:
830: IF PG_DEBUG <> 0 THEN
831: oe_debug_pub.add('populate_plan_level: ' || 'This model is having buy sourcing rule...',1);
832: END IF;
833:
834:
835: if( v_source_type_code = 'INTERNAL' ) then
866:
867: -- end of chain, exit out of loop
868: -- update final src for BOM creation
869: IF PG_DEBUG <> 0 THEN
870: oe_debug_pub.add('populate_plan_level: ' || 'NDF::End of chain for model '||to_char(pModelItemId), 1);
871: END IF;
872:
873: -- added by Renga Kannan on 08/21/01
874: -- When there is no sourcing rule defined we need to check for the make_buy_type of the
894: EXCEPTION
895: WHEN NO_DATA_FOUND THEN
896:
897: IF PG_DEBUG <> 0 THEN
898: oe_debug_pub.add('populate_plan_level: ' || 'Inventory_item_id = '|| to_char(pModelItemId),1);
899:
900: oe_debug_pub.add('populate_plan_level: ' || 'Organization id = '|| to_char(l_curr_RcvOrgId),1);
901:
902: oe_debug_pub.add('populate_plan_level: ' || 'ERROR::The item is not defined in the sourcing org',1);
896:
897: IF PG_DEBUG <> 0 THEN
898: oe_debug_pub.add('populate_plan_level: ' || 'Inventory_item_id = '|| to_char(pModelItemId),1);
899:
900: oe_debug_pub.add('populate_plan_level: ' || 'Organization id = '|| to_char(l_curr_RcvOrgId),1);
901:
902: oe_debug_pub.add('populate_plan_level: ' || 'ERROR::The item is not defined in the sourcing org',1);
903: END IF;
904:
898: oe_debug_pub.add('populate_plan_level: ' || 'Inventory_item_id = '|| to_char(pModelItemId),1);
899:
900: oe_debug_pub.add('populate_plan_level: ' || 'Organization id = '|| to_char(l_curr_RcvOrgId),1);
901:
902: oe_debug_pub.add('populate_plan_level: ' || 'ERROR::The item is not defined in the sourcing org',1);
903: END IF;
904:
905:
906: -- The following message handling is modified by Renga Kannan
916:
917: -- End of addition by Renga on 11/07/01
918:
919: IF PG_DEBUG <> 0 THEN
920: oe_debug_pub.add('populate_plan_level: ' || 'Planning Make_buy_code for this model = '||l_make_buy_code,1);
921: END IF;
922:
923: IF l_make_buy_code = 2 then
924: l_source_type := 3; ----- Buy Type
922:
923: IF l_make_buy_code = 2 then
924: l_source_type := 3; ----- Buy Type
925: IF PG_DEBUG <> 0 THEN
926: oe_debug_pub.add('populate_plan_level: ' || 'This model is a buy model...',1);
927: END IF;
928: END IF;
929:
930:
934: l_source_type := 5; ----- Drop Ship Type
935: l_curr_src_org := l_curr_RcvOrgId;
936: l_curr_rank := null;
937: IF PG_DEBUG <> 0 THEN
938: oe_debug_pub.add('populate_plan_level: ' || 'This model is a buy model...',1);
939: END IF;
940:
941: end if ;
942:
949: and model_item_id = pModelItemId
950: and organization_id = l_curr_src_org;
951:
952: IF PG_DEBUG <> 0 THEN
953: oe_debug_pub.add('populate_plan_level: ' || 'Rows updated::'||sql%rowcount,2);
954: END IF;
955: EXIT;
956:
957:
967: IF l_circular_src = 'Y' THEN
968: -- circular sourcing defined
969: lStmtNumber := 110;
970: IF PG_DEBUG <> 0 THEN
971: oe_debug_pub.add('populate_plan_level: ' || 'Circular sourcing defined for model '
972: ||to_char(pModelItemId)
973: ||' in org '
974: ||to_char(pRcvOrgId), 1);
975: END IF;
1032: IF (l_curr_src_org = l_curr_RcvOrgId) THEN
1033: -- end of chain, exit out of loop
1034: -- update final src for BOM creation
1035: IF PG_DEBUG <> 0 THEN
1036: oe_debug_pub.add('populate_plan_level: ' || 'End of chain for model '||to_char(pModelItemId), 1);
1037: END IF;
1038:
1039:
1040: --- The source type column is added by Renga Kannan on 08/21/01
1050: EXIT;
1051: END IF;
1052:
1053: IF PG_DEBUG <> 0 THEN
1054: oe_debug_pub.add('populate_plan_level: ' || 'inserted rcv org::'||to_char(l_curr_RcvOrgId)||' src org::'||to_char(l_curr_src_org), 2);
1055: END IF;
1056: l_curr_RcvOrgId := l_curr_src_org;
1057:
1058: END LOOP;
1057:
1058: END LOOP;
1059: --<
1060: IF PG_DEBUG <> 0 THEN
1061: oe_debug_pub.add('populate_plan_level: ' || 'after insert 1',2);
1062: END IF;
1063: END IF; /* MRP profile is not null */
1064:
1065: END IF; /* check for DROP SHIP , BUY_ITEM_FLAG is not Y */
1121: where line_id = pLineId
1122: and model_item_id = pModelItemId);
1123:
1124: IF PG_DEBUG <> 0 THEN
1125: oe_debug_pub.add('populate_plan_level: ' || 'after insert 2',2);
1126: END IF;
1127:
1128: --
1129: -- Next, populate bom_cto_src_orgs with the
1185: and model_item_id = pModelItemId
1186: and organization_id = pRcvOrgId);
1187:
1188: IF PG_DEBUG <> 0 THEN
1189: oe_debug_pub.add('populate_plan_level: ' || 'after insert 3',2);
1190: END IF;
1191:
1192: --
1193: -- Next, get the OE validation org and populate it in
1196: -- do nothing, else insert a new row
1197: --
1198: lStmtNumber := 170;
1199: IF PG_DEBUG <> 0 THEN
1200: oe_debug_pub.add('populate_plan_level: ' || 'Before getting validation org',2);
1201: END IF;
1202:
1203: lStmtNumber := 180;
1204:
1224:
1225: */
1226:
1227: IF PG_DEBUG <> 0 THEN
1228: oe_debug_pub.add('populate_plan_level: ' || 'Going to fetch Validation Org ' ,2);
1229: END IF;
1230:
1231:
1232: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
1242: cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING');
1243: raise FND_API.G_EXC_ERROR;
1244: end if;
1245: IF PG_DEBUG <> 0 THEN
1246: oe_debug_pub.add('populate_plan_level: ' || 'Validation Org is :' || lValidationOrg,2);
1247: END IF;
1248:
1249: lStmtNumber := 190;
1250:
1298: and model_item_id = pModelItemId
1299: and organization_id = lVAlidationOrg);
1300:
1301: IF PG_DEBUG <> 0 THEN
1302: oe_debug_pub.add('populate_plan_level: ' || 'after insert 4',2);
1303: END IF;
1304:
1305: --
1306: -- Next, get the PO validation org and populate it in
1310: -- If the org already exists for this item,
1311: -- do nothing, else insert a new row
1312: --
1313: IF PG_DEBUG <> 0 THEN
1314: oe_debug_pub.add('populate_plan_level: ' || 'Before getting validation org',2);
1315: END IF;
1316:
1317: lStmtNumber := 200;
1318: BEGIN
1360: end if ;
1361:
1362:
1363: IF PG_DEBUG <> 0 THEN
1364: oe_debug_pub.add('populate_plan_level: ' || 'Po Validation Org ='||to_char(lpovalidationorg),1);
1365: END IF;
1366:
1367:
1368:
1379: IF l_source_type in (3,4, 5, 6 ) THEN
1380:
1381: if lPoVAlidationOrg = -99 then --bugfix 2646849
1382: IF PG_DEBUG <> 0 THEN
1383: oe_debug_pub.add('populate_plan_level: ' || 'No Povalidation org is defined .. Need to error out..',1);
1384: END IF;
1385: cto_msg_pub.cto_message('BOM','CTO_PO_VALIDATION');
1386: raise po_multiorg_error;
1387: end if;
1395:
1396: -- End of addition
1397:
1398: IF PG_DEBUG <> 0 THEN
1399: oe_debug_pub.add('populate_plan_level: ' || 'PO Validation Org is :' ||lPoValidationOrg,2);
1400: END IF;
1401:
1402: lStmtNumber := 210;
1403:
1451: and model_item_id = pModelItemId
1452: and organization_id = lPoVAlidationOrg);
1453:
1454: IF PG_DEBUG <> 0 THEN
1455: oe_debug_pub.add('populate_plan_level: ' || 'after insert 5',2);
1456: END IF;
1457:
1458:
1459: -- Added By Renga Kannan on 10/31/02 for Global Purchase agreement
1514:
1515: EXCEPTION
1516: WHEN no_data_found THEN
1517: IF PG_DEBUG <> 0 THEN
1518: oe_debug_pub.add('populate_plan_level: ' || 'PO validation org is NULL, not inserting row for PO val org',2);
1519: END IF;
1520: null;
1521: WHEN po_multiorg_error THEN
1522: IF PG_DEBUG <> 0 THEN
1519: END IF;
1520: null;
1521: WHEN po_multiorg_error THEN
1522: IF PG_DEBUG <> 0 THEN
1523: oe_debug_pub.add('populate_plan_level: ' || 'po_multiorg_error, not inserting row for PO val org',2);
1524: END IF;
1525: Raise FND_API.G_EXC_ERROR;
1526: WHEN others THEN
1527: IF PG_DEBUG <> 0 THEN
1524: END IF;
1525: Raise FND_API.G_EXC_ERROR;
1526: WHEN others THEN
1527: IF PG_DEBUG <> 0 THEN
1528: oe_debug_pub.add('populate_plan_level: ' || 'others exception in PO validation block, not inserting row for PO val org',2);
1529: END IF;
1530: null;
1531: END;
1532: return(1);
1533:
1534: EXCEPTION
1535: when FND_API.G_EXC_ERROR then
1536: IF PG_DEBUG <> 0 THEN
1537: oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::exp error::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1538: END IF;
1539: x_return_status := FND_API.G_RET_STS_ERROR;
1540: CTO_MSG_PUB.Count_And_Get
1541: (p_msg_count => x_msg_count
1544: return(0);
1545:
1546: when FND_API.G_EXC_UNEXPECTED_ERROR then
1547: IF PG_DEBUG <> 0 THEN
1548: oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::unexp error::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1549: END IF;
1550: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551: CTO_MSG_PUB.Count_And_Get (
1552: p_msg_count => x_msg_count
1555: return(0);
1556:
1557: when OTHERS then
1558: IF PG_DEBUG <> 0 THEN
1559: oe_debug_pub.add('populate_plan_level: ' || 'Get_All_item_orgs::others::'||to_char(lStmtNumber)||'::'||sqlerrm,1);
1560: END IF;
1561: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1562: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1563: FND_MSG_PUB.Add_Exc_Msg
1600: and inventory_item_id = pModelId;
1601:
1602: if sql%notfound then
1603: IF PG_DEBUG <> 0 THEN
1604: oe_debug_pub.add('populate_plan_level: ' || 'Update_Order_Lines:: ndf::model line does not exist in bcol'||to_char(lStmtNumber)||sqlerrm,1);
1605: END IF;
1606: return(0);
1607: else
1608: return(1);
1610:
1611: EXCEPTION
1612: when others then
1613: IF PG_DEBUG <> 0 THEN
1614: oe_debug_pub.add('populate_plan_level: ' || 'Update_Order_Lines:: others exception'||to_char(lStmtNumber)||sqlerrm,1);
1615: END IF;
1616: return(0);
1617:
1618: END Update_Order_Lines;
1641: and model_item_id = pModelId;
1642:
1643: if sql%notfound then
1644: IF PG_DEBUG <> 0 THEN
1645: oe_debug_pub.add('populate_plan_level: ' || 'Update_Src_Orgs:: Could not update the config item: '||sqlerrm,1);
1646: END IF;
1647: return(0);
1648: else
1649: return(1);
1653: EXCEPTION
1654:
1655: when others then
1656: IF PG_DEBUG <> 0 THEN
1657: oe_debug_pub.add('populate_plan_level: ' || 'Update_Src_Orgs:: others exception'||sqlerrm,1);
1658: END IF;
1659: return(0);
1660:
1661: END Update_Src_Orgs;
1707: lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
1708:
1709: IF lMrpAssignmentSet is null THEN
1710: IF PG_DEBUG <> 0 THEN
1711: oe_debug_pub.add('populate_plan_level: ' || 'Default assignment set is null, returning from create_sourcing_rules procedure',1);
1712: END IF;
1713: return;
1714: ELSE
1715: IF PG_DEBUG <> 0 THEN
1712: END IF;
1713: return;
1714: ELSE
1715: IF PG_DEBUG <> 0 THEN
1716: oe_debug_pub.add('populate_plan_level: ' || 'Default assignment set is '||to_char(lMrpAssignmentSet),2);
1717: END IF;
1718: END IF;
1719:
1720: --
1755: EXCEPTION
1756: WHEN NO_DATA_FOUND THEN
1757:
1758: IF PG_DEBUG <> 0 THEN
1759: oe_debug_pub.add('populate_plan_level: ' || 'There is no sourcing rule defined ',1);
1760: END IF;
1761: raise no_sourcing_defined;
1762:
1763: END;
1763: END;
1764:
1765:
1766: IF PG_DEBUG <> 0 THEN
1767: oe_debug_pub.add('populate_plan_level: ' || 'lAssnType::'||to_char(lAssignmentType)||'::lAssnId::'||to_char(lAssignmentId),2);
1768: END IF;
1769:
1770: --
1771: -- copy assignment into lAssignmentRec
1861: FROM MRP_SR_ASSIGNMENTS
1862: WHERE ASSIGNMENT_ID = lAssignmentId;
1863:
1864: IF PG_DEBUG <> 0 THEN
1865: oe_debug_pub.add('populate_plan_level: ' || 'after query row',2);
1866: END IF;
1867:
1868: --
1869: -- check if this assignment already exists for config item
1871: lStmtNum := 35;
1872: BEGIN
1873:
1874: IF PG_DEBUG <> 0 THEN
1875: oe_debug_pub.add('populate_plan_level: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
1876:
1877: oe_debug_pub.add('populate_plan_level: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
1878:
1879: oe_debug_pub.add('populate_plan_level: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
1873:
1874: IF PG_DEBUG <> 0 THEN
1875: oe_debug_pub.add('populate_plan_level: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
1876:
1877: oe_debug_pub.add('populate_plan_level: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
1878:
1879: oe_debug_pub.add('populate_plan_level: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
1880:
1881: oe_debug_pub.add('populate_plan_level: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
1875: oe_debug_pub.add('populate_plan_level: ' || 'assignment_set_id::'||to_char(lAssignmentRec.assignment_set_id),2);
1876:
1877: oe_debug_pub.add('populate_plan_level: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
1878:
1879: oe_debug_pub.add('populate_plan_level: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
1880:
1881: oe_debug_pub.add('populate_plan_level: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
1882:
1883: oe_debug_pub.add('populate_plan_level: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
1877: oe_debug_pub.add('populate_plan_level: ' || 'assignment_type::'||to_char(lAssignmentRec.assignment_type),2);
1878:
1879: oe_debug_pub.add('populate_plan_level: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
1880:
1881: oe_debug_pub.add('populate_plan_level: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
1882:
1883: oe_debug_pub.add('populate_plan_level: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
1884:
1885: oe_debug_pub.add('populate_plan_level: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
1879: oe_debug_pub.add('populate_plan_level: ' || 'organization_id::'||to_char(lAssignmentRec.organization_id),2);
1880:
1881: oe_debug_pub.add('populate_plan_level: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
1882:
1883: oe_debug_pub.add('populate_plan_level: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
1884:
1885: oe_debug_pub.add('populate_plan_level: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
1886:
1887: oe_debug_pub.add('populate_plan_level: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
1881: oe_debug_pub.add('populate_plan_level: ' || 'customer_id::'||to_char(lAssignmentRec.customer_id),2);
1882:
1883: oe_debug_pub.add('populate_plan_level: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
1884:
1885: oe_debug_pub.add('populate_plan_level: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
1886:
1887: oe_debug_pub.add('populate_plan_level: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
1888:
1889: oe_debug_pub.add('populate_plan_level: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
1883: oe_debug_pub.add('populate_plan_level: ' || 'ship_to_site_id::'||to_char(lAssignmentRec.ship_to_site_id),2);
1884:
1885: oe_debug_pub.add('populate_plan_level: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
1886:
1887: oe_debug_pub.add('populate_plan_level: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
1888:
1889: oe_debug_pub.add('populate_plan_level: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
1890: END IF;
1891:
1885: oe_debug_pub.add('populate_plan_level: ' || 'sourcing_rule_type::'||to_char(lAssignmentRec.sourcing_rule_type),2);
1886:
1887: oe_debug_pub.add('populate_plan_level: ' || 'inventory_item_id:: '||to_char(pConfigId),2);
1888:
1889: oe_debug_pub.add('populate_plan_level: ' || 'category_id:: '||to_char(lAssignmentRec.category_id),2);
1890: END IF;
1891:
1892: -- bug 6617686
1893: IF pConfigId IS NOT NULL THEN
1918: -- end: bug 6617686
1919:
1920: IF lAssignmentExists = 1 THEN
1921: IF PG_DEBUG <> 0 THEN
1922: oe_debug_pub.add('populate_plan_level: ' || 'assignment exists already, do not recreate',2);
1923: END IF;
1924: return;
1925: END IF;
1926:
1926:
1927: EXCEPTION
1928: when NO_DATA_FOUND then
1929: IF PG_DEBUG <> 0 THEN
1930: oe_debug_pub.add('populate_plan_level: ' || 'assignment does not exist, create it',2);
1931: END IF;
1932: when OTHERS then
1933: IF PG_DEBUG <> 0 THEN
1934: oe_debug_pub.add('populate_plan_level: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
1930: oe_debug_pub.add('populate_plan_level: ' || 'assignment does not exist, create it',2);
1931: END IF;
1932: when OTHERS then
1933: IF PG_DEBUG <> 0 THEN
1934: oe_debug_pub.add('populate_plan_level: ' || 'others exception while checking ifassignment exists, not handling, creating assignment:: '||sqlerrm,2);
1935: END IF;
1936: END;
1937:
1938: --
1987: lAssignmentTbl(1).db_flag := NULL;
1988: lAssignmentTbl(1).operation := MRP_Globals.G_OPR_CREATE;
1989:
1990: IF PG_DEBUG <> 0 THEN
1991: oe_debug_pub.add('populate_plan_level: ' || 'after forming lAssignmentTbl',2);
1992: END IF;
1993:
1994: --
1995: -- form lAssignmentSetRec
1996: --
1997: lStmtNum := 50;
1998: lAssignmentSetRec.operation := MRP_Globals.G_OPR_NONE;
1999: IF PG_DEBUG <> 0 THEN
2000: oe_debug_pub.add('populate_plan_level: ' || 'after forming lAssignmentSetRec',2);
2001: END IF;
2002:
2003: --
2004: -- call mrp API to insert rec into assignment set
2004: -- call mrp API to insert rec into assignment set
2005: --
2006: lStmtNum := 60;
2007: IF PG_DEBUG <> 0 THEN
2008: oe_debug_pub.add('populate_plan_level: ' || 'before Process_Assignment',2);
2009: END IF;
2010:
2011: -- currently, not passing commented out parameters, need to
2012: -- confirm with raghu, confirmed with stupe
2030: );
2031:
2032: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2033: IF PG_DEBUG <> 0 THEN
2034: oe_debug_pub.add('populate_plan_level: ' || 'unexp error in process_assignment::'||sqlerrm,1);
2035: END IF;
2036: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2037:
2038: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2036: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2037:
2038: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2039: IF PG_DEBUG <> 0 THEN
2040: oe_debug_pub.add('populate_plan_level: ' || 'error in process_assignment::'||sqlerrm,1);
2041: END IF;
2042: raise FND_API.G_EXC_ERROR;
2043:
2044: END IF;
2042: raise FND_API.G_EXC_ERROR;
2043:
2044: END IF;
2045: IF PG_DEBUG <> 0 THEN
2046: oe_debug_pub.add('populate_plan_level: ' || 'success in process_assignment',2);
2047: END IF;
2048:
2049: EXCEPTION
2050: When NO_sourcing_defined THEN
2051: null;
2052:
2053: when FND_API.G_EXC_ERROR then
2054: IF PG_DEBUG <> 0 THEN
2055: oe_debug_pub.add('populate_plan_level: ' || 'Create_Src_Rules::exp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
2056: END IF;
2057: x_return_status := FND_API.G_RET_STS_ERROR;
2058: CTO_MSG_PUB.Count_And_Get
2059: (p_msg_count => x_msg_count
2061: );
2062:
2063: when FND_API.G_EXC_UNEXPECTED_ERROR then
2064: IF PG_DEBUG <> 0 THEN
2065: oe_debug_pub.add('populate_plan_level: ' || 'Create_Src_Rules::unexp error::'||to_char(lStmtNum)||'::'||sqlerrm,1);
2066: END IF;
2067: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2068: CTO_MSG_PUB.Count_And_Get
2069: (p_msg_count => x_msg_count
2071: );
2072:
2073: when OTHERS then
2074: IF PG_DEBUG <> 0 THEN
2075: oe_debug_pub.add('populate_plan_level: ' || 'Create_Src_Rules::others::'||to_char(lStmtNum)||'::'||sqlerrm,1);
2076: END IF;
2077: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2078: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2079: FND_MSG_PUB.Add_Exc_Msg
2204: into v_mfg_comp_seq_id
2205: from dual;
2206:
2207: IF PG_DEBUG <> 0 THEN
2208: oe_debug_pub.add('populate_bcol: ' || ' sequence ' || v_mfg_comp_seq_id , 3);
2209: END IF;
2210:
2211:
2212: v_step := 'Step A2' ;
2218: and line_id = p_bcol_line_id ;
2219:
2220:
2221: IF PG_DEBUG <> 0 THEN
2222: oe_debug_pub.add('populate_bcol: ' || ' inventory item id ' || v_inventory_item_id , 3);
2223: END IF;
2224:
2225:
2226: v_step := 'Step A3' ;
2240:
2241:
2242:
2243: IF PG_DEBUG <> 0 THEN
2244: oe_debug_pub.add('populate_bcol: ' || 'Going to fetch Validation Org ' ,2);
2245: END IF;
2246:
2247:
2248: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
2253:
2254:
2255:
2256: IF PG_DEBUG <> 0 THEN
2257: oe_debug_pub.add('populate_bcol: ' || ' master org id ' || v_organization_id , 3 );
2258: END IF;
2259:
2260: /*
2261: ** If the profile BOM:Allow Multilevel ATO is set to "NO" then
2266: /* v_multilevel := FND_PROFILE.VALUE('BOM:MULTILEVEL_ATO');
2267: ** v_multilevel := nvl(v_multilevel , 'N' ) ;
2268:
2269:
2270: ** oe_debug_pub.add('ML profile is '||v_multilevel, 2);
2271: */
2272:
2273:
2274: lMatchProfile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
2353: end if;
2354:
2355:
2356:
2357: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2358: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2359: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2360: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2361:
2354:
2355:
2356:
2357: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2358: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2359: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2360: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2361:
2362:
2355:
2356:
2357: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2358: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2359: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2360: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2361:
2362:
2363: t_bcol(v_bcol_line_id).Request_Id := FND_GLOBAL.CONC_REQUEST_ID;
2356:
2357: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2358: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2359: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2360: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2361:
2362:
2363: t_bcol(v_bcol_line_id).Request_Id := FND_GLOBAL.CONC_REQUEST_ID;
2364: t_bcol(v_bcol_line_id).Program_Id := FND_GLOBAL.CONC_PROGRAM_ID;
2368:
2369: /* set plan level to 0 for top level item */
2370: t_bcol(v_bcol_line_id).plan_level := 0 ;
2371: IF PG_DEBUG <> 0 THEN
2372: oe_debug_pub.add('populate_bcol: ' || ' ----- setting plan level = 0 for base item ' , 3 );
2373: END IF;
2374:
2375: exception
2376: when others then
2374:
2375: exception
2376: when others then
2377: IF PG_DEBUG <> 0 THEN
2378: oe_debug_pub.add('populate_bcol: ' || ' got into exception for top level item at ' || v_step , 1 );
2379:
2380: oe_debug_pub.add('populate_bcol: ' || 'error in top ato model line id::'||sqlerrm , 1 );
2381: END IF;
2382: cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
2376: when others then
2377: IF PG_DEBUG <> 0 THEN
2378: oe_debug_pub.add('populate_bcol: ' || ' got into exception for top level item at ' || v_step , 1 );
2379:
2380: oe_debug_pub.add('populate_bcol: ' || 'error in top ato model line id::'||sqlerrm , 1 );
2381: END IF;
2382: cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
2383: raise FND_API.G_EXC_ERROR;
2384:
2433: if( upper( v_bcol_item_type_code) = 'CONFIG' ) then
2434: /* you need to error out as the config item exists */
2435:
2436: IF PG_DEBUG <> 0 THEN
2437: oe_debug_pub.add('populate_bcol: ' || ' ---- CONFIG Exists ', 1 );
2438: END IF;
2439:
2440: cto_msg_pub.cto_message('BOM','CTO_CONFIG_ITEM_EXISTS');
2441: raise FND_API.G_EXC_ERROR;
2470: else
2471: t_bcol(v_bcol_line_id).perform_match := 'N' ;
2472: end if;
2473:
2474: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2475: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2476: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2477: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2478:
2471: t_bcol(v_bcol_line_id).perform_match := 'N' ;
2472: end if;
2473:
2474: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2475: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2476: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2477: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2478:
2479:
2472: end if;
2473:
2474: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2475: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2476: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2477: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2478:
2479:
2480: t_bcol(v_bcol_line_id).Request_Id := FND_GLOBAL.CONC_REQUEST_ID;
2473:
2474: oe_debug_pub.add('bcol info : CONC REQ PARAMS ' , 3) ;
2475: oe_debug_pub.add('bcol info : CONC REQ ' || FND_GLOBAL.CONC_REQUEST_ID , 3 );
2476: oe_debug_pub.add('bcol info : CONC PROG ' || FND_GLOBAL.CONC_PROGRAM_ID , 3 );
2477: oe_debug_pub.add('bcol info : PROG APPL ' || FND_GLOBAL.PROG_APPL_ID , 3 );
2478:
2479:
2480: t_bcol(v_bcol_line_id).Request_Id := FND_GLOBAL.CONC_REQUEST_ID;
2481: t_bcol(v_bcol_line_id).Program_Id := FND_GLOBAL.CONC_PROGRAM_ID;
2495: ** this loop can be discarded
2496: */
2497:
2498: IF PG_DEBUG <> 0 THEN
2499: oe_debug_pub.add('populate_bcol: ' || 'source data ' , 3 );
2500:
2501: oe_debug_pub.add('populate_bcol: ' || 'line_id top_model ato link_to plan_level ', 1 );
2502: END IF;
2503:
2497:
2498: IF PG_DEBUG <> 0 THEN
2499: oe_debug_pub.add('populate_bcol: ' || 'source data ' , 3 );
2500:
2501: oe_debug_pub.add('populate_bcol: ' || 'line_id top_model ato link_to plan_level ', 1 );
2502: END IF;
2503:
2504: v_step := 'Step A8' ;
2505:
2512: loop
2513:
2514: if( t_bcol.exists(i) ) then
2515: IF PG_DEBUG <> 0 THEN
2516: oe_debug_pub.add('populate_bcol: ' || t_bcol(i).line_id || ' ' || t_bcol(i).top_model_line_id
2517: || ' A ' || t_bcol(i).ato_line_id
2518: || ' LN ' || t_bcol(i).link_to_line_id
2519: || ' PAL ' || t_bcol(i).parent_ato_line_id
2520: || ' P ' || t_bcol(i).plan_level
2539: , l_return_status );
2540:
2541: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2542: IF PG_DEBUG <> 0 THEN
2543: oe_debug_pub.add('populate_bcol: ' || 'unexp error in update_mfg_comp_seq_id::'||sqlerrm , 1 );
2544: END IF;
2545: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2546:
2547: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2545: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2546:
2547: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2548: IF PG_DEBUG <> 0 THEN
2549: oe_debug_pub.add('populate_bcol: ' || 'error in update_mfg_comp_seq_id::'||sqlerrm , 1 );
2550: END IF;
2551: raise FND_API.G_EXC_ERROR;
2552: END IF;
2553:
2587: */
2588: t_bcol(p_bcol_line_id).parent_ato_line_id := p_bcol_line_id;
2589:
2590: IF PG_DEBUG <> 0 THEN
2591: oe_debug_pub.add('populate_bcol: ' || 'processed data ' , 4 );
2592:
2593: oe_debug_pub.add('populate_bcol: ' || 'line_id top_model ato link_to plan_level ' , 3 );
2594:
2595: oe_debug_pub.add('populate_bcol: ' || 'going to check for invalid model setup ' , 4 );
2589:
2590: IF PG_DEBUG <> 0 THEN
2591: oe_debug_pub.add('populate_bcol: ' || 'processed data ' , 4 );
2592:
2593: oe_debug_pub.add('populate_bcol: ' || 'line_id top_model ato link_to plan_level ' , 3 );
2594:
2595: oe_debug_pub.add('populate_bcol: ' || 'going to check for invalid model setup ' , 4 );
2596:
2597: END IF;
2591: oe_debug_pub.add('populate_bcol: ' || 'processed data ' , 4 );
2592:
2593: oe_debug_pub.add('populate_bcol: ' || 'line_id top_model ato link_to plan_level ' , 3 );
2594:
2595: oe_debug_pub.add('populate_bcol: ' || 'going to check for invalid model setup ' , 4 );
2596:
2597: END IF;
2598:
2599:
2610:
2611: if( t_bcol(t_bcol(i).parent_ato_line_id).config_creation = 3) then
2612:
2613: IF PG_DEBUG <> 0 THEN
2614: oe_debug_pub.add('populate_bcol: ' || 'INVALID MODEL SETUP exists for line id ' || t_bcol(i).line_id
2615: || ' model item ' || t_bcol(i).inventory_item_id
2616: || ' item type ' || t_bcol(i).config_creation
2617: , 1 );
2618: oe_debug_pub.add('populate_bcol: ' || ' parent line id ' || t_bcol(t_bcol(i).parent_ato_line_id).line_id
2614: oe_debug_pub.add('populate_bcol: ' || 'INVALID MODEL SETUP exists for line id ' || t_bcol(i).line_id
2615: || ' model item ' || t_bcol(i).inventory_item_id
2616: || ' item type ' || t_bcol(i).config_creation
2617: , 1 );
2618: oe_debug_pub.add('populate_bcol: ' || ' parent line id ' || t_bcol(t_bcol(i).parent_ato_line_id).line_id
2619: || ' parent model item ' || t_bcol(t_bcol(i).parent_ato_line_id).inventory_item_id
2620: || ' parent item type ' || t_bcol(t_bcol(i).parent_ato_line_id).config_creation
2621: , 1 );
2622:
2641: ** PENDING WORK!!!!
2642: */
2643:
2644: if( lMatchProfile = 1 ) then
2645: oe_debug_pub.add('populate_bcol: ' || ' preparing information for v_match_flag_tab ' , 3 );
2646: i :=t_bcol.first ;
2647:
2648: while i is not null
2649: loop
2660:
2661: end loop ;
2662:
2663:
2664: oe_debug_pub.add('populate_bcol: ' || ' going to call cto_match_config.evaluate_n_pop_match_flag ' , 3 );
2665:
2666: cto_match_config.evaluate_n_pop_match_flag( p_match_flag_tab => v_match_flag_tab
2667: , x_sparse_tab => v_sparse_tab
2668: , x_return_status => x_return_status
2672:
2673:
2674:
2675:
2676: oe_debug_pub.add('populate_bcol: ' || ' populating match flag from results ' , 3 );
2677:
2678: i := v_sparse_tab.first ;
2679:
2680: while i is not null
2685: i := v_sparse_tab.next(i) ;
2686:
2687: end loop ;
2688:
2689: oe_debug_pub.add('populate_bcol: ' || ' done populating match flag from results ' , 3 );
2690:
2691:
2692: else
2693:
2690:
2691:
2692: else
2693:
2694: oe_debug_pub.add('populate_bcol: ' || ' will not be calling cto_match_config.evaluate_n_pop_match_flag ' , 3 );
2695:
2696: end if ;
2697:
2698:
2711: if( t_bcol.exists(i) ) then
2712:
2713: IF PG_DEBUG <> 0 THEN
2714:
2715: oe_debug_pub.add('populate_bcol: ' || t_bcol(i).line_id || ' ' || t_bcol(i).top_model_line_id
2716: || ' A ' || t_bcol(i).ato_line_id
2717: || ' LN ' || t_bcol(i).link_to_line_id
2718: || ' PAL ' || t_bcol(i).parent_ato_line_id
2719: || ' P ' || t_bcol(i).plan_level
2808: ,t_bcol(i).config_creation );
2809:
2810:
2811: IF PG_DEBUG <> 0 THEN
2812: oe_debug_pub.add('populate_bcol: bcol ' || t_bcol(i).line_id , 1 );
2813: END IF;
2814:
2815:
2816: else
2889: ,t_bcol(i).SHIP_FROM_ORG_ID --bugfix 3555026
2890: ) ;
2891:
2892: IF PG_DEBUG <> 0 THEN
2893: oe_debug_pub.add('populate_bcol: bcol_gt ' || t_bcol(i).line_id , 1 );
2894: END IF;
2895:
2896:
2897: end if ; /* check for reschedule flag */
2905:
2906:
2907:
2908: IF PG_DEBUG <> 0 THEN
2909: oe_debug_pub.add('populate_bcol: ' || 'success in populate bcol ', 1 );
2910: END IF;
2911:
2912: exception
2913:
2912: exception
2913:
2914: when FND_API.G_EXC_ERROR then
2915: IF PG_DEBUG <> 0 THEN
2916: oe_debug_pub.add('populate_bcol: ' || 'populate_Bcol::exp error::'|| v_step ||'::'||sqlerrm , 1);
2917: END IF;
2918: x_return_status := FND_API.G_RET_STS_ERROR;
2919: CTO_MSG_PUB.Count_And_Get
2920: (p_msg_count => x_msg_count
2922: );
2923:
2924: when FND_API.G_EXC_UNEXPECTED_ERROR then
2925: IF PG_DEBUG <> 0 THEN
2926: oe_debug_pub.add('populate_bcol: ' || 'populate_Bcol::unexp error::'|| v_step ||'::'||sqlerrm , 1);
2927: END IF;
2928: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2929: CTO_MSG_PUB.Count_And_Get
2930: (p_msg_count => x_msg_count
2932: );
2933:
2934: when OTHERS then
2935: IF PG_DEBUG <> 0 THEN
2936: oe_debug_pub.add('populate_bcol: ' || 'populate_Bcol::others::'|| v_step ||'::'||sqlerrm , 1 );
2937: END IF;
2938: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2939: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2940: FND_MSG_PUB.Add_Exc_Msg
3086: v_src_point := i ;
3087: /* please note, here it stores the index which is the same as line_id due to sparse array*/
3088:
3089: IF PG_DEBUG <> 0 THEN
3090: oe_debug_pub.add('populate_parent_ato: ' || ' processing ' || to_char( v_src_point ) , 3 );
3091: END IF;
3092: /*
3093: ** resolve parent ato line id for item.
3094: */
3105: v_src_point := p_t_bcol(v_src_point).link_to_line_id ;
3106:
3107:
3108: IF PG_DEBUG <> 0 THEN
3109: oe_debug_pub.add('populate_parent_ato: ' || 'prev point ' || to_char( v_prev_src_point ) || ' bcol ' || to_char( p_bcol_line_id ) , 3 );
3110: END IF;
3111:
3112:
3113: if( v_src_point is null or v_prev_src_point = p_bcol_line_id ) then
3200: -- The following initialize_assignment_set is used to initialize the global variable
3201:
3202: IF gMrpAssignmentSet is null THEN
3203: IF PG_DEBUG <> 0 THEN
3204: oe_debug_pub.add('query_sourcing_org: ' || 'Initializing the assignment set',5);
3205: END IF;
3206: initialize_assignment_set(x_return_status);
3207: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3208: IF PG_DEBUG <> 0 THEN
3205: END IF;
3206: initialize_assignment_set(x_return_status);
3207: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3208: IF PG_DEBUG <> 0 THEN
3209: oe_debug_pub.add('query_sourcing_org: ' || 'Error in initializing assignment set',5);
3210: END IF;
3211: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3212: end if;
3213: End IF;
3301: --- For procuring config project change.
3302: elsif (v_source_type = 3) then
3303: p_sourcing_rule_exists := FND_API.G_TRUE ;
3304: IF PG_DEBUG <> 0 THEN
3305: oe_debug_pub.add('query_sourcing_org: ' || 'Buy Sourcing rule exists...',1);
3306: END IF;
3307: end if ;
3308:
3309:
3307: end if ;
3308:
3309:
3310: IF PG_DEBUG <> 0 THEN
3311: oe_debug_pub.add('query_sourcing_org: ' || '****$$$$ IID ' || p_inventory_item_id || ' in org ' ||
3312: p_organization_id || ' is sourced from org ' || p_sourcing_org ||
3313: ' type ' || v_source_type || ' $$$$****' , 1 );
3314: END IF;
3315:
3315:
3316: EXCEPTION
3317: WHEN NO_DATA_FOUND THEN
3318: IF PG_DEBUG <> 0 THEN
3319: oe_debug_pub.add('query_sourcing_org: ' || ' came into no data when finding source ' || to_char(l_stmt_num ) , 1 );
3320: END IF;
3321: /* removed no sourcing flag as cascading of sourcing rules will
3322: ** be continued till no more sourcing rules can be cascaded
3323: */
3347:
3348:
3349: WHEN TOO_MANY_ROWS THEN
3350: IF PG_DEBUG <> 0 THEN
3351: oe_debug_pub.add('query_sourcing_org: ' || ' came into too_many when finding source ' || to_char(l_stmt_num) , 1 );
3352: END IF;
3353: select count(*)
3354: into v_sourcing_rule_count
3355: from mrp_sources_v msv
3387:
3388:
3389: WHEN OTHERS THEN
3390: IF PG_DEBUG <> 0 THEN
3391: oe_debug_pub.add('query_sourcing_org: ' || 'QUERY_SOURCING_ORG::others:: ' ||
3392: to_char(l_stmt_num) || '::' ||
3393: ' came into others when finding source ' , 1 );
3394:
3395: oe_debug_pub.add('query_sourcing_org: ' || ' SQLCODE ' || SQLCODE , 1 );
3391: oe_debug_pub.add('query_sourcing_org: ' || 'QUERY_SOURCING_ORG::others:: ' ||
3392: to_char(l_stmt_num) || '::' ||
3393: ' came into others when finding source ' , 1 );
3394:
3395: oe_debug_pub.add('query_sourcing_org: ' || ' SQLCODE ' || SQLCODE , 1 );
3396:
3397: oe_debug_pub.add('query_sourcing_org: ' || ' SQLERRM ' || SQLERRM , 1 );
3398:
3399: oe_debug_pub.add('query_sourcing_org: ' || ' came into others when finding source ' , 1 );
3393: ' came into others when finding source ' , 1 );
3394:
3395: oe_debug_pub.add('query_sourcing_org: ' || ' SQLCODE ' || SQLCODE , 1 );
3396:
3397: oe_debug_pub.add('query_sourcing_org: ' || ' SQLERRM ' || SQLERRM , 1 );
3398:
3399: oe_debug_pub.add('query_sourcing_org: ' || ' came into others when finding source ' , 1 );
3400: END IF;
3401:
3395: oe_debug_pub.add('query_sourcing_org: ' || ' SQLCODE ' || SQLCODE , 1 );
3396:
3397: oe_debug_pub.add('query_sourcing_org: ' || ' SQLERRM ' || SQLERRM , 1 );
3398:
3399: oe_debug_pub.add('query_sourcing_org: ' || ' came into others when finding source ' , 1 );
3400: END IF;
3401:
3402: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3403:
3437:
3438: IF( gMrpAssignmentSet is null )
3439: THEN
3440: IF PG_DEBUG <> 0 THEN
3441: oe_debug_pub.add('initialize_assignment_set: ' || '**$$ Default assignment set is null', 1);
3442: END IF;
3443:
3444: ELSE
3445: IF PG_DEBUG <> 0 THEN
3442: END IF;
3443:
3444: ELSE
3445: IF PG_DEBUG <> 0 THEN
3446: oe_debug_pub.add('initialize_assignment_set: ' || 'Default assignment set is '||to_char(gMrpAssignmentSet),2);
3447: END IF;
3448:
3449: l_stmt_num := 10 ;
3450:
3457:
3458: exception
3459: when no_data_found then
3460: IF PG_DEBUG <> 0 THEN
3461: oe_debug_pub.add('initialize_assignment_set: ' || 'The assignment set pointed by the
3462: profile MRP_DEFAULT_ASSIGNMENT_SET
3463: does not exist in the database ' ,1);
3464: END IF;
3465:
3470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3471: end ;
3472:
3473: IF PG_DEBUG <> 0 THEN
3474: oe_debug_pub.add('initialize_assignment_set: ' || 'Default assignment set name is '||
3475: assign_set_name ,2);
3476: END IF;
3477:
3478: END IF;
3482: when INVALID_MRP_ASSIGNMENT_SET then
3483: x_return_status := FND_API.G_RET_STS_ERROR;
3484:
3485: IF PG_DEBUG <> 0 THEN
3486: oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::INVALID ASSIGNMENT SET ::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3487: END IF;
3488:
3489:
3490: when FND_API.G_EXC_UNEXPECTED_ERROR then
3489:
3490: when FND_API.G_EXC_UNEXPECTED_ERROR then
3491: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3492: IF PG_DEBUG <> 0 THEN
3493: oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3494: END IF;
3495:
3496:
3497: when OTHERS then
3496:
3497: when OTHERS then
3498: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3499: IF PG_DEBUG <> 0 THEN
3500: oe_debug_pub.add('initialize_assignment_set: ' || 'INITIALIZE_ASSIGNMENT_SET::others::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3501: END IF;
3502:
3503:
3504: END initialize_assignment_set ;
3655: if( v_circular_sourcing )
3656: then
3657: x_exp_error_code := 66 ;
3658: IF PG_DEBUG <> 0 THEN
3659: oe_debug_pub.add('get_model_sourcing_org: ' || ' circular sourcing problem ' , 1 );
3660: END IF;
3661:
3662: --bugfix 2813271: Added message to show the user
3663: cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING'); -- bugfix 2813271
3667:
3668: p_sourcing_org := v_organization_id ;
3669:
3670: IF PG_DEBUG <> 0 THEN
3671: oe_debug_pub.add('get_model_sourcing_org: ' || 'sourcing org is ' || p_sourcing_org || ' lead time ' || to_char( p_transit_lead_time ) , 1 );
3672: END IF;
3673:
3674: exception
3675: when FND_API.G_EXC_ERROR then
3673:
3674: exception
3675: when FND_API.G_EXC_ERROR then
3676: IF PG_DEBUG <> 0 THEN
3677: oe_debug_pub.add('get_model_sourcing_org: ' || 'GET_MODEL_SOURCING_ORG::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3678: END IF;
3679: x_return_status := FND_API.G_RET_STS_ERROR;
3680:
3681: CTO_MSG_PUB.Count_And_Get(
3684: );
3685:
3686: when FND_API.G_EXC_UNEXPECTED_ERROR then
3687: IF PG_DEBUG <> 0 THEN
3688: oe_debug_pub.add('get_model_sourcing_org: ' || 'GET_MODEL_SOURCING_ORG::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3689: END IF;
3690: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3691:
3692: CTO_MSG_PUB.Count_And_Get(
3695: );
3696:
3697: when OTHERS then
3698: IF PG_DEBUG <> 0 THEN
3699: oe_debug_pub.add('get_model_sourcing_org: ' || 'GET_MODEL_SOURCING_ORG::others::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3700: END IF;
3701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3702:
3703: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3761: l_source_document_type_id number;
3762: BEGIN
3763:
3764: IF PG_DEBUG <> 0 THEN
3765: oe_debug_pub.add('get_source_document_id: ' || 'CTOUTILB: get_source_document_id: Fetching the source document type id', 1);
3766: END IF;
3767:
3768: select h.source_document_type_id
3769: into l_source_document_type_id
3772: and l.line_id = pLineId
3773: and rownum = 1;
3774:
3775: IF PG_DEBUG <> 0 THEN
3776: oe_debug_pub.add('get_source_document_id: ' || 'CTOUTILB: get_source_document_id: source document type id = '||l_source_document_type_id, 1);
3777: END IF;
3778:
3779: return (l_source_document_type_id);
3780:
3891: CTO_WIP_WORKFLOW_API_PK.cto_debug('check_rsv_qty', 'allowed_unreservation_qty = '||allowed_unreservation_qty);
3892: End if;
3893:
3894: IF PG_DEBUG <> 0 THEN
3895: oe_debug_pub.add('check_rsv_quantity: ' || 'check_rsv_qty: l_shipped_qty = '||l_shipped_qty ||
3896: ' l_reservation_qty = '||l_reservation_qty ||
3897: ' allowed_unreservation_qty = '||allowed_unreservation_qty, 2);
3898: END IF;
3899:
3912: EXCEPTION
3913:
3914: when others then
3915: IF PG_DEBUG <> 0 THEN
3916: oe_debug_pub.add ('check_rsv_quantity: ' || 'OTHERS excpn while checking for unreserved qty :' ||substr(sqlerrm,1,150));
3917: END IF;
3918: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3919:
3920: END CHECK_RSV_QUANTITY;
3952: and fdt.language = userenv('LANG');
3953:
3954: if l_blob_loc is null then
3955: x_return_status := fnd_api.g_ret_sts_error;
3956: oe_debug_pub.add('get_cto_item_attachment: ' || 'File attachment is null. Nothing to convert to clob... returning null', 1);
3957: return null;
3958: end if;
3959:
3960: l_lob_length := dbms_lob.getlength(l_blob_loc);
3998:
3999: when no_data_found then
4000: x_return_status := fnd_api.g_ret_sts_error;
4001: IF PG_DEBUG <> 0 THEN
4002: oe_debug_pub.add('get_cto_item_attachment: ' || 'no file attachment existsfor given item and org', 1);
4003: END IF;
4004: return null;
4005:
4006: when others then
4005:
4006: when others then
4007: x_return_status := fnd_api.g_ret_sts_unexp_error;
4008: IF PG_DEBUG <> 0 THEN
4009: oe_debug_pub.add('get_cto_item_attachment: ' || 'When others exception ..' || sqlerrm, 1);
4010: END IF;
4011: return null;
4012:
4013: END get_cto_item_attachment;
4041: x_return_status := FND_API.G_RET_STS_SUCCESS;
4042:
4043: l_stmt := 10;
4044: IF PG_DEBUG <> 0 THEN
4045: oe_debug_pub.add('create_attachment: ' || 'Entering Create_attachment...',1);
4046:
4047: oe_debug_pub.add('create_attachment: ' || 'Attachment desc = '||p_desc,1);
4048: END IF;
4049:
4043: l_stmt := 10;
4044: IF PG_DEBUG <> 0 THEN
4045: oe_debug_pub.add('create_attachment: ' || 'Entering Create_attachment...',1);
4046:
4047: oe_debug_pub.add('create_attachment: ' || 'Attachment desc = '||p_desc,1);
4048: END IF;
4049:
4050: -- The following FND API will create a document with the given Text information.
4051:
4095: ,x_attribute15 => null );
4096:
4097:
4098: IF PG_DEBUG <> 0 THEN
4099: oe_debug_pub.add('create_attachment: ' || 'The document is created with the following id....'||to_char(l_doc_id),1);
4100:
4101: oe_debug_pub.add('create_attachment: ' || 'Media id ...'||to_char(l_media_id),1);
4102: END IF;
4103:
4097:
4098: IF PG_DEBUG <> 0 THEN
4099: oe_debug_pub.add('create_attachment: ' || 'The document is created with the following id....'||to_char(l_doc_id),1);
4100:
4101: oe_debug_pub.add('create_attachment: ' || 'Media id ...'||to_char(l_media_id),1);
4102: END IF;
4103:
4104: -- The following insert is inserting into the media and text infor into fnd_documents_long_text
4105:
4188: EXCEPTION
4189:
4190: when FND_API.G_EXC_UNEXPECTED_ERROR then
4191: IF PG_DEBUG <> 0 THEN
4192: oe_debug_pub.add('create_attachment: ' || 'Create_attachment::unexp error::'||l_Stmt||'::'||sqlerrm,1);
4193: END IF;
4194: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4195:
4196: when FND_API.G_EXC_ERROR then
4194: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4195:
4196: when FND_API.G_EXC_ERROR then
4197: IF PG_DEBUG <> 0 THEN
4198: oe_debug_pub.add('create_attachment: ' || 'Create_attachment::exp error::'||l_Stmt||'::'||sqlerrm,1);
4199: END IF;
4200: x_return_status := FND_API.G_RET_STS_ERROR;
4201:
4202: when others then
4200: x_return_status := FND_API.G_RET_STS_ERROR;
4201:
4202: when others then
4203: IF PG_DEBUG <> 0 THEN
4204: oe_debug_pub.add('create_attachment: ' || 'Create_attachment::OTHERS error::'||l_Stmt||'::'||sqlerrm,1);
4205: END IF;
4206: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4207:
4208:
4278:
4279: begin
4280:
4281: IF PG_DEBUG <> 0 THEN
4282: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'Entering GENERATE_BOM_ATTACH_TEXT',1);
4283:
4284: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'line_id =>'||p_line_id,1);
4285: END IF;
4286:
4280:
4281: IF PG_DEBUG <> 0 THEN
4282: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'Entering GENERATE_BOM_ATTACH_TEXT',1);
4283:
4284: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'line_id =>'||p_line_id,1);
4285: END IF;
4286:
4287:
4288: x_return_status := FND_API.G_RET_STS_SUCCESS;
4326:
4327: l_stmt := 20;
4328:
4329: IF PG_DEBUG <> 0 THEN
4330: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'before starting the loop (inorder to get bcol lines)',1);
4331: END IF;
4332: for comp_cur in components
4333:
4334: loop
4336: if l_loop_switch then
4337: l_model_ord_qty := comp_cur.ordered_qty;
4338: l_loop_switch := false;
4339: IF PG_DEBUG <> 0 THEN
4340: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'in IF block of loop_siwtch,model_ord_qty=>l_model_ord_qty',1);
4341: END IF;
4342: end if;
4343:
4344: --for text attachment
4351: --x_text := concat(x_text,l_hdr_text);
4352: l_test_text := concat(l_test_text,l_hdr_text);
4353: l_line_count := 0;
4354: IF PG_DEBUG <> 0 THEN
4355: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'should start on new line as l_line_count = 15',1);
4356: END IF;
4357: elsif nvl(l_line_count,0) = 0 then
4358: --
4359: -- bug 6717456
4361: --
4362: --x_text := concat(x_text,l_hdr_text);
4363: l_test_text := concat(l_test_text,l_hdr_text);
4364: IF PG_DEBUG <> 0 THEN
4365: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'adding header text as l_line_count = 0',1);
4366: END IF;
4367: end if;
4368:
4369:
4416: x_text := l_test_text;
4417:
4418: l_line_count := nvl(l_line_count,0) + 1;
4419: IF PG_DEBUG <> 0 THEN
4420: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'value of l_line_count'||l_line_count,4);
4421: END IF;
4422:
4423:
4424: end loop;
4423:
4424: end loop;
4425:
4426: IF PG_DEBUG <> 0 THEN
4427: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'End of LOOP in generate BOM attachment',1);
4428: END IF;
4429:
4430:
4431: EXCEPTION
4431: EXCEPTION
4432:
4433: when FND_API.G_EXC_UNEXPECTED_ERROR then
4434: IF PG_DEBUG <> 0 THEN
4435: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::unexp error::'||l_Stmt||sqlerrm,1);
4436: END IF;
4437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4438:
4439: when FND_API.G_EXC_ERROR then
4437: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4438:
4439: when FND_API.G_EXC_ERROR then
4440: IF PG_DEBUG <> 0 THEN
4441: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::exp error::'||l_Stmt||sqlerrm,1);
4442: END IF;
4443: x_return_status := FND_API.G_RET_STS_ERROR;
4444:
4445: --
4447: -- ntungare
4448: --
4449: when text_too_long THEN
4450: IF PG_DEBUG <> 0 THEN
4451: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::text too long exception::'||l_Stmt||sqlerrm,1);
4452: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::text too long exception:: continuing..',1);
4453: END IF;
4454:
4455: when others then
4448: --
4449: when text_too_long THEN
4450: IF PG_DEBUG <> 0 THEN
4451: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::text too long exception::'||l_Stmt||sqlerrm,1);
4452: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::text too long exception:: continuing..',1);
4453: END IF;
4454:
4455: when others then
4456: IF PG_DEBUG <> 0 THEN
4453: END IF;
4454:
4455: when others then
4456: IF PG_DEBUG <> 0 THEN
4457: oe_debug_pub.add('GENERATE_BOM_ATTACH_TEXT: ' || 'GENERATE_BOM_ATTACH_TEXT::OTHERS error::'||l_Stmt||sqlerrm,1);
4458: END IF;
4459: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4460:
4461: END GENERATE_BOM_ATTACH_TEXT;
4564:
4565: BEGIN
4566:
4567: IF PG_DEBUG <> 0 THEN
4568: oe_debug_pub.add('chk_all_rsv_details: ' || 'Entered CTO_UTILITY_PK.chk_all_rsv_details',1);
4569: END IF;
4570: x_return_status := FND_API.G_RET_STS_SUCCESS;
4571:
4572:
4597: IF (p_rsv_details.count>0) THEN
4598: l_index := p_rsv_details.FIRST;
4599: LOOP
4600: IF PG_DEBUG <> 0 THEN
4601: oe_debug_pub.add('chk_all_rsv_details: ' || 'rsv id '||p_rsv_details(l_index).l_reservation_id,5);
4602:
4603: oe_debug_pub.add('chk_all_rsv_details: ' || 'rsv qty '||p_rsv_details(l_index).l_reservation_quantity,5);
4604:
4605: oe_debug_pub.add('chk_all_rsv_details: ' || 'suppy sourc '||p_rsv_details(l_index).l_supply_source_type_id,5);
4599: LOOP
4600: IF PG_DEBUG <> 0 THEN
4601: oe_debug_pub.add('chk_all_rsv_details: ' || 'rsv id '||p_rsv_details(l_index).l_reservation_id,5);
4602:
4603: oe_debug_pub.add('chk_all_rsv_details: ' || 'rsv qty '||p_rsv_details(l_index).l_reservation_quantity,5);
4604:
4605: oe_debug_pub.add('chk_all_rsv_details: ' || 'suppy sourc '||p_rsv_details(l_index).l_supply_source_type_id,5);
4606: END IF;
4607:
4601: oe_debug_pub.add('chk_all_rsv_details: ' || 'rsv id '||p_rsv_details(l_index).l_reservation_id,5);
4602:
4603: oe_debug_pub.add('chk_all_rsv_details: ' || 'rsv qty '||p_rsv_details(l_index).l_reservation_quantity,5);
4604:
4605: oe_debug_pub.add('chk_all_rsv_details: ' || 'suppy sourc '||p_rsv_details(l_index).l_supply_source_type_id,5);
4606: END IF;
4607:
4608: EXIT WHEN l_index = p_rsv_details.LAST;
4609: l_index := p_rsv_details.NEXT(l_index);
4623: EXCEPTION
4624: when others then
4625: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4626: IF PG_DEBUG <> 0 THEN
4627: oe_debug_pub.add ('chk_all_rsv_details: ' || 'unexpected error in called program chk_all_rsv_details'|| l_stmt||sqlerrm , 1);
4628: END IF;
4629: if fnd_msg_pub.check_msg_level
4630: (fnd_msg_pub.g_msg_lvl_unexp_error)
4631: then
4718: , null
4719: ) ;
4720:
4721: IF PG_DEBUG <> 0 THEN
4722: oe_debug_pub.add('isModelMLMO: ' || ' after get model source status ' || x_return_status, 4 );
4723: END IF;
4724:
4725:
4726: IF x_return_status = FND_API.G_RET_STS_ERROR
4911:
4912: rowcount := SQL%ROWCOUNT;
4913:
4914: IF PG_DEBUG <> 0 THEN
4915: oe_debug_pub.add ('Row Count : ' || rowcount, 2);
4916: END IF;
4917:
4918: If PG_DEBUG <> 0 Then
4919: cto_wip_workflow_api_pk.cto_debug('create_isp_bom', 'Row Count:'||rowcount);
4932: and nvl(msi.auto_created_config_flag, 'N') = 'Y');
4933:
4934: rowcount := SQL%ROWCOUNT;
4935: IF PG_DEBUG <> 0 THEN
4936: oe_debug_pub.add ('Deleted Row Count : ' || rowcount, 2);
4937: END IF;
4938:
4939: If PG_DEBUG <> 0 Then
4940: cto_wip_workflow_api_pk.cto_debug('create_isp_bom', 'Deleted Row Count:'||rowcount);
5112: and C.organization_id = p_organization_id
5113: and C.cost_type_id = p_src_cost_type_id;
5114:
5115: IF PG_DEBUG <> 0 THEN
5116: oe_debug_pub.add('copy_cost: ' || 'after insert:CST_ITEM_COSTS',2);
5117:
5118: oe_debug_pub.add('copy_cost: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
5119: END IF;
5120:
5114:
5115: IF PG_DEBUG <> 0 THEN
5116: oe_debug_pub.add('copy_cost: ' || 'after insert:CST_ITEM_COSTS',2);
5117:
5118: oe_debug_pub.add('copy_cost: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
5119: END IF;
5120:
5121: /*------ ----------------------------------------------+
5122: Insert rows into the cst_item_cost_details table
5219: and C.organization_id = p_organization_id
5220: and C.cost_type_id = p_src_cost_type_id ;
5221:
5222: IF PG_DEBUG <> 0 THEN
5223: oe_debug_pub.add('copy_cost: ' || 'after insert:cst_item_cost_details',2);
5224:
5225: oe_debug_pub.add('copy_cost: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
5226: END IF;
5227:
5221:
5222: IF PG_DEBUG <> 0 THEN
5223: oe_debug_pub.add('copy_cost: ' || 'after insert:cst_item_cost_details',2);
5224:
5225: oe_debug_pub.add('copy_cost: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
5226: END IF;
5227:
5228:
5229: exception
5234: xMessageName := 'CTO_CALC_COST_ROLLUP_ERROR';
5235: */
5236:
5237: IF PG_DEBUG <> 0 THEN
5238: oe_debug_pub.add('copy_cost: ' || 'copy_ctocost_to_frozen no data found ',2);
5239: END IF;
5240:
5241: when OTHERS THEN
5242: /*
5246: xMessageName := 'CTO_CALC_COST_ROLLUP_ERROR';
5247: */
5248:
5249: IF PG_DEBUG <> 0 THEN
5250: oe_debug_pub.add('copy_cost: ' || 'copy_ctocost_to_frozen others ',2);
5251: END IF;
5252:
5253:
5254:
5294: x_return_status := FND_API.G_RET_STS_SUCCESS;
5295: x_can_create_supply := 'Y';
5296:
5297: IF PG_DEBUG <> 0 THEN
5298: oe_debug_pub.add('check_cto_can_create_supply : ' ||'ENTERED',1);
5299: END IF;
5300:
5301: --call custom API
5302: lStmtNum := 10;
5303: P_custom_in_params_rec.CONFIG_ITEM_ID := P_config_item_id;
5304: P_custom_in_params_rec.Org_id := P_org_id ;
5305:
5306: IF PG_DEBUG <> 0 THEN
5307: oe_debug_pub.add('check_cto_can_create_supply : ' ||'P_config_item_id'
5308: ||P_config_item_id,1);
5309: oe_debug_pub.add('check_cto_can_create_supply : ' ||'P_org_id'
5310: ||P_org_id,1);
5311: END IF;
5305:
5306: IF PG_DEBUG <> 0 THEN
5307: oe_debug_pub.add('check_cto_can_create_supply : ' ||'P_config_item_id'
5308: ||P_config_item_id,1);
5309: oe_debug_pub.add('check_cto_can_create_supply : ' ||'P_org_id'
5310: ||P_org_id,1);
5311: END IF;
5312:
5313:
5328: X_custom_out_params_rec.can_cto_create_supply = 'Y' THEN
5329:
5330:
5331: IF PG_DEBUG <> 0 THEN
5332: oe_debug_pub.add('check_cto_can_create_supply : '
5333: ||'success from custom API',5);
5334: END IF;
5335:
5336:
5350:
5351: lStmtNum:=50;
5352: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
5353: IF PG_DEBUG <> 0 THEN
5354: oe_debug_pub.add('check_cto_can_create_supply : '
5355: ||'success from query sourcing org',5);
5356: END IF;
5357:
5358: -- 100% trasfer rule = 1 and multiple sources = 66
5361: -- Now CTO can create supply for 1. So removed it in if condition.
5362: IF p_source_type = 66 THEN
5363:
5364: IF PG_DEBUG <> 0 THEN
5365: oe_debug_pub.add('check_cto_can_create_supply : '
5366: ||'P_source_type'||p_source_type,5);
5367: END IF;
5368: x_can_create_supply := 'N';
5369: x_message := 'MULTIPLE SOURCES PRESENT';
5377: IF INV_GMI_RSV_BRANCH.Process_Branch
5378: (p_organization_id =>P_org_id) THEN
5379:
5380: IF PG_DEBUG <> 0 THEN
5381: oe_debug_pub.add('check_cto_can_create_supply : '
5382: ||'MAKE in a process org not allowed',1);
5383: END IF;
5384:
5385: x_can_create_supply := 'N';
5391:
5392:
5393: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5394: IF PG_DEBUG <> 0 THEN
5395: oe_debug_pub.add('check_cto_can_create_supply : ' || 'Unexpected Error in the sourcing rule.',1);
5396: END IF;
5397: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5398:
5399: END IF;
5401: ELSIF X_return_status = FND_API.G_RET_STS_SUCCESS --cutsom API's out param
5402: and X_custom_out_params_rec.can_cto_create_supply = 'N' THEN
5403:
5404: IF PG_DEBUG <> 0 THEN
5405: oe_debug_pub.add('check_cto_can_create_supply : '
5406: ||'from custom api:can_create_supply is N',5);
5407: END IF;
5408:
5409: x_can_create_supply := 'N';
5420: EXCEPTION
5421:
5422: WHEN fnd_api.g_exc_error THEN
5423: IF PG_DEBUG <> 0 THEN
5424: oe_debug_pub.add('check_cto_can_create_supply: ' || 'Exception in stmt num: '
5425: || to_char(lStmtNum), 1);
5426: END IF;
5427: x_return_status := FND_API.G_RET_STS_ERROR;
5428: -- Get message count and data
5431: , p_msg_data => x_msg_data
5432: );
5433: WHEN fnd_api.g_exc_unexpected_error THEN
5434: IF PG_DEBUG <> 0 THEN
5435: oe_debug_pub.add('check_cto_can_create_supply: ' || ' Unexpected Exception in stmt num: '
5436: || to_char(lStmtNum), 1);
5437: END IF;
5438: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5439: -- Get message count and data
5443: );
5444: WHEN OTHERS then
5445: IF PG_DEBUG <> 0 THEN
5446:
5447: oe_debug_pub.add('check_cto_can_create_supply: ' || 'Others Exception in stmt num: '
5448: || to_char(lStmtNum), 1);
5449: oe_debug_pub.add('check_cto_can_create_supply: ' || 'errormsg='||sqlerrm, 1);
5450: END IF;
5451: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5445: IF PG_DEBUG <> 0 THEN
5446:
5447: oe_debug_pub.add('check_cto_can_create_supply: ' || 'Others Exception in stmt num: '
5448: || to_char(lStmtNum), 1);
5449: oe_debug_pub.add('check_cto_can_create_supply: ' || 'errormsg='||sqlerrm, 1);
5450: END IF;
5451: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5452: -- Get message count and data
5453: cto_msg_pub.count_and_get
5486: begin
5487:
5488:
5489:
5490: oe_debug_pub.add('CTOUTILB.split_line: entered split_line ' , 1);
5491:
5492:
5493:
5494:
5505:
5506:
5507:
5508: IF PG_DEBUG <> 0 THEN
5509: oe_debug_pub.add('CTOUTILB.split_line: ' || 'Going to fetch Validation Org ' ,2);
5510: END IF;
5511:
5512:
5513: select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
5530: /* reschedule should be always No for Split Line
5531: */
5532:
5533:
5534: oe_debug_pub.add('CTOUTILB.split_line: opening config_update cursor ' , 1);
5535:
5536:
5537:
5538: lstmtnum := 20 ;
5547:
5548: exit when config_update%notfound ;
5549:
5550:
5551: oe_debug_pub.add('CTOUTILB.split_line: line ' || v_config.line_id , 1);
5552: oe_debug_pub.add('CTOUTILB.split_line: split line ' ||
5553: v_config.split_from_line_id , 1);
5554:
5555:
5548: exit when config_update%notfound ;
5549:
5550:
5551: oe_debug_pub.add('CTOUTILB.split_line: line ' || v_config.line_id , 1);
5552: oe_debug_pub.add('CTOUTILB.split_line: split line ' ||
5553: v_config.split_from_line_id , 1);
5554:
5555:
5556: update bom_cto_order_lines set config_item_id = ( select bcol1.config_item_id
5557: from bom_cto_order_lines bcol1
5558: where bcol1.line_id = v_config.split_from_line_id )
5559: where line_id = v_config.line_id ;
5560:
5561: oe_debug_pub.add('CTOUTILB.split_line: update cnt ' || SQL%ROWCOUNT , 1);
5562:
5563:
5564: end loop ;
5565:
5568: exception
5569: when others then
5570: IF PG_DEBUG <> 0 THEN
5571:
5572: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'Others Exception in stmt num : '
5573: || to_char(lStmtNum), 1);
5574: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'errormsg='||sqlerrm, 1);
5575:
5576: END IF;
5570: IF PG_DEBUG <> 0 THEN
5571:
5572: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'Others Exception in stmt num : '
5573: || to_char(lStmtNum), 1);
5574: oe_debug_pub.add('CTO_UTILITY_PK.split_line: ' || 'errormsg='||sqlerrm, 1);
5575:
5576: END IF;
5577: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5578:
5588: )
5589: is
5590: begin
5591:
5592: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_split: entered ' || p_ato_line_id , 1);
5593:
5594: update bom_cto_order_lines bcol
5595: set ordered_quantity = ( select ordered_quantity
5596: from oe_order_lines_all
5597: where ato_line_id = bcol.ato_line_id
5598: and line_id = bcol.line_id )
5599: where ato_line_id = p_ato_line_id ;
5600:
5601: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_split: upd cnt ' || SQL%ROWCOUNT , 1);
5602:
5603: end adjust_bcol_for_split ;
5604:
5605:
5611: )
5612: is
5613: begin
5614:
5615: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_warehouse: entered ' || p_ato_line_id , 1);
5616:
5617: update bom_cto_order_lines bcol
5618: set ship_from_org_id = ( select ship_from_org_id
5619: from oe_order_lines_all
5620: where ato_line_id = bcol.ato_line_id
5621: and line_id = bcol.line_id )
5622: where ato_line_id = p_ato_line_id ;
5623:
5624: oe_debug_pub.add('CTO_UTILITY_PK.adjust_bcol_for_warehouse: upd cnt ' || SQL%ROWCOUNT , 1);
5625:
5626:
5627:
5628: end adjust_bcol_for_warehouse;
5639:
5640: BEGIN
5641:
5642: IF PG_DEBUG <> 0 THEN
5643: oe_debug_pub.add('Reservation_Exists: ' || 'Entering into Reservation_exists procedure....',1);
5644: END IF;
5645: -- Check if flow schedule exists . If not check some inv/work order Reservation
5646: -- exists. If both of them does'nt exists then return false. Other wise return true.
5647:
5647:
5648: IF CTO_WORKFLOW.flow_sch_exists(pconfigLineId) <> TRUE THEN
5649:
5650: IF PG_DEBUG <> 0 THEN
5651: oe_debug_pub.add('Reservation_Exists: ' || 'flow Reservation not exists..',5);
5652:
5653: oe_debug_pub.add('Reservation_Exists: ' || 'The line_id passed for reservation = '||to_char(pconfiglineid),5);
5654: END IF;
5655:
5649:
5650: IF PG_DEBUG <> 0 THEN
5651: oe_debug_pub.add('Reservation_Exists: ' || 'flow Reservation not exists..',5);
5652:
5653: oe_debug_pub.add('Reservation_Exists: ' || 'The line_id passed for reservation = '||to_char(pconfiglineid),5);
5654: END IF;
5655:
5656: SELECT reservation_id
5657: INTO l_reservation_id
5680: INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
5681: and mr.reservation_quantity > 0
5682: and rownum = 1;
5683: IF PG_DEBUG <> 0 THEN
5684: oe_debug_pub.add('Reservation_Exists: ' || 'Work order/Inv reservation Exists..',5);
5685: END IF;
5686: END IF;
5687:
5688: x_result := TRUE;
5690:
5691: EXCEPTION
5692: WHEN no_data_found THEN
5693: IF PG_DEBUG <> 0 THEN
5694: oe_debug_pub.add('Reservation_Exists: ' || 'Coming out of reservation_exists procedure with FALSE...',2);
5695: END IF;
5696: x_return_status := FND_API.G_RET_STS_SUCCESS;
5697: x_result := FALSE;
5698:
5697: x_result := FALSE;
5698:
5699: WHEN others THEN
5700: IF PG_DEBUG <> 0 THEN
5701: oe_debug_pub.add('Reservation_Exists: ' || 'OTHERS excpn occured in Reservation_Exists procedure..',2);
5702:
5703: oe_debug_pub.add('Reservation_Exists: ' || 'Error message is : '||sqlerrm,1);
5704: END IF;
5705: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5699: WHEN others THEN
5700: IF PG_DEBUG <> 0 THEN
5701: oe_debug_pub.add('Reservation_Exists: ' || 'OTHERS excpn occured in Reservation_Exists procedure..',2);
5702:
5703: oe_debug_pub.add('Reservation_Exists: ' || 'Error message is : '||sqlerrm,1);
5704: END IF;
5705: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5706: x_result := FALSE;
5707:
5718: is
5719: lStmtNumber number;
5720: begin
5721:
5722: oe_debug_pub.add( ' entered copy bcol_gt to bcol' , 1) ;
5723:
5724: insert into bom_cto_order_lines (
5725: LINE_ID
5726: ,HEADER_ID
5789: ,CONFIG_CREATION
5790: from bom_cto_order_lines_gt
5791: where ato_line_id = p_ato_line_id ;
5792:
5793: oe_debug_pub.add( ' copied bcol_gt to bcol ' || SQL%ROWCOUNT , 1) ;
5794:
5795:
5796:
5797:
5807: is
5808: lStmtNumber number;
5809: begin
5810:
5811: oe_debug_pub.add( ' entered copy bcol to bcol_gt ' , 1) ;
5812: --bugfix#3756670
5813: delete from bom_cto_order_lines_gt
5814: where ato_line_id = p_ato_line_id ;
5815:
5885: where ato_line_id = p_ato_line_id ;
5886:
5887:
5888:
5889: oe_debug_pub.add( ' copied bcol to bcol_gt ' || SQL%ROWCOUNT , 1) ;
5890:
5891:
5892: end copy_bcol_bcolgt ;
5893:
5940:
5941:
5942: litem_key := to_char(p_line_id)||to_char(sysdate,'mmddyyhhmiss' );
5943:
5944: oe_debug_pub.add( 'SEND_NOTIFICATION item_key ' || litem_key , 1 ) ;
5945: g_error_seq := g_error_seq + 1 ;
5946: oe_debug_pub.add( 'error_seq is ' || g_error_seq , 1 ) ;
5947:
5948: luser_key := litem_key;
5942: litem_key := to_char(p_line_id)||to_char(sysdate,'mmddyyhhmiss' );
5943:
5944: oe_debug_pub.add( 'SEND_NOTIFICATION item_key ' || litem_key , 1 ) ;
5945: g_error_seq := g_error_seq + 1 ;
5946: oe_debug_pub.add( 'error_seq is ' || g_error_seq , 1 ) ;
5947:
5948: luser_key := litem_key;
5949:
5950: lplanner_code := P_NOTIFY_USER ;
6045:
6046: lstmt_num := 50 ;
6047:
6048: if( P_PROCESS = 'NOTIFY_OID_IC' ) then
6049: oe_debug_pub.add( ' going to create process 1 ' || P_PROCESS ) ;
6050:
6051: litem_key := litem_key || to_char( g_error_seq ) || '1111' ;
6052:
6053: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6049: oe_debug_pub.add( ' going to create process 1 ' || P_PROCESS ) ;
6050:
6051: litem_key := litem_key || to_char( g_error_seq ) || '1111' ;
6052:
6053: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6054:
6055: wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OID_IC');
6056:
6057: elsif( P_PROCESS = 'NOTIFY_OID_INC' ) then
6057: elsif( P_PROCESS = 'NOTIFY_OID_INC' ) then
6058:
6059: litem_key := litem_key || to_char( g_error_seq ) || '2222' ;
6060:
6061: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6062:
6063: oe_debug_pub.add( ' going to create process 2 ' || P_PROCESS ) ;
6064: wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OID_INC');
6065:
6059: litem_key := litem_key || to_char( g_error_seq ) || '2222' ;
6060:
6061: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6062:
6063: oe_debug_pub.add( ' going to create process 2 ' || P_PROCESS ) ;
6064: wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OID_INC');
6065:
6066: elsif( P_PROCESS = 'NOTIFY_OEE_INC' ) then
6067:
6066: elsif( P_PROCESS = 'NOTIFY_OEE_INC' ) then
6067:
6068: litem_key := litem_key || to_char( g_error_seq ) || '3333' ;
6069:
6070: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6071:
6072:
6073: oe_debug_pub.add( ' going to create process 3 ' || P_PROCESS ) ;
6074: wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OEE_INC');
6069:
6070: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6071:
6072:
6073: oe_debug_pub.add( ' going to create process 3 ' || P_PROCESS ) ;
6074: wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OEE_INC');
6075:
6076: elsif( P_PROCESS = 'NOTIFY_OEE_IC' ) then
6077:
6076: elsif( P_PROCESS = 'NOTIFY_OEE_IC' ) then
6077:
6078: litem_key := litem_key || to_char(g_error_seq ) || '4444' ;
6079:
6080: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6081:
6082:
6083:
6084: oe_debug_pub.add( ' going to create process 4 ' || P_PROCESS ) ;
6080: oe_debug_pub.add( 'SEND_NOTIFICATION modified item_key ' || litem_key , 1 ) ;
6081:
6082:
6083:
6084: oe_debug_pub.add( ' going to create process 4 ' || P_PROCESS ) ;
6085: wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OEE_IC');
6086:
6087: end if;
6088:
6098: -- Get the default adminstrator value from Workflow Attributes.
6099: lplanner_code := wf_engine.getItemAttrText(ItemType => 'CTOEXCP',
6100: ItemKey => litem_key,
6101: aname => 'WF_ADMINISTRATOR');
6102: oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
6103:
6104: ELSE
6105:
6106: oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
6102: oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
6103:
6104: ELSE
6105:
6106: oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
6107:
6108: END IF;
6109:
6110:
6139: lstmt_num := 130 ;
6140: wf_engine.StartProcess(itemtype=>'CTOEXCP',ItemKey=>litem_key);
6141:
6142:
6143: oe_debug_pub.add( ' done till stmt ' || lstmt_num ) ;
6144:
6145:
6146:
6147: exception
6146:
6147: exception
6148: when others then
6149:
6150: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
6151: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
6152: oe_debug_pub.add( ' exception in others ' || SQLERRM ) ;
6153:
6154:
6147: exception
6148: when others then
6149:
6150: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
6151: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
6152: oe_debug_pub.add( ' exception in others ' || SQLERRM ) ;
6153:
6154:
6155: end send_notification ;
6148: when others then
6149:
6150: oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
6151: oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
6152: oe_debug_pub.add( ' exception in others ' || SQLERRM ) ;
6153:
6154:
6155: end send_notification ;
6156:
6181:
6182:
6183: begin
6184:
6185: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** BEGIN AUTONOMOUS TRANSACTION **********' , 1 );
6186:
6187:
6188:
6189: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6185: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** BEGIN AUTONOMOUS TRANSACTION **********' , 1 );
6186:
6187:
6188:
6189: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6190:
6191:
6192: if ( nvl( p_msg_count , 0 ) = 0 ) then
6193: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: nothing to notify' , 1 );
6189: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: msg count ' || P_MSG_COUNT , 1 );
6190:
6191:
6192: if ( nvl( p_msg_count , 0 ) = 0 ) then
6193: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: nothing to notify' , 1 );
6194: return ;
6195: end if;
6196:
6197:
6202: l_msg_data := substr(fnd_msg_pub.get(
6203: p_msg_index => l_index,
6204: p_encoded => FND_API.G_FALSE),1,2000);
6205:
6206: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_error: '||substr(l_msg_data,1,250));
6207:
6208: -- Fixed bug 5639511
6209: -- Added Substr fucntion to avoid buffer overflow error
6210:
6215: if( length (v_error_message ) >= 2000 ) then
6216:
6217: v_error_message := substr(v_error_message, 1 , 2000) ;
6218:
6219: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_error: truncated v_error_message to 4000 chars ' );
6220: exit; -- Added exit statement for bug fix 5639511
6221: end if;
6222:
6223:
6223:
6224: END LOOP;
6225:
6226:
6227: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: error_message : ' || v_error_message );
6228:
6229: g_t_expected_error_info(1).NOTIFY_USER := P_NOTIFY_USER ;
6230: g_t_expected_error_info(1).SALES_ORDER_NUM := P_SALES_ORDER_NUM ;
6231: g_t_expected_error_info(1).TOP_MODEL_NAME := p_top_model_name ;
6251: g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
6252: */
6253:
6254:
6255: oe_debug_pub.add( 'PROCESS: ' || g_t_expected_error_info(1).PROCESS , 1 ) ;
6256: oe_debug_pub.add( 'LINE_ID: ' || g_t_expected_error_info(1).LINE_ID , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6252: */
6253:
6254:
6255: oe_debug_pub.add( 'PROCESS: ' || g_t_expected_error_info(1).PROCESS , 1 ) ;
6256: oe_debug_pub.add( 'LINE_ID: ' || g_t_expected_error_info(1).LINE_ID , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6253:
6254:
6255: oe_debug_pub.add( 'PROCESS: ' || g_t_expected_error_info(1).PROCESS , 1 ) ;
6256: oe_debug_pub.add( 'LINE_ID: ' || g_t_expected_error_info(1).LINE_ID , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6254:
6255: oe_debug_pub.add( 'PROCESS: ' || g_t_expected_error_info(1).PROCESS , 1 ) ;
6256: oe_debug_pub.add( 'LINE_ID: ' || g_t_expected_error_info(1).LINE_ID , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6255: oe_debug_pub.add( 'PROCESS: ' || g_t_expected_error_info(1).PROCESS , 1 ) ;
6256: oe_debug_pub.add( 'LINE_ID: ' || g_t_expected_error_info(1).LINE_ID , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6256: oe_debug_pub.add( 'LINE_ID: ' || g_t_expected_error_info(1).LINE_ID , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6257: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || g_t_expected_error_info(1).SALES_ORDER_NUM ,1 );
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6258: oe_debug_pub.add( 'ERROR_MESSAGE: ' || g_t_expected_error_info(1).ERROR_MESSAGE , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6259: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || g_t_expected_error_info(1).TOP_MODEL_NAME , 1 ) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6260: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).TOP_MODEL_LINE_NUM , 1 ) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6261: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || g_t_expected_error_info(1).TOP_CONFIG_NAME , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6262: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || g_t_expected_error_info(1).TOP_CONFIG_LINE_NUM , 1) ;
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6270:
6263: oe_debug_pub.add( 'PROBLEM_MODEL: ' || g_t_expected_error_info(1).PROBLEM_MODEL , 1 ) ;
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6270:
6271:
6264: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || g_t_expected_error_info(1).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6270:
6271:
6272: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done expected errors ' , 1 );
6265: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || g_t_expected_error_info(1).PROBLEM_CONFIG , 1) ;
6266: oe_debug_pub.add( 'ERROR_ORG: ' || g_t_expected_error_info(1).ERROR_ORG , 1 ) ;
6267: oe_debug_pub.add( 'ERROR_ORG_ID: ' || g_t_expected_error_info(1).ERROR_ORG_ID , 1) ;
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6270:
6271:
6272: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done expected errors ' , 1 );
6273:
6268: oe_debug_pub.add( 'NOTIFY_USER: ' || g_t_expected_error_info(1).NOTIFY_USER, 1) ;
6269: oe_debug_pub.add( 'REQUEST_ID: ' || g_t_expected_error_info(1).REQUEST_ID , 1 ) ;
6270:
6271:
6272: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done expected errors ' , 1 );
6273:
6274:
6275:
6276:
6273:
6274:
6275:
6276:
6277: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: going to call send notification ' , 1 );
6278:
6279: send_notification (
6280: P_PROCESS => g_t_expected_error_info(1).process
6281: ,P_LINE_ID => g_t_expected_error_info(1).line_id
6295:
6296: commit ; /* COMMIT FOR AUTONOMOUS TRANSACTION */
6297:
6298:
6299: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done send notification ' , 1 );
6300:
6301: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** END AUTONOMOUS TRANSACTION FOR NOTIFY_EXPECTED_ERRORS **********' , 1 );
6302: end notify_expected_errors ;
6303:
6297:
6298:
6299: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: done send notification ' , 1 );
6300:
6301: oe_debug_pub.add('CTO_UTILITY_PK.notify_expected_errors: ******** END AUTONOMOUS TRANSACTION FOR NOTIFY_EXPECTED_ERRORS **********' , 1 );
6302: end notify_expected_errors ;
6303:
6304:
6305: PROCEDURE APPLY_CREATE_CONFIG_HOLD( p_line_id in number
6316:
6317: BEGIN
6318:
6319: IF PG_DEBUG <> 0 THEN
6320: oe_debug_pub.add('******************APPLY_CREATE_CONFIG_HOLD:BEGIN APPLY AUTONOMOUS TRANSACTION **************** ' ,1);
6321: END IF;
6322:
6323:
6324:
6323:
6324:
6325:
6326: IF PG_DEBUG <> 0 THEN
6327: oe_debug_pub.add('APPLY_CREATE_CONFIG_HOLD:Calling OM api to apply create config activity hold.' ,1);
6328: END IF;
6329:
6330: l_hold_source_rec.hold_entity_code := 'O';
6331: l_hold_source_rec.hold_id := 60 ; /* Change hold id 1062 to 60 after getting script from gayatri */
6341: , x_msg_data => x_msg_data);
6342:
6343: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
6344: IF PG_DEBUG <> 0 THEN
6345: oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with expected error.' ,1);
6346: END IF;
6347: raise FND_API.G_EXC_ERROR;
6348:
6349: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6347: raise FND_API.G_EXC_ERROR;
6348:
6349: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6350: IF PG_DEBUG <> 0 THEN
6351: oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with unexpected error.' ,1);
6352: END IF;
6353: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6354: END IF;
6355:
6356: commit ; /* COMMIT FOR AUTONOMOUS TRANSACTION */
6357:
6358:
6359: IF PG_DEBUG <> 0 THEN
6360: oe_debug_pub.add('******************APPLY_CREATE_CONFIG_HOLD:END APPLY AUTONOMOUS TRANSACTION **************** ' ,1);
6361: END IF;
6362:
6363:
6364: END APPLY_CREATE_CONFIG_HOLD ;
6369: is
6370: PRAGMA AUTONOMOUS_TRANSACTION ;
6371: begin
6372:
6373: oe_debug_pub.add('SEND_OID_NOTIFICATION : ******** BEGIN AUTONOMOUS TRANSACTION FOR SEND_OID_NOTIFICATION **********' , 1 );
6374:
6375:
6376: if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count > 0 ) then
6377:
6377:
6378:
6379:
6380:
6381: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count , 1 ) ;
6382:
6383: for i in 1..CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count
6384: loop
6385: oe_debug_pub.add( 'PROCESS: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROCESS , 1 ) ;
6381: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count , 1 ) ;
6382:
6383: for i in 1..CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count
6384: loop
6385: oe_debug_pub.add( 'PROCESS: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROCESS , 1 ) ;
6386: oe_debug_pub.add( 'LINE_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).LINE_ID , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6382:
6383: for i in 1..CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count
6384: loop
6385: oe_debug_pub.add( 'PROCESS: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROCESS , 1 ) ;
6386: oe_debug_pub.add( 'LINE_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).LINE_ID , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6383: for i in 1..CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count
6384: loop
6385: oe_debug_pub.add( 'PROCESS: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROCESS , 1 ) ;
6386: oe_debug_pub.add( 'LINE_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).LINE_ID , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6384: loop
6385: oe_debug_pub.add( 'PROCESS: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROCESS , 1 ) ;
6386: oe_debug_pub.add( 'LINE_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).LINE_ID , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6385: oe_debug_pub.add( 'PROCESS: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROCESS , 1 ) ;
6386: oe_debug_pub.add( 'LINE_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).LINE_ID , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6386: oe_debug_pub.add( 'LINE_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).LINE_ID , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6387: oe_debug_pub.add( 'SALES_ORDER_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM ,1 );
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6388: oe_debug_pub.add( 'ERROR_MESSAGE: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_MESSAGE , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6389: oe_debug_pub.add( 'TOP_MODEL_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME , 1 ) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6390: oe_debug_pub.add( 'TOP_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM , 1 ) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6391: oe_debug_pub.add( 'TOP_CONFIG_NAME: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6399: oe_debug_pub.add( 'REQUEST_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID , 1 ) ;
6392: oe_debug_pub.add( 'TOP_CONFIG_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM , 1) ;
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6399: oe_debug_pub.add( 'REQUEST_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID , 1 ) ;
6400:
6393: oe_debug_pub.add( 'PROBLEM_MODEL: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL , 1 ) ;
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6399: oe_debug_pub.add( 'REQUEST_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID , 1 ) ;
6400:
6401: if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE is not null ) then
6394: oe_debug_pub.add( 'PROBLEM_MODEL_LINE_NUM: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_MODEL_LINE_NUM , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6399: oe_debug_pub.add( 'REQUEST_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID , 1 ) ;
6400:
6401: if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE is not null ) then
6402: oe_debug_pub.add( 'MFG_REL_DATE: ' || to_char(CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE) , 1 ) ;
6395: oe_debug_pub.add( 'PROBLEM_CONFIG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).PROBLEM_CONFIG , 1) ;
6396: oe_debug_pub.add( 'ERROR_ORG: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG , 1 ) ;
6397: oe_debug_pub.add( 'ERROR_ORG_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).ERROR_ORG_ID , 1) ;
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6399: oe_debug_pub.add( 'REQUEST_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID , 1 ) ;
6400:
6401: if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE is not null ) then
6402: oe_debug_pub.add( 'MFG_REL_DATE: ' || to_char(CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE) , 1 ) ;
6403: else
6398: oe_debug_pub.add( 'NOTIFY_USER: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).NOTIFY_USER, 1) ;
6399: oe_debug_pub.add( 'REQUEST_ID: ' || CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID , 1 ) ;
6400:
6401: if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE is not null ) then
6402: oe_debug_pub.add( 'MFG_REL_DATE: ' || to_char(CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE) , 1 ) ;
6403: else
6404:
6405: oe_debug_pub.add( 'MFG REL DATE is null ' , 1 ) ;
6406: end if;
6401: if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE is not null ) then
6402: oe_debug_pub.add( 'MFG_REL_DATE: ' || to_char(CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).MFG_REL_DATE) , 1 ) ;
6403: else
6404:
6405: oe_debug_pub.add( 'MFG REL DATE is null ' , 1 ) ;
6406: end if;
6407:
6408:
6409:
6406: end if;
6407:
6408:
6409:
6410: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || ' going to send notification ' , 1 ) ;
6411:
6412: CTO_UTILITY_PK.send_notification (
6413: P_PROCESS => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).process
6414: ,P_LINE_ID => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).line_id
6425: ,P_NOTIFY_USER => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).notify_user
6426: ,P_REQUEST_ID => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).request_id
6427: ,P_MFG_REL_DATE => CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).mfg_rel_date );
6428:
6429: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || ' sent notification ' , 1 ) ;
6430:
6431:
6432: end loop ;
6433:
6432: end loop ;
6433:
6434: else
6435:
6436: oe_debug_pub.add( 'DROPPED ITEM TABLE ' || ' is empty ' , 1 ) ;
6437:
6438: end if ;
6439:
6440:
6439:
6440:
6441: commit ; -- AUTONOMOUS TRANSACTION
6442:
6443: oe_debug_pub.add('SEND_OID_NOTIFICATION : ******** END AUTONOMOUS TRANSACTION FOR SEND_OID_NOTIFICATION **********' , 1 );
6444: CTO_CONFIG_BOM_PK.g_t_dropped_item_type.delete;
6445:
6446: end send_oid_notification ;
6447:
6455: is
6456: begin
6457:
6458: IF PG_DEBUG <> 0 THEN
6459: oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
6460: END IF;
6461:
6462: BEGIN
6463: -- bugfix 2203802: Instead of getting the planner code directly from MSI,
6474: and p.planner_code = item.planner_code
6475: and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
6476:
6477:
6478: oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || x_planner_code ,2);
6479:
6480:
6481: EXCEPTION
6482:
6481: EXCEPTION
6482:
6483: WHEN OTHERS THEN
6484: IF PG_DEBUG <> 0 THEN
6485: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
6486:
6487: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
6488:
6489: x_planner_code := 'SYSADMIN' ;
6483: WHEN OTHERS THEN
6484: IF PG_DEBUG <> 0 THEN
6485: oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
6486:
6487: oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
6488:
6489: x_planner_code := 'SYSADMIN' ;
6490:
6491: END IF;
6491: END IF;
6492: END;
6493:
6494:
6495: oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || x_planner_code ,2);
6496:
6497: end get_planner_code ;
6498:
6499:
6514: v_recipient varchar2(200) ;
6515: begin
6516:
6517: IF PG_DEBUG <> 0 THEN
6518: oe_debug_pub.add('handle_expected_error: entered handle_expected_error ' , 3);
6519: oe_debug_pub.add('handle_expected_error: p_inventory_item_id ' || p_inventory_item_id , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6515: begin
6516:
6517: IF PG_DEBUG <> 0 THEN
6518: oe_debug_pub.add('handle_expected_error: entered handle_expected_error ' , 3);
6519: oe_debug_pub.add('handle_expected_error: p_inventory_item_id ' || p_inventory_item_id , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6516:
6517: IF PG_DEBUG <> 0 THEN
6518: oe_debug_pub.add('handle_expected_error: entered handle_expected_error ' , 3);
6519: oe_debug_pub.add('handle_expected_error: p_inventory_item_id ' || p_inventory_item_id , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6517: IF PG_DEBUG <> 0 THEN
6518: oe_debug_pub.add('handle_expected_error: entered handle_expected_error ' , 3);
6519: oe_debug_pub.add('handle_expected_error: p_inventory_item_id ' || p_inventory_item_id , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6518: oe_debug_pub.add('handle_expected_error: entered handle_expected_error ' , 3);
6519: oe_debug_pub.add('handle_expected_error: p_inventory_item_id ' || p_inventory_item_id , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6519: oe_debug_pub.add('handle_expected_error: p_inventory_item_id ' || p_inventory_item_id , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6520: oe_debug_pub.add('handle_expected_error: p_organization_id ' || p_organization_id , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6521: oe_debug_pub.add('handle_expected_error: p_line_id ' || p_line_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6522: oe_debug_pub.add('handle_expected_error: p_sales_order_num ' || p_sales_order_num , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6530: oe_debug_pub.add('handle_expected_error: ******** P_PROCESS ******** ' || p_process , 3);
6523: oe_debug_pub.add('handle_expected_error: p_top_model_name ' || p_top_model_name , 3);
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6530: oe_debug_pub.add('handle_expected_error: ******** P_PROCESS ******** ' || p_process , 3);
6531:
6524: oe_debug_pub.add('handle_expected_error: p_top_model_line_num ' || p_top_model_line_num , 3);
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6530: oe_debug_pub.add('handle_expected_error: ******** P_PROCESS ******** ' || p_process , 3);
6531:
6532:
6525: oe_debug_pub.add('handle_expected_error: p_top_config_name ' || p_top_config_name , 3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6530: oe_debug_pub.add('handle_expected_error: ******** P_PROCESS ******** ' || p_process , 3);
6531:
6532:
6533: oe_debug_pub.add('handle_expected_error: ' || 'Getting Custom Recipient..',3);
6526: oe_debug_pub.add('handle_expected_error: p_top_config_line_num ' || p_top_config_line_num , 3);
6527: oe_debug_pub.add('handle_expected_error: p_msg_count ' || p_msg_count , 3);
6528: oe_debug_pub.add('handle_expected_error: p_planner_code ' || p_planner_code , 3);
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6530: oe_debug_pub.add('handle_expected_error: ******** P_PROCESS ******** ' || p_process , 3);
6531:
6532:
6533: oe_debug_pub.add('handle_expected_error: ' || 'Getting Custom Recipient..',3);
6534: END IF;
6529: oe_debug_pub.add('handle_expected_error: p_request_id ' || p_request_id , 3);
6530: oe_debug_pub.add('handle_expected_error: ******** P_PROCESS ******** ' || p_process , 3);
6531:
6532:
6533: oe_debug_pub.add('handle_expected_error: ' || 'Getting Custom Recipient..',3);
6534: END IF;
6535:
6536: v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => p_error_type
6537: ,p_inventory_item_id => p_inventory_item_id
6543:
6544: if( v_recipient is not null ) then
6545:
6546: IF PG_DEBUG <> 0 THEN
6547: oe_debug_pub.add('handle_expected_error: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
6548: END IF;
6549:
6550: else
6551: v_recipient := p_planner_code ;
6549:
6550: else
6551: v_recipient := p_planner_code ;
6552: IF PG_DEBUG <> 0 THEN
6553: oe_debug_pub.add('handle_expected_error: ' || 'planner code is ..' || v_recipient ,3);
6554: END IF;
6555: end if;
6556:
6557:
6569: ,P_NOTIFY_USER => v_recipient
6570: ,P_REQUEST_ID => p_request_id ) ;
6571:
6572: IF PG_DEBUG <> 0 THEN
6573: oe_debug_pub.add('handle_expected_error: ' || 'done handle_expected_error..' ,3);
6574: END IF;
6575:
6576:
6577: end handle_expected_error;
6639: -- they are already existing for this item. It they are existing
6640: -- We should not create it once again.
6641:
6642: IF PG_DEBUG <> 0 THEN
6643: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6644: ||to_char(config_items_rec.line_id),1);
6645: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6646: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6647: END IF;
6641:
6642: IF PG_DEBUG <> 0 THEN
6643: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6644: ||to_char(config_items_rec.line_id),1);
6645: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6646: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6647: END IF;
6648:
6649: BEGIN
6642: IF PG_DEBUG <> 0 THEN
6643: oe_debug_pub.add('Cto_Utility_pk: ' || 'Creating attachment for line id = '
6644: ||to_char(config_items_rec.line_id),1);
6645: oe_debug_pub.add('Cto_Utility_pk: '|| 'Config item id = '||to_char(config_items_rec.config_item_id),1);
6646: oe_debug_pub.add('Cto_Utility_pk: '|| 'Po Validation Org = '||to_char(config_orgs_rec.po_valid_org),1);
6647: END IF;
6648:
6649: BEGIN
6650: SELECT document_id
6655: AND entity_name = 'MTL_SYSTEM_ITEMS'
6656: AND Pk3_value = 'CTO:BOM:ATTACHMENT';
6657:
6658: IF PG_DEBUG <> 0 THEN
6659: oe_debug_pub.add('Cto_Utility_pk: '
6660: || 'Bom attachment already exists for this item.. document_id ='||to_char(l_document_id),1);
6661: END IF;
6662: EXCEPTION
6663: WHEN NO_DATA_FOUND THEN
6661: END IF;
6662: EXCEPTION
6663: WHEN NO_DATA_FOUND THEN
6664: IF PG_DEBUG <> 0 THEN
6665: oe_debug_pub.add('Cto_Utility_pk: '
6666: || 'There is no bom document attached to this item.. We need to attach the document',1);
6667: END IF;
6668:
6669: IF l_attach_text is not null THEN
6676: p_doc_type => 'CTO:BOM:ATTACHMENT',
6677: x_Return_Status => x_Return_Status);
6678:
6679: IF PG_DEBUG <> 0 THEN
6680: oe_debug_pub.add('Cto_Utility_pk: ' || 'Return status from create_attachment after bom attachment='
6681: ||x_Return_Status,1);
6682: END IF;
6683:
6684: END IF;
6686:
6687: End Loop;
6688: End Loop;
6689: Exception When Others Then
6690: oe_debug_pub.add('Some Error occured in generating attachment',1);
6691: oe_debug_pub.add('Ignoring the error and continuing the process',1);
6692: End Create_item_attachments;
6693:
6694:
6687: End Loop;
6688: End Loop;
6689: Exception When Others Then
6690: oe_debug_pub.add('Some Error occured in generating attachment',1);
6691: oe_debug_pub.add('Ignoring the error and continuing the process',1);
6692: End Create_item_attachments;
6693:
6694:
6695:
6737: and ordered_quantity > 0 ; -- Added this condition to take care of cancel line cases.
6738:
6739: if bcol_count <> oe_count then
6740: IF PG_DEBUG <> 0 THEN
6741: oe_debug_pub.add ('validate_oe_data: '||v_step||': OE count '||oe_count||' and BCOL count '||bcol_count||' does not match.');
6742: END IF;
6743: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6744: end if;
6745:
6792: AND bcol.top_model_line_id is not null
6793: AND bcol.ato_line_id = p_bcol_line_id )) ;
6794:
6795: IF PG_DEBUG <> 0 THEN
6796: oe_debug_pub.add ('validate_oe_data: Symmetric Difference between OEL and BCOL did return rows .');
6797: END IF;
6798:
6799:
6800: v_oe_bcol_diff := 'Y' ;
6802: exception
6803: when no_data_found then
6804:
6805: IF PG_DEBUG <> 0 THEN
6806: oe_debug_pub.add ('validate_oe_data: Symmetric Difference between OEL and BCOL did not return any rows .');
6807: END IF;
6808:
6809: when others then
6810:
6808:
6809: when others then
6810:
6811: IF PG_DEBUG <> 0 THEN
6812: oe_debug_pub.add ('validate_oe_data: Symmetric Difference between OEL and BCOL did result in error .' || SQLCODE );
6813: oe_debug_pub.add ('validate_oe_data: Symmetric Difference between OEL and BCOL did result in error .' || SQLERRM );
6814: END IF;
6815:
6816: v_oe_bcol_diff := 'Y' ;
6809: when others then
6810:
6811: IF PG_DEBUG <> 0 THEN
6812: oe_debug_pub.add ('validate_oe_data: Symmetric Difference between OEL and BCOL did result in error .' || SQLCODE );
6813: oe_debug_pub.add ('validate_oe_data: Symmetric Difference between OEL and BCOL did result in error .' || SQLERRM );
6814: END IF;
6815:
6816: v_oe_bcol_diff := 'Y' ;
6817:
6820:
6821:
6822: if( v_oe_bcol_diff = 'Y' ) then
6823: IF PG_DEBUG <> 0 THEN
6824: oe_debug_pub.add ('validate_oe_data: data in BCOL and OE_ORDER_LINES_ALL does not match' , 1);
6825: END IF;
6826: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6827: end if;
6828:
6832:
6833: exception
6834: when OTHERS then
6835: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6836: oe_debug_pub.add('validate_oe_data: ' || 'validate_oe_data::others:: '||sqlerrm,1);
6837:
6838:
6839: end validate_oe_data ;
6840:
6938: FETCH c1 BULK COLLECT INTO l_comp_item_id;
6939:
6940: if l_comp_item_id.count = 0 then
6941: -- This situation should never arise. If it does, raise error.
6942: oe_debug_pub.add ('l_comp_item_id.count = 0. Raising error..');
6943: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6944: end if;
6945:
6946: IF PG_DEBUG <> 0 THEN
6943: raise FND_API.G_EXC_UNEXPECTED_ERROR;
6944: end if;
6945:
6946: IF PG_DEBUG <> 0 THEN
6947: oe_debug_pub.add ('Number of records selected = '|| l_comp_item_id.count);
6948: oe_debug_pub.add ('COMPARESIZE = '|| COMPARESIZE);
6949: END IF;
6950:
6951: if (l_comp_item_id.count > COMPARESIZE) then
6944: end if;
6945:
6946: IF PG_DEBUG <> 0 THEN
6947: oe_debug_pub.add ('Number of records selected = '|| l_comp_item_id.count);
6948: oe_debug_pub.add ('COMPARESIZE = '|| COMPARESIZE);
6949: END IF;
6950:
6951: if (l_comp_item_id.count > COMPARESIZE) then
6952: -- Get the first N components
6953: for i in 1 .. COMPARESIZE
6954: loop
6955: str := str || l_comp_item_id(i);
6956: IF PG_DEBUG <> 0 THEN
6957: oe_debug_pub.add (' str = '||str);
6958: END IF;
6959: if (i < COMPARESIZE) then
6960: str := str || '-'; -- Append "-" for each value.
6961: end if;
6970: while (j <> loop_limit)
6971: loop
6972: str := str || l_comp_item_id(j);
6973: IF PG_DEBUG <> 0 THEN
6974: oe_debug_pub.add (' str = '||str);
6975: END IF;
6976: j := j - 1;
6977: if (j <> loop_limit) then
6978: str := str || '-';
6984: for i in 1 .. l_comp_item_id.count
6985: loop
6986: str := str || l_comp_item_id(i);
6987: IF PG_DEBUG <> 0 THEN
6988: oe_debug_pub.add (' str = '||str);
6989: END IF;
6990: if (i < l_comp_item_id.count) then
6991: str := str || '-'; -- Append "-" for each value.
6992: end if;
6995:
6996: str := l_comp_item_id.count||'*'||str;
6997:
6998: IF PG_DEBUG <> 0 THEN
6999: oe_debug_pub.add ('str = '|| str);
7000: END IF;
7001:
7002: --
7003: -- bug 7203643
7014: input_string => str);
7015:
7016: IF PG_DEBUG <> 0 THEN
7017: -- bug 7203643
7018: --oe_debug_pub.add ('x_hash_value = '||x_hash_value);
7019: oe_debug_pub.add ('x_hash_value = '||utl_raw.cast_to_raw(x_hash_value));
7020: END IF;
7021:
7022: l_lock_handle := get_lock_handle (p_hash_string => x_hash_value );
7015:
7016: IF PG_DEBUG <> 0 THEN
7017: -- bug 7203643
7018: --oe_debug_pub.add ('x_hash_value = '||x_hash_value);
7019: oe_debug_pub.add ('x_hash_value = '||utl_raw.cast_to_raw(x_hash_value));
7020: END IF;
7021:
7022: l_lock_handle := get_lock_handle (p_hash_string => x_hash_value );
7023:
7022: l_lock_handle := get_lock_handle (p_hash_string => x_hash_value );
7023:
7024: IF PG_DEBUG <> 0 THEN
7025: -- bug 7203643
7026: --oe_debug_pub.add ('x_hash_value = '||x_hash_value);
7027: oe_debug_pub.add ('l_lock_handle = '||l_lock_handle);
7028: END IF;
7029:
7030: --
7023:
7024: IF PG_DEBUG <> 0 THEN
7025: -- bug 7203643
7026: --oe_debug_pub.add ('x_hash_value = '||x_hash_value);
7027: oe_debug_pub.add ('l_lock_handle = '||l_lock_handle);
7028: END IF;
7029:
7030: --
7031: -- request lock with release_on_commit TRUE so that we dont have to manually
7037: ,timeout => dbms_lock.maxwait
7038: ,release_on_commit => TRUE);
7039:
7040: IF PG_DEBUG <> 0 THEN
7041: oe_debug_pub.add ('lock_for_match: Returning from lock_for_match.');
7042: END IF;
7043: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Lock_For_Match End Time: '||to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));
7044:
7045: EXCEPTION
7044:
7045: EXCEPTION
7046:
7047: WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
7048: oe_debug_pub.add('lock_for_match: ' || 'Unexpected Error.');
7049: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7050: -- Get message count and data
7051: cto_msg_pub.count_and_get
7052: ( p_msg_count => xMsgCount
7052: ( p_msg_count => xMsgCount
7053: , p_msg_data => xMsgData
7054: );
7055: WHEN OTHERS then
7056: oe_debug_pub.add('lock_for_match: ' || 'Others Exception : ' || sqlerrm);
7057: x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
7058: -- Get message count and data
7059: cto_msg_pub.count_and_get
7060: ( p_msg_count => xMsgCount
7091:
7092: --validate hash_value
7093: IF (p_hash_value IS NULL) THEN
7094: --raise error condition
7095: oe_debug_pub.add('RELEASE_LOCK: Hash Value is null but required for releasing lock.');
7096: RAISE fnd_api.g_exc_error;
7097: END IF;
7098:
7099:
7115:
7116: EXCEPTION
7117:
7118: WHEN fnd_api.g_exc_error THEN
7119: oe_debug_pub.add('release_lock: ' || 'expected error');
7120: x_return_status := fnd_api.g_ret_sts_error;
7121:
7122: WHEN fnd_api.g_exc_unexpected_error THEN
7123: oe_debug_pub.add('release_lock: ' || 'Unexpected error');
7119: oe_debug_pub.add('release_lock: ' || 'expected error');
7120: x_return_status := fnd_api.g_ret_sts_error;
7121:
7122: WHEN fnd_api.g_exc_unexpected_error THEN
7123: oe_debug_pub.add('release_lock: ' || 'Unexpected error');
7124: x_return_status := fnd_api.g_ret_sts_unexp_error ;
7125:
7126: WHEN OTHERS THEN
7127: oe_debug_pub.add('release_lock: ' || 'Others Exception : ' || sqlerrm);
7123: oe_debug_pub.add('release_lock: ' || 'Unexpected error');
7124: x_return_status := fnd_api.g_ret_sts_unexp_error ;
7125:
7126: WHEN OTHERS THEN
7127: oe_debug_pub.add('release_lock: ' || 'Others Exception : ' || sqlerrm);
7128: x_return_status := fnd_api.g_ret_sts_unexp_error ;
7129:
7130: END release_lock;
7131:
7227: and msi.organization_id = oel.ship_from_org_id
7228: and oel.line_id = p_order_line_id; --bugfix 4557050
7229:
7230: IF PG_DEBUG <> 0 THEN
7231: oe_debug_pub.add('get_resv_qty: ' || 'Entered get_resv_qty', 1);
7232: l_message := 'Entered get_rsv_qty_code';
7233: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7234: oe_debug_pub.add('get_resv_qty: ' || 'Before cursor c_resv', 5);
7235: l_message := 'Before cursor c_resv';
7230: IF PG_DEBUG <> 0 THEN
7231: oe_debug_pub.add('get_resv_qty: ' || 'Entered get_resv_qty', 1);
7232: l_message := 'Entered get_rsv_qty_code';
7233: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7234: oe_debug_pub.add('get_resv_qty: ' || 'Before cursor c_resv', 5);
7235: l_message := 'Before cursor c_resv';
7236: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7237: END IF;
7238:
7248: x_rsv_rec(l_index).secondary_reservation_quantity := cur_var.secondary_reservation_quantity;
7249:
7250:
7251: IF PG_DEBUG <> 0 THEN
7252: oe_debug_pub.add('get_resv_qty:'||'source_id=>' || cur_var.supply_source_type_id, 5);
7253: l_message := 'source_id=>'|| cur_var.supply_source_type_id;
7254: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7255:
7256: oe_debug_pub.add('get_resv_qty:'||'prim_rsv_qty=>' || cur_var.primary_reservation_quantity, 5);
7252: oe_debug_pub.add('get_resv_qty:'||'source_id=>' || cur_var.supply_source_type_id, 5);
7253: l_message := 'source_id=>'|| cur_var.supply_source_type_id;
7254: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7255:
7256: oe_debug_pub.add('get_resv_qty:'||'prim_rsv_qty=>' || cur_var.primary_reservation_quantity, 5);
7257: l_message := 'rsv_qty=>' || cur_var.primary_reservation_quantity;
7258: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7259:
7260: oe_debug_pub.add('get_resv_qty:'||'sec_rsv_qty=>' || cur_var.secondary_reservation_quantity, 5);
7256: oe_debug_pub.add('get_resv_qty:'||'prim_rsv_qty=>' || cur_var.primary_reservation_quantity, 5);
7257: l_message := 'rsv_qty=>' || cur_var.primary_reservation_quantity;
7258: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7259:
7260: oe_debug_pub.add('get_resv_qty:'||'sec_rsv_qty=>' || cur_var.secondary_reservation_quantity, 5);
7261: l_message := 'sec_rsv_qty=>' || cur_var.secondary_reservation_quantity;
7262: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7263:
7264: END IF;
7265:
7266: END LOOP;
7267:
7268: IF PG_DEBUG = 5 THEN
7269: oe_debug_pub.add('get_resv_qty:'||'printing rsv source type and qty in loop', 5);
7270: l_message := 'printing rsv source type and qty in loop';
7271: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7272:
7273: oe_debug_pub.add('get_resv_qty:'||'RSV_SRC_TYP '||' Prim Quantity '
7269: oe_debug_pub.add('get_resv_qty:'||'printing rsv source type and qty in loop', 5);
7270: l_message := 'printing rsv source type and qty in loop';
7271: cto_wip_workflow_api_pk.cto_debug('get_resv_qty:', l_message);
7272:
7273: oe_debug_pub.add('get_resv_qty:'||'RSV_SRC_TYP '||' Prim Quantity '
7274: ||' Sec qunatity ', 5);
7275:
7276:
7277: IF x_rsv_rec.count <> 0 THEN
7278: k := x_rsv_rec.first;
7279: WHILE(k is not null)
7280: LOOP
7281:
7282: oe_debug_pub.add('get_resv_qty:'||x_rsv_rec(k).supply_source_type_id
7283: ||' => ' ||x_rsv_rec(k).primary_reservation_quantity
7284: ||' => ' ||x_rsv_rec(k).secondary_reservation_quantity,5);
7285:
7286:
7319: p_demand_source_type => inv_reservation_global.g_source_type_oe,
7320: p_demand_source_delivery => NULL,
7321: p_use_open_quantity => 'Y');
7322: IF PG_DEBUG <> 0 THEN
7323: oe_debug_pub.add('get_resv_qty:'||'flow open quantity =>' || v_open_flow_qty, 5);
7324: END IF;
7325: -- Added by Renga Kannan on 06/27/05
7326: -- Getting the primary uom code from mtl system items.
7327:
7334:
7335: lStmtNum := 45;
7336:
7337: IF PG_DEBUG <> 0 THEN
7338: oe_debug_pub.add('get_resv_qty:'||'adding flow to x_rsv_rec', 5);
7339: END IF;
7340:
7341: --add this to record structure
7342: l_index := CTO_UTILITY_PK.g_source_type_flow;
7403:
7404: EXCEPTION
7405: WHEN fnd_api.g_exc_error THEN
7406: IF PG_DEBUG <> 0 THEN
7407: oe_debug_pub.add('get_resv_qty: ' || 'Exception in stmt num: '
7408: || to_char(lStmtNum), 1);
7409: END IF;
7410: x_return_status := FND_API.G_RET_STS_ERROR;
7411: -- Get message count and data
7414: , p_msg_data => x_msg_data
7415: );
7416: WHEN fnd_api.g_exc_unexpected_error THEN
7417: IF PG_DEBUG <> 0 THEN
7418: oe_debug_pub.add('get_resv_qty: '|| ' Unexpected Exception in stmt num: '
7419: || to_char(lStmtNum), 1);
7420: END IF;
7421: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7422: -- Get message count and data
7424: ( p_msg_count => x_msg_count
7425: , p_msg_data => x_msg_data
7426: );
7427: WHEN OTHERS then
7428: oe_debug_pub.add('errmsg'||sqlerrm);
7429: IF PG_DEBUG <> 0 THEN
7430:
7431: oe_debug_pub.add('get_resv_qty: ' || 'Others Exception in stmt num: '
7432: || to_char(lStmtNum), 1);
7427: WHEN OTHERS then
7428: oe_debug_pub.add('errmsg'||sqlerrm);
7429: IF PG_DEBUG <> 0 THEN
7430:
7431: oe_debug_pub.add('get_resv_qty: ' || 'Others Exception in stmt num: '
7432: || to_char(lStmtNum), 1);
7433: END IF;
7434: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7435: -- Get message count and data