[Home] [Help]
346: where inventory_item_id = p_item_id
347: and organization_id = p_org_id;
348:
349: IF PG_DEBUG <> 0 THEN
350: oe_debug_pub.add('get_need_by_date: l_offset_days=' || l_offset_days, 1);
351: END IF;
352:
353: -- rkaza. Need to consider the manufacturing calendar while
354: -- determining need by date
374:
375: end if;
376:
377: IF PG_DEBUG <> 0 THEN
378: oe_debug_pub.add('get_need_by_date: x_need_by_date = '|| x_need_by_date, 1);
379: END IF;
380:
381: EXCEPTION
382:
381: EXCEPTION
382:
383: when FND_API.G_EXC_ERROR THEN
384: IF PG_DEBUG <> 0 THEN
385: oe_debug_pub.add('get_need_by_date: ' || 'expected error: ' || sqlerrm, 1);
386: END IF;
387: x_return_status := FND_API.G_RET_STS_ERROR;
388:
389: when FND_API.G_EXC_UNEXPECTED_ERROR then
387: x_return_status := FND_API.G_RET_STS_ERROR;
388:
389: when FND_API.G_EXC_UNEXPECTED_ERROR then
390: IF PG_DEBUG <> 0 THEN
391: oe_debug_pub.add('get_need_by_date: ' || 'unexpected error: ' || sqlerrm, 1);
392: END IF;
393: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394:
395: when others then
393: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394:
395: when others then
396: IF PG_DEBUG <> 0 THEN
397: oe_debug_pub.add('get_need_by_date: ' || 'exception others: ' || sqlerrm, 1);
398: END IF;
399: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400:
401: END get_need_by_date;
409: l_res_quantity number;
410:
411: BEGIN
412: IF PG_DEBUG <> 0 THEN
413: oe_debug_pub.add('is_line_eligible: p_line_id = '|| p_line_id, 5);
414: oe_debug_pub.add('is_line_eligible: p_shipped_quantity = '|| p_shipped_quantity, 5);
415: oe_debug_pub.add('is_line_eligible: p_ordered_quantity = '|| p_ordered_quantity, 5);
416: END IF;
417:
410:
411: BEGIN
412: IF PG_DEBUG <> 0 THEN
413: oe_debug_pub.add('is_line_eligible: p_line_id = '|| p_line_id, 5);
414: oe_debug_pub.add('is_line_eligible: p_shipped_quantity = '|| p_shipped_quantity, 5);
415: oe_debug_pub.add('is_line_eligible: p_ordered_quantity = '|| p_ordered_quantity, 5);
416: END IF;
417:
418: l_res_quantity := cto_auto_procure_pk.get_reserved_qty(p_line_id);
411: BEGIN
412: IF PG_DEBUG <> 0 THEN
413: oe_debug_pub.add('is_line_eligible: p_line_id = '|| p_line_id, 5);
414: oe_debug_pub.add('is_line_eligible: p_shipped_quantity = '|| p_shipped_quantity, 5);
415: oe_debug_pub.add('is_line_eligible: p_ordered_quantity = '|| p_ordered_quantity, 5);
416: END IF;
417:
418: l_res_quantity := cto_auto_procure_pk.get_reserved_qty(p_line_id);
419:
417:
418: l_res_quantity := cto_auto_procure_pk.get_reserved_qty(p_line_id);
419:
420: IF PG_DEBUG <> 0 THEN
421: oe_debug_pub.add('is_line_eligible: l_res_quantity = '|| l_res_quantity, 5);
422: END IF;
423:
424: if l_res_quantity - p_shipped_quantity < p_ordered_quantity then
425: return 'TRUE';
429:
430: EXCEPTION
431: when others then
432: IF PG_DEBUG <> 0 THEN
433: oe_debug_pub.add('is_line_eligible: ' || 'exception others: ' || sqlerrm, 1);
434: END IF;
435: RETURN 'FALSE';
436: END is_line_eligible;
437:
662: -- begin the main procedure.
663: BEGIN
664:
665: IF PG_DEBUG <> 0 THEN
666: oe_debug_pub.add('auto_create_pur_req_cr: entered ' ,1);
667: END IF;
668:
669: -- initialize the program_id and the request_id from the concurrent req
670: l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
687:
688: savepoint begin_line;
689:
690: IF PG_DEBUG <> 0 THEN
691: oe_debug_pub.add('auto_create_pur_req_cr: going to load eligible lines into bom_cto_order_lines_temp ' ,1);
692: END IF;
693:
694: -- load eligible lines into bcolt
695: load_lines_into_bcolt(p_sales_order_line_id,
698: p_offset_days,
699: x_return_status);
700:
701: if x_return_status <> FND_API.G_RET_STS_SUCCESS then
702: oe_debug_pub.add ('Failed to load the lines into GTT.');
703: raise FND_API.G_EXC_ERROR;
704: end if;
705:
706: -- set the return status.
710: RETCODE := 0;
711:
712: lSourceCode := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
713: IF PG_DEBUG <> 0 THEN
714: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'l_source_code = '||lsourcecode,1);
715: END IF;
716:
717: -- set the batch_id to the request_id
718: l_batch_id := FND_GLOBAL.CONC_REQUEST_ID;
722:
723: -- for all the sales order lines (entered, booked )
724: -- Given parameters.
725: IF PG_DEBUG <> 0 THEN
726: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
727:
728: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
729:
730: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
724: -- Given parameters.
725: IF PG_DEBUG <> 0 THEN
726: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
727:
728: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
729:
730: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
731:
732: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
726: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
727:
728: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
729:
730: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
731:
732: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
733:
734: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
728: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
729:
730: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
731:
732: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
733:
734: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
735:
736: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
730: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
731:
732: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
733:
734: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
735:
736: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
737:
738: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
732: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
733:
734: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
735:
736: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
737:
738: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
739:
740: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
734: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
735:
736: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
737:
738: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
739:
740: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
741: END IF;
742:
736: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
737:
738: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
739:
740: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
741: END IF;
742:
743:
744: /* bug fix 3590305/3599860 */
752: open eligible_lines_cur;
753: fetch eligible_lines_cur bulk collect into line_id_tab limit c_batch_size;
754:
755: IF PG_DEBUG <> 0 THEN
756: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'records in line_id_tab ' ||to_char(line_id_tab.count),1);
757: END IF;
758: exit when (line_id_tab.count = 0);
759:
760: -- Open loop for processing each eligible line.
762: tab_index := line_id_tab.first;
763:
764:
765: IF PG_DEBUG <> 0 THEN
766: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'starting tab index ' ||to_char(tab_index),1);
767: END IF;
768:
769: while tab_index is not null
770: LOOP
771: savepoint begin_line;
772:
773: -- Added for bug 7559710.FP to 7388304.pdube
774: IF PG_DEBUG <> 0 THEN
775: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Details of records in line_id_tab collection',2);
776:
777: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Line Id : '||line_id_tab(tab_index).line_id,2);
778:
779: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Org Id : '||line_id_tab(tab_index).org_id,2);
773: -- Added for bug 7559710.FP to 7388304.pdube
774: IF PG_DEBUG <> 0 THEN
775: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Details of records in line_id_tab collection',2);
776:
777: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Line Id : '||line_id_tab(tab_index).line_id,2);
778:
779: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Org Id : '||line_id_tab(tab_index).org_id,2);
780:
781: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Interface Qty : '||line_id_tab(tab_index).qty,2);
775: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Details of records in line_id_tab collection',2);
776:
777: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Line Id : '||line_id_tab(tab_index).line_id,2);
778:
779: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Org Id : '||line_id_tab(tab_index).org_id,2);
780:
781: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Interface Qty : '||line_id_tab(tab_index).qty,2);
782: END IF;
783:
777: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Line Id : '||line_id_tab(tab_index).line_id,2);
778:
779: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Org Id : '||line_id_tab(tab_index).org_id,2);
780:
781: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Interface Qty : '||line_id_tab(tab_index).qty,2);
782: END IF;
783:
784: OPEN oe_lines_cur (line_id_tab(tab_index).line_id);
785:
792:
793: -- Log all the record being processed.
794: -- Processing for
795: IF PG_DEBUG <> 0 THEN
796: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
797: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
798: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
799: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
800: --Bugfix 13500057: Begin debugging changes.
793: -- Log all the record being processed.
794: -- Processing for
795: IF PG_DEBUG <> 0 THEN
796: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
797: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
798: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
799: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
800: --Bugfix 13500057: Begin debugging changes.
801: --Additional OM Information.
794: -- Processing for
795: IF PG_DEBUG <> 0 THEN
796: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
797: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
798: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
799: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
800: --Bugfix 13500057: Begin debugging changes.
801: --Additional OM Information.
802: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Shipped Qty from OE : '||to_char(so_line.shipped_quantity), 5);
795: IF PG_DEBUG <> 0 THEN
796: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
797: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
798: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
799: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
800: --Bugfix 13500057: Begin debugging changes.
801: --Additional OM Information.
802: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Shipped Qty from OE : '||to_char(so_line.shipped_quantity), 5);
803: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Flow Status Code : '||to_char(so_line.flow_status_code), 5);
798: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
799: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
800: --Bugfix 13500057: Begin debugging changes.
801: --Additional OM Information.
802: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Shipped Qty from OE : '||to_char(so_line.shipped_quantity), 5);
803: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Flow Status Code : '||to_char(so_line.flow_status_code), 5);
804: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship Interfaced : '||to_char(so_line.shipping_interfaced_flag), 5);
805: --WF Information.
806: oe_debug_pub.add('+======== Begin WF Information ========+', 5);
799: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
800: --Bugfix 13500057: Begin debugging changes.
801: --Additional OM Information.
802: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Shipped Qty from OE : '||to_char(so_line.shipped_quantity), 5);
803: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Flow Status Code : '||to_char(so_line.flow_status_code), 5);
804: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship Interfaced : '||to_char(so_line.shipping_interfaced_flag), 5);
805: --WF Information.
806: oe_debug_pub.add('+======== Begin WF Information ========+', 5);
807: oe_debug_pub.add('Activity_Name --- Activity_Status --- Begin_Date --- End_Date --- Execution_Time', 5);
800: --Bugfix 13500057: Begin debugging changes.
801: --Additional OM Information.
802: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Shipped Qty from OE : '||to_char(so_line.shipped_quantity), 5);
803: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Flow Status Code : '||to_char(so_line.flow_status_code), 5);
804: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship Interfaced : '||to_char(so_line.shipping_interfaced_flag), 5);
805: --WF Information.
806: oe_debug_pub.add('+======== Begin WF Information ========+', 5);
807: oe_debug_pub.add('Activity_Name --- Activity_Status --- Begin_Date --- End_Date --- Execution_Time', 5);
808: for debug_wf_line in debug_wf_info(so_line.line_id) loop
802: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Shipped Qty from OE : '||to_char(so_line.shipped_quantity), 5);
803: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Flow Status Code : '||to_char(so_line.flow_status_code), 5);
804: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship Interfaced : '||to_char(so_line.shipping_interfaced_flag), 5);
805: --WF Information.
806: oe_debug_pub.add('+======== Begin WF Information ========+', 5);
807: oe_debug_pub.add('Activity_Name --- Activity_Status --- Begin_Date --- End_Date --- Execution_Time', 5);
808: for debug_wf_line in debug_wf_info(so_line.line_id) loop
809: oe_debug_pub.add(nvl(to_char(debug_wf_line.activity_name), 'NULL') || ' --- ' ||
810: nvl(to_char(debug_wf_line.activity_status), 'NULL')|| ' --- ' ||
803: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Flow Status Code : '||to_char(so_line.flow_status_code), 5);
804: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship Interfaced : '||to_char(so_line.shipping_interfaced_flag), 5);
805: --WF Information.
806: oe_debug_pub.add('+======== Begin WF Information ========+', 5);
807: oe_debug_pub.add('Activity_Name --- Activity_Status --- Begin_Date --- End_Date --- Execution_Time', 5);
808: for debug_wf_line in debug_wf_info(so_line.line_id) loop
809: oe_debug_pub.add(nvl(to_char(debug_wf_line.activity_name), 'NULL') || ' --- ' ||
810: nvl(to_char(debug_wf_line.activity_status), 'NULL')|| ' --- ' ||
811: nvl(to_char(debug_wf_line.begin_date), 'NULL') || ' --- ' ||
805: --WF Information.
806: oe_debug_pub.add('+======== Begin WF Information ========+', 5);
807: oe_debug_pub.add('Activity_Name --- Activity_Status --- Begin_Date --- End_Date --- Execution_Time', 5);
808: for debug_wf_line in debug_wf_info(so_line.line_id) loop
809: oe_debug_pub.add(nvl(to_char(debug_wf_line.activity_name), 'NULL') || ' --- ' ||
810: nvl(to_char(debug_wf_line.activity_status), 'NULL')|| ' --- ' ||
811: nvl(to_char(debug_wf_line.begin_date), 'NULL') || ' --- ' ||
812: nvl(to_char(debug_wf_line.end_date), 'NULL') || ' --- ' ||
813: nvl(to_char(debug_wf_line.execution_time), 'NULL'), 5);
812: nvl(to_char(debug_wf_line.end_date), 'NULL') || ' --- ' ||
813: nvl(to_char(debug_wf_line.execution_time), 'NULL'), 5);
814: end loop;
815: --Reservation Information.
816: oe_debug_pub.add('+======== Begin Reservation Information ========+', 5);
817: oe_debug_pub.add('Res_Id --- Res_Qty --- Prim_Res_Qty --- Src_Type --- Src_Header_Id', 5);
818: for debug_reservation_line in debug_reservation_info(so_line.line_id) loop
819: oe_debug_pub.add(nvl(to_char(debug_reservation_line.reservation_id), 'NULL') || ' --- ' ||
820: nvl(to_char(debug_reservation_line.reservation_quantity), 'NULL') || ' --- ' ||
813: nvl(to_char(debug_wf_line.execution_time), 'NULL'), 5);
814: end loop;
815: --Reservation Information.
816: oe_debug_pub.add('+======== Begin Reservation Information ========+', 5);
817: oe_debug_pub.add('Res_Id --- Res_Qty --- Prim_Res_Qty --- Src_Type --- Src_Header_Id', 5);
818: for debug_reservation_line in debug_reservation_info(so_line.line_id) loop
819: oe_debug_pub.add(nvl(to_char(debug_reservation_line.reservation_id), 'NULL') || ' --- ' ||
820: nvl(to_char(debug_reservation_line.reservation_quantity), 'NULL') || ' --- ' ||
821: nvl(to_char(debug_reservation_line.primary_reservation_quantity), 'NULL') || ' --- ' ||
815: --Reservation Information.
816: oe_debug_pub.add('+======== Begin Reservation Information ========+', 5);
817: oe_debug_pub.add('Res_Id --- Res_Qty --- Prim_Res_Qty --- Src_Type --- Src_Header_Id', 5);
818: for debug_reservation_line in debug_reservation_info(so_line.line_id) loop
819: oe_debug_pub.add(nvl(to_char(debug_reservation_line.reservation_id), 'NULL') || ' --- ' ||
820: nvl(to_char(debug_reservation_line.reservation_quantity), 'NULL') || ' --- ' ||
821: nvl(to_char(debug_reservation_line.primary_reservation_quantity), 'NULL') || ' --- ' ||
822: nvl(to_char(debug_reservation_line.supply_source_type_id), 'NULL') || ' --- ' ||
823: nvl(to_char(debug_reservation_line.supply_source_header_id), 'NULL'), 5);
822: nvl(to_char(debug_reservation_line.supply_source_type_id), 'NULL') || ' --- ' ||
823: nvl(to_char(debug_reservation_line.supply_source_header_id), 'NULL'), 5);
824: end loop;
825: --Shipping Information.
826: oe_debug_pub.add('+======== Begin Shipping Information ========+', 5);
827: oe_debug_pub.add('Released_Status --- oe_i/f --- inv_i/f --- shipped_quantity', 5);
828: for debug_shipping_line in debug_shipping_info(so_line.line_id) loop
829: oe_debug_pub.add(nvl(to_char(debug_shipping_line.released_status), 'NULL') || ' --- ' ||
830: nvl(to_char(debug_shipping_line.oe_interfaced_flag), 'NULL') || ' --- ' ||
823: nvl(to_char(debug_reservation_line.supply_source_header_id), 'NULL'), 5);
824: end loop;
825: --Shipping Information.
826: oe_debug_pub.add('+======== Begin Shipping Information ========+', 5);
827: oe_debug_pub.add('Released_Status --- oe_i/f --- inv_i/f --- shipped_quantity', 5);
828: for debug_shipping_line in debug_shipping_info(so_line.line_id) loop
829: oe_debug_pub.add(nvl(to_char(debug_shipping_line.released_status), 'NULL') || ' --- ' ||
830: nvl(to_char(debug_shipping_line.oe_interfaced_flag), 'NULL') || ' --- ' ||
831: nvl(to_char(debug_shipping_line.inv_interfaced_flag), 'NULL') || ' --- ' ||
825: --Shipping Information.
826: oe_debug_pub.add('+======== Begin Shipping Information ========+', 5);
827: oe_debug_pub.add('Released_Status --- oe_i/f --- inv_i/f --- shipped_quantity', 5);
828: for debug_shipping_line in debug_shipping_info(so_line.line_id) loop
829: oe_debug_pub.add(nvl(to_char(debug_shipping_line.released_status), 'NULL') || ' --- ' ||
830: nvl(to_char(debug_shipping_line.oe_interfaced_flag), 'NULL') || ' --- ' ||
831: nvl(to_char(debug_shipping_line.inv_interfaced_flag), 'NULL') || ' --- ' ||
832: nvl(to_char(debug_shipping_line.shipped_quantity), 'NULL'), 5);
833: end loop;
830: nvl(to_char(debug_shipping_line.oe_interfaced_flag), 'NULL') || ' --- ' ||
831: nvl(to_char(debug_shipping_line.inv_interfaced_flag), 'NULL') || ' --- ' ||
832: nvl(to_char(debug_shipping_line.shipped_quantity), 'NULL'), 5);
833: end loop;
834: oe_debug_pub.add('+-------- End SO Line Information --------+', 5);
835: END IF;
836:
837:
838: -- get the sourcing type of the item in the specified organization.
881: ((p_create_req_type = 1 or p_create_req_type is null)
882: and v_source_type <> 3) THEN
883:
884: IF PG_DEBUG <> 0 THEN
885: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
886: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
887: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
888: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
889: END IF;
882: and v_source_type <> 3) THEN
883:
884: IF PG_DEBUG <> 0 THEN
885: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
886: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
887: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
888: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
889: END IF;
890:
883:
884: IF PG_DEBUG <> 0 THEN
885: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
886: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
887: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
888: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
889: END IF;
890:
891: RETCODE := 1;
884: IF PG_DEBUG <> 0 THEN
885: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
886: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
887: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
888: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
889: END IF;
890:
891: RETCODE := 1;
892: rollback to begin_line;
894: goto loop1;
895: --Bugfix 9957935
896: ELSIF v_source_type = 2 THEN
897: IF PG_DEBUG <> 0 THEN
898: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
899: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'This is not a procured configuration...',1);
900: END IF;
901: goto loop1;
902: END IF;
895: --Bugfix 9957935
896: ELSIF v_source_type = 2 THEN
897: IF PG_DEBUG <> 0 THEN
898: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
899: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'This is not a procured configuration...',1);
900: END IF;
901: goto loop1;
902: END IF;
903:
902: END IF;
903:
904: ELSE
905: IF PG_DEBUG <> 0 THEN
906: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error in the sourcing rule. Status = '|| x_return_status,1);
907: END IF;
908: RETCODE := 1;
909: rollback to begin_line;
910: update_bcolt_line_status(line_id_tab(tab_index).line_id, 3, x_return_status); --7559710
932: so_line.inventory_item_id);
933:
934: IF nvl(p_new_order_quantity, 0) = 0 THEN
935: -- Should throw this into log even if debug is off.
936: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The new order quantity is zero. Not eligible for req creation.',1);
937:
938: rollback to begin_line;
939: update_bcolt_line_status(line_id_tab(tab_index).line_id, 4, x_return_status); --7559710
940: goto loop1;
946:
947: /*
948: if (lOperUnit <> so_line.org_id ) then
949: IF PG_DEBUG <> 0 THEN
950: oe_debug_pub.add('Setting the Org Context to '||so_line.org_id,1);
951: END IF;
952:
953: OE_ORDER_CONTEXT_GRP.Set_Created_By_Context (
954: p_header_id => NULL
960: ,x_msg_count => x_Msg_Count
961: ,x_msg_data => x_Msg_Data );
962:
963: IF x_return_status <> FND_API.G_RET_STS_SUCCESS Then
964: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error in the set_created_by_context',1);
965: RETCODE := 1;
966: rollback to begin_line;
967: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
968: goto loop1;
992: x_msg_count => v_x_error_msg_count,
993: x_msg_data => v_x_error_msg);
994:
995: IF (v_x_hold_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
996: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Failed in Check Hold ' || v_x_hold_return_status, 1);
997: RETCODE := 1;
998: rollback to begin_line;
999: update_bcolt_line_status(line_id_tab(tab_index).line_id, 3, x_return_status); --7559710
1000: goto loop1;
1001:
1002: ELSE
1003:
1004: IF PG_DEBUG <> 0 THEN
1005: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Success in Check Hold ' || v_x_hold_return_status, 1);
1006: END IF;
1007:
1008: IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
1009:
1006: END IF;
1007:
1008: IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
1009:
1010: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Order Line ID ' || to_char(so_line.line_id )|| 'is on HOLD. ' ||v_x_hold_result_out,1);
1011: fnd_message.set_name('BOM', 'CTO_ORDER_LINE_ON_HOLD');
1012: oe_msg_pub.add;
1013:
1014: RETCODE := 1;
1036: x_return_status => x_return_status);
1037:
1038: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1039: IF PG_DEBUG <> 0 THEN
1040: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'success from get_need_by_date' || ' l_need_by_date=' || l_need_by_date, 5);
1041: END IF;
1042: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1043: IF PG_DEBUG <> 0 THEN
1044: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'expected error in get_need_by_date', 5);
1040: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'success from get_need_by_date' || ' l_need_by_date=' || l_need_by_date, 5);
1041: END IF;
1042: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1043: IF PG_DEBUG <> 0 THEN
1044: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'expected error in get_need_by_date', 5);
1045: END IF;
1046: raise fnd_api.g_exc_error;
1047: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1048: IF PG_DEBUG <> 0 THEN
1045: END IF;
1046: raise fnd_api.g_exc_error;
1047: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1048: IF PG_DEBUG <> 0 THEN
1049: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'unexpected error in get_need_by_date', 5);
1050: END IF;
1051: raise fnd_api.g_exc_unexpected_error;
1052: END IF;
1053:
1051: raise fnd_api.g_exc_unexpected_error;
1052: END IF;
1053:
1054: IF PG_DEBUG <> 0 THEN
1055: oe_debug_pub.add('Need by date: '|| l_need_by_date, 1);
1056: ENd IF;
1057:
1058: -- Insert record into the interface table.
1059:
1088: l_req_input_data, -- req_interface_input_data
1089: x_return_status );
1090:
1091: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1092: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'populate_req_interface failed with status '|| x_return_status,1);
1093: RETCODE := 1;
1094: rollback to begin_line;
1095: update_bcolt_line_status(line_id_tab(tab_index).line_id, 3, x_return_status); --7559710
1096: goto loop1;
1096: goto loop1;
1097: ELSE
1098: v_rec_count_noerr := v_rec_count_noerr + 1;
1099: IF PG_DEBUG <> 0 THEN
1100: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Insert successful.',1);
1101: END IF;
1102: END IF;
1103:
1104:
1129: l_rets := CTO_WORKFLOW_API_PK.display_wf_status( p_order_line_id=>so_line.line_id);
1130:
1131: IF l_rets = 0 THEN
1132:
1133: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error occurred in display_wf_status - Stmt_num'||to_char(l_stmt_num),1);
1134:
1135: RETCODE := 1;
1136: rollback to begin_line;
1137: update_bcolt_line_status(line_id_tab(tab_index).line_id, 3, x_return_status); --7559710
1138: goto loop1;
1139:
1140: ELSE
1141: IF PG_DEBUG <> 0 THEN
1142: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Order updated to REQ_REQUESTED.',1);
1143: END IF;
1144: END IF;
1145:
1146:
1170: -- (FP 5633040) bugfix 5623360: call with RESERVED instead of COMPLETE.
1171:
1172: IF NOT l_res THEN
1173:
1174: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
1175:
1176: RETCODE := 1;
1177: rollback to begin_line;
1178: update_bcolt_line_status(line_id_tab(tab_index).line_id, 3, x_return_status); --7559710
1214: end loop; -- loop on eligible_lines_cur
1215:
1216:
1217: IF PG_DEBUG <> 0 THEN
1218: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1219:
1220: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1221:
1222: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1216:
1217: IF PG_DEBUG <> 0 THEN
1218: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1219:
1220: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1221:
1222: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1223:
1224: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1218: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1219:
1220: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1221:
1222: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1223:
1224: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1225:
1226: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1220: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1221:
1222: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1223:
1224: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1225:
1226: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1227: END IF;
1228:
1222: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1223:
1224: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1225:
1226: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1227: END IF;
1228:
1229: --Bugfix 10235354
1230: IF PG_DEBUG <> 0 THEN
1239: and pria.process_flag = 'ERROR';
1240: begin
1241: open error_lines_cur;
1242:
1243: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1244: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The following lines are stuck in requisition interface table.',1);
1245: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Correct the errors, run Requisition Import Exception Report with Delete Exceptions parameter set to Yes and then run CTOACREQ program again to process them',1);
1246:
1247:
1240: begin
1241: open error_lines_cur;
1242:
1243: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1244: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The following lines are stuck in requisition interface table.',1);
1245: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Correct the errors, run Requisition Import Exception Report with Delete Exceptions parameter set to Yes and then run CTOACREQ program again to process them',1);
1246:
1247:
1248: loop
1241: open error_lines_cur;
1242:
1243: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1244: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The following lines are stuck in requisition interface table.',1);
1245: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Correct the errors, run Requisition Import Exception Report with Delete Exceptions parameter set to Yes and then run CTOACREQ program again to process them',1);
1246:
1247:
1248: loop
1249: fetch error_lines_cur into err_line_id;
1248: loop
1249: fetch error_lines_cur into err_line_id;
1250: exit when error_lines_cur%notfound;
1251:
1252: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Line Id:' || err_line_id, 1);
1253:
1254: end loop;
1255:
1256: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1252: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Line Id:' || err_line_id, 1);
1253:
1254: end loop;
1255:
1256: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1257: end;
1258: END IF;
1259: --Bugfix 10235354
1260:
1277: -- removed pg_debug check since messages need to be printed irrespective
1278: -- of debug level.
1279: EXCEPTION
1280: WHEN FND_API.G_EXC_ERROR THEN
1281: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1282: rollback to begin_line; --bug fix 3590305/3599860
1283: x_return_status := FND_API.G_RET_STS_ERROR;
1284: RETCODE := 2;
1285: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1284: RETCODE := 2;
1285: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1286:
1287: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1289: rollback to begin_line; --bug fix 3590305/3599860
1290: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1291: RETCODE := 2;
1292: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1291: RETCODE := 2;
1292: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1293:
1294: WHEN OTHERS THEN
1295: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1296: rollback to begin_line; --bug fix 3590305/3599860
1297: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1298: RETCODE := 2;
1299: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1358: FETCH delivery_location_cur INTO x_location_id;
1359: CLOSE delivery_location_cur;
1360:
1361: IF PG_DEBUG <> 0 THEN
1362: oe_debug_pub.add('populate_req_interface: ' || 'Location ID : '||to_Char(x_location_id),1);
1363: END IF;
1364:
1365: IF x_location_id IS NULL THEN
1366: RAISE No_Data_Found;
1381: EXCEPTION
1382:
1383: When no_data_found THEN
1384: IF PG_DEBUG <> 0 THEN
1385: oe_debug_pub.add('get_deliver_to_location_id: ' || 'exception No location_id found', 1);
1386: END IF;
1387: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1388:
1389: when FND_API.G_EXC_ERROR THEN
1387: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1388:
1389: when FND_API.G_EXC_ERROR THEN
1390: IF PG_DEBUG <> 0 THEN
1391: oe_debug_pub.add('get_deliver_to_location_id: ' || 'expected error: ' || sqlerrm, 1);
1392: END IF;
1393: x_return_status := FND_API.G_RET_STS_ERROR;
1394:
1395: when FND_API.G_EXC_UNEXPECTED_ERROR then
1393: x_return_status := FND_API.G_RET_STS_ERROR;
1394:
1395: when FND_API.G_EXC_UNEXPECTED_ERROR then
1396: IF PG_DEBUG <> 0 THEN
1397: oe_debug_pub.add('get_deliver_to_location_id: ' || 'unexpected error: ' || sqlerrm, 1);
1398: END IF;
1399: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1400:
1401: when others then
1399: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1400:
1401: when others then
1402: IF PG_DEBUG <> 0 THEN
1403: oe_debug_pub.add('get_deliver_to_location_id: ' || 'exception others: ' || sqlerrm, 1);
1404: END IF;
1405: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1406:
1407: End get_deliver_to_location_id;
1433: return p_price;
1434:
1435: -- lStmtNo := 10;
1436: -- IF PG_DEBUG <> 0 THEN
1437: -- oe_debug_pub.add('Entering convert_currency');
1438: -- oe_debug_pub.add('convert_currency::p_int_header_id' || p_int_header_id);
1439: -- oe_debug_pub.add('convert_currency::p_price' || p_price);
1440: -- END IF;
1441: --
1434:
1435: -- lStmtNo := 10;
1436: -- IF PG_DEBUG <> 0 THEN
1437: -- oe_debug_pub.add('Entering convert_currency');
1438: -- oe_debug_pub.add('convert_currency::p_int_header_id' || p_int_header_id);
1439: -- oe_debug_pub.add('convert_currency::p_price' || p_price);
1440: -- END IF;
1441: --
1442: -- --Get the functional currency for the OU
1435: -- lStmtNo := 10;
1436: -- IF PG_DEBUG <> 0 THEN
1437: -- oe_debug_pub.add('Entering convert_currency');
1438: -- oe_debug_pub.add('convert_currency::p_int_header_id' || p_int_header_id);
1439: -- oe_debug_pub.add('convert_currency::p_price' || p_price);
1440: -- END IF;
1441: --
1442: -- --Get the functional currency for the OU
1443: -- SELECT glb.currency_code,
1460: -- AND org_information_context = 'Operating Unit Information'
1461: -- AND TO_NUMBER(hoi.org_information3) = glb.set_of_books_id;
1462: --
1463: -- IF PG_DEBUG <> 0 THEN
1464: -- oe_debug_pub.add('convert_currency::p_from_currency:' || p_from_currency);
1465: -- oe_debug_pub.add('convert_currency::p_to_currency:' || p_to_currency);
1466: -- oe_debug_pub.add('convert_currency::p_org_id:' || p_org_id);
1467: -- oe_debug_pub.add('convert_currency::p_rate_type:' || p_rate_type);
1468: -- oe_debug_pub.add('convert_currency::p_rate:' || p_rate);
1461: -- AND TO_NUMBER(hoi.org_information3) = glb.set_of_books_id;
1462: --
1463: -- IF PG_DEBUG <> 0 THEN
1464: -- oe_debug_pub.add('convert_currency::p_from_currency:' || p_from_currency);
1465: -- oe_debug_pub.add('convert_currency::p_to_currency:' || p_to_currency);
1466: -- oe_debug_pub.add('convert_currency::p_org_id:' || p_org_id);
1467: -- oe_debug_pub.add('convert_currency::p_rate_type:' || p_rate_type);
1468: -- oe_debug_pub.add('convert_currency::p_rate:' || p_rate);
1469: -- oe_debug_pub.add('convert_currency::p_rate_date:' || p_rate_date);
1462: --
1463: -- IF PG_DEBUG <> 0 THEN
1464: -- oe_debug_pub.add('convert_currency::p_from_currency:' || p_from_currency);
1465: -- oe_debug_pub.add('convert_currency::p_to_currency:' || p_to_currency);
1466: -- oe_debug_pub.add('convert_currency::p_org_id:' || p_org_id);
1467: -- oe_debug_pub.add('convert_currency::p_rate_type:' || p_rate_type);
1468: -- oe_debug_pub.add('convert_currency::p_rate:' || p_rate);
1469: -- oe_debug_pub.add('convert_currency::p_rate_date:' || p_rate_date);
1470: -- END IF;
1463: -- IF PG_DEBUG <> 0 THEN
1464: -- oe_debug_pub.add('convert_currency::p_from_currency:' || p_from_currency);
1465: -- oe_debug_pub.add('convert_currency::p_to_currency:' || p_to_currency);
1466: -- oe_debug_pub.add('convert_currency::p_org_id:' || p_org_id);
1467: -- oe_debug_pub.add('convert_currency::p_rate_type:' || p_rate_type);
1468: -- oe_debug_pub.add('convert_currency::p_rate:' || p_rate);
1469: -- oe_debug_pub.add('convert_currency::p_rate_date:' || p_rate_date);
1470: -- END IF;
1471: --
1464: -- oe_debug_pub.add('convert_currency::p_from_currency:' || p_from_currency);
1465: -- oe_debug_pub.add('convert_currency::p_to_currency:' || p_to_currency);
1466: -- oe_debug_pub.add('convert_currency::p_org_id:' || p_org_id);
1467: -- oe_debug_pub.add('convert_currency::p_rate_type:' || p_rate_type);
1468: -- oe_debug_pub.add('convert_currency::p_rate:' || p_rate);
1469: -- oe_debug_pub.add('convert_currency::p_rate_date:' || p_rate_date);
1470: -- END IF;
1471: --
1472: -- lStmtNo := 20;
1465: -- oe_debug_pub.add('convert_currency::p_to_currency:' || p_to_currency);
1466: -- oe_debug_pub.add('convert_currency::p_org_id:' || p_org_id);
1467: -- oe_debug_pub.add('convert_currency::p_rate_type:' || p_rate_type);
1468: -- oe_debug_pub.add('convert_currency::p_rate:' || p_rate);
1469: -- oe_debug_pub.add('convert_currency::p_rate_date:' || p_rate_date);
1470: -- END IF;
1471: --
1472: -- lStmtNo := 20;
1473: --
1481: -- fnd_currency.get_info(p_to_currency, PRECISION, ext_precision, mau);
1482: -- mau := nvl(mau, power(10, (-1 * PRECISION )));
1483: --
1484: -- IF PG_DEBUG <> 0 THEN
1485: -- oe_debug_pub.add('convert_currency::precision:' || PRECISION);
1486: -- oe_debug_pub.add('convert_currency::ext_precision:' || ext_precision);
1487: -- oe_debug_pub.add('convert_currency::mau:' || mau);
1488: -- END IF;
1489: --
1482: -- mau := nvl(mau, power(10, (-1 * PRECISION )));
1483: --
1484: -- IF PG_DEBUG <> 0 THEN
1485: -- oe_debug_pub.add('convert_currency::precision:' || PRECISION);
1486: -- oe_debug_pub.add('convert_currency::ext_precision:' || ext_precision);
1487: -- oe_debug_pub.add('convert_currency::mau:' || mau);
1488: -- END IF;
1489: --
1490: -- IF p_from_currency <> p_to_currency THEN
1483: --
1484: -- IF PG_DEBUG <> 0 THEN
1485: -- oe_debug_pub.add('convert_currency::precision:' || PRECISION);
1486: -- oe_debug_pub.add('convert_currency::ext_precision:' || ext_precision);
1487: -- oe_debug_pub.add('convert_currency::mau:' || mau);
1488: -- END IF;
1489: --
1490: -- IF p_from_currency <> p_to_currency THEN
1491: -- lStmtNo := 30;
1489: --
1490: -- IF p_from_currency <> p_to_currency THEN
1491: -- lStmtNo := 30;
1492: -- IF PG_DEBUG <> 0 THEN
1493: -- oe_debug_pub.add('convert_currency::Going to convert.');
1494: -- END IF;
1495: -- --IF p_rate_type != 'USER' THEN
1496: -- --p_rate := GL_CURRENCY_API.get_rate(x_from_currency =>p_from_currency,
1497: -- -- x_to_currency =>p_to_currency,
1501: -- l_new_unit_price := round((p_price / p_rate) / mau) * mau;
1502: -- ELSE
1503: -- lStmtNo := 40;
1504: -- IF PG_DEBUG <> 0 THEN
1505: -- oe_debug_pub.add('convert_currency::Conversion is not needed.');
1506: -- END IF;
1507: -- l_new_unit_price := p_price;
1508: -- END IF;
1509: --
1507: -- l_new_unit_price := p_price;
1508: -- END IF;
1509: --
1510: -- IF PG_DEBUG <> 0 THEN
1511: -- oe_debug_pub.add('convert_currency::l_new_unit_price:' || l_new_unit_price);
1512: -- END IF;
1513: --
1514: -- return(l_new_unit_price);
1515: --
1515: --
1516: -- EXCEPTION
1517: -- WHEN OTHERS THEN
1518: -- IF PG_DEBUG <> 0 THEN
1519: -- oe_debug_pub.add('convert_currency::Others Exception at stmt:' || lStmtNo);
1520: -- oe_debug_pub.add('convert_currency::Error:' || sqlerrm);
1521: -- END IF;
1522: --
1523: -- return(p_price);
1516: -- EXCEPTION
1517: -- WHEN OTHERS THEN
1518: -- IF PG_DEBUG <> 0 THEN
1519: -- oe_debug_pub.add('convert_currency::Others Exception at stmt:' || lStmtNo);
1520: -- oe_debug_pub.add('convert_currency::Error:' || sqlerrm);
1521: -- END IF;
1522: --
1523: -- return(p_price);
1524:
1659: x_return_status);
1660:
1661: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1662: IF PG_DEBUG <> 0 THEN
1663: oe_debug_pub.add('populate_req_interface: ' || 'success from get_deliver_to_location_id' || ' l_location_id = ' || l_location_id, 1);
1664: END IF;
1665: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1666: IF PG_DEBUG <> 0 THEN
1667: oe_debug_pub.add('populate_req_interface: ' || 'expected error in get_deliver_to_location_id', 1);
1663: oe_debug_pub.add('populate_req_interface: ' || 'success from get_deliver_to_location_id' || ' l_location_id = ' || l_location_id, 1);
1664: END IF;
1665: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1666: IF PG_DEBUG <> 0 THEN
1667: oe_debug_pub.add('populate_req_interface: ' || 'expected error in get_deliver_to_location_id', 1);
1668: END IF;
1669: raise fnd_api.g_exc_error;
1670: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1671: IF PG_DEBUG <> 0 THEN
1668: END IF;
1669: raise fnd_api.g_exc_error;
1670: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1671: IF PG_DEBUG <> 0 THEN
1672: oe_debug_pub.add('populate_req_interface: ' || 'unexpected error in get_deliver_to_location_id', 1);
1673: END IF;
1674: raise fnd_api.g_exc_unexpected_error;
1675: END IF;
1676:
1683: l_stmt_num := 30;
1684: FOR e_id in emp_id (p_created_by) LOOP
1685: p_preparer_id := e_id.employee_id;
1686: IF PG_DEBUG <> 0 THEN
1687: oe_debug_pub.add('populate_req_interface: ' || 'Preparer ID : '||to_char(p_preparer_id),1);
1688: END IF;
1689: exit;
1690: END LOOP;
1691:
1695: IF p_preparer_id is null and p_interface_source_code in ('CTO','CTO-LOWER LEVEL') THEN
1696: p_preparer_id := oe_sys_parameters.value('ONT_EMP_ID_FOR_SS_ORDERS',p_org_id);
1697:
1698: IF PG_DEBUG <> 0 THEN
1699: oe_debug_pub.add('populate_req_interface: ' || 'Preparer ID (empIdforSSOrders): '||to_char(p_preparer_id),1);
1700: END IF;
1701: END IF;
1702:
1703:
1764:
1765: IF (l_result) THEN
1766:
1767: IF PG_DEBUG <> 0 THEN
1768: oe_debug_pub.add('success after call to FND_INSTALLATION.GET_APP_INFO',1);
1769: oe_debug_pub.add('returned custmer schema name for ONT =>'||l_customer_schema,1);
1770: END IF;
1771: ELSE
1772: null;
1765: IF (l_result) THEN
1766:
1767: IF PG_DEBUG <> 0 THEN
1768: oe_debug_pub.add('success after call to FND_INSTALLATION.GET_APP_INFO',1);
1769: oe_debug_pub.add('returned custmer schema name for ONT =>'||l_customer_schema,1);
1770: END IF;
1771: ELSE
1772: null;
1773: IF PG_DEBUG <> 0 THEN
1770: END IF;
1771: ELSE
1772: null;
1773: IF PG_DEBUG <> 0 THEN
1774: oe_debug_pub.add('FAILED IN call to FND_INSTALLATION.GET_APP_INFO',1);
1775: END IF;
1776:
1777: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1778:
1807: ||' AND msi.organization_id = :p_destination_org_id'
1808: ||' AND msi.allow_item_desc_update_flag = :pflag ';
1809:
1810: IF PG_DEBUG <> 0 THEN
1811: oe_debug_pub.add(sql_stmt,1);
1812: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1813: l_user_item_desc||'-BEFORE EXE IMM',1);
1814: END IF;
1815:
1808: ||' AND msi.allow_item_desc_update_flag = :pflag ';
1809:
1810: IF PG_DEBUG <> 0 THEN
1811: oe_debug_pub.add(sql_stmt,1);
1812: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1813: l_user_item_desc||'-BEFORE EXE IMM',1);
1814: END IF;
1815:
1816: l_stmt_num := 90;
1818: EXECUTE IMMEDIATE sql_stmt INTO l_user_item_desc
1819: USING p_interface_source_line_id,p_destination_org_id,pflag;
1820:
1821: IF PG_DEBUG <> 0 THEN
1822: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1823: l_user_item_desc||'-AFTER EXE IMM',1);
1824: oe_debug_pub.add('User Item Description is : ' || l_user_item_desc ,1);
1825: END IF;
1826: else
1820:
1821: IF PG_DEBUG <> 0 THEN
1822: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1823: l_user_item_desc||'-AFTER EXE IMM',1);
1824: oe_debug_pub.add('User Item Description is : ' || l_user_item_desc ,1);
1825: END IF;
1826: else
1827: l_user_item_desc := NULL;
1828:
1826: else
1827: l_user_item_desc := NULL;
1828:
1829: IF PG_DEBUG <> 0 THEN
1830: oe_debug_pub.add('OE_ORDER_LINES_ALL does not have column USER_ITEM_DESCRIPTION , l_chk_col: ' ||
1831: to_char(l_chk_col),1);
1832: END IF;
1833:
1834: end if;
1866:
1867: End;
1868:
1869: IF PG_DEBUG <> 0 THEN
1870: oe_debug_pub.add('Project _id and task_id are =>' ||l_project_id || 'and' || l_task_id);
1871: END IF;
1872:
1873: --IF p_interface_source_line_id is null THEN --bugfix 3042904
1874:
1952: and msi.bom_item_type = 4; --standard item
1953:
1954: Exception when others then
1955: IF PG_DEBUG <> 0 THEN
1956: oe_debug_pub.add('Revision not populated because '||SQLERRM);
1957: END IF;
1958: End;
1959: end if;
1960:
1958: End;
1959: end if;
1960:
1961: IF PG_DEBUG <> 0 THEN
1962: oe_debug_pub.add('Item Revision is ' ||l_item_revision);
1963: END IF;
1964: else
1965: If PG_DEBUG <> 0 Then
1966: oe_debug_pub.add('Popluate_req_interface: Inv Purchase by Revision is set to No or Source type is IR',1);
1962: oe_debug_pub.add('Item Revision is ' ||l_item_revision);
1963: END IF;
1964: else
1965: If PG_DEBUG <> 0 Then
1966: oe_debug_pub.add('Popluate_req_interface: Inv Purchase by Revision is set to No or Source type is IR',1);
1967: End if;
1968: end if;
1969: -- End bugfix 4068164
1970:
2001: -- account from rcv_paramters
2002: -- p_receiving_account_id := ch_act.receiving_account_id;
2003: p_receiving_account_id := ch_act.material_account;
2004: IF PG_DEBUG <> 0 THEN
2005: oe_debug_pub.add('populate_req_interface: ' || 'Charge Account ID : '||to_char(p_receiving_account_id),1);
2006: END IF;
2007: exit;
2008: END LOOP;
2009:
2021: ,x_return_status =>x_return_status);
2022: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2023:
2024: IF PG_DEBUG <> 0 THEN
2025: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(p_receiving_account_id),1);
2026: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
2027: END IF;
2028: ELSE
2029: IF PG_DEBUG <> 0 THEN
2022: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2023:
2024: IF PG_DEBUG <> 0 THEN
2025: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(p_receiving_account_id),1);
2026: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
2027: END IF;
2028: ELSE
2029: IF PG_DEBUG <> 0 THEN
2030: oe_debug_pub.add('populate_req_interface: ' || 'Get_opm_charge_account RET STATUS : '||x_return_status,1);
2026: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
2027: END IF;
2028: ELSE
2029: IF PG_DEBUG <> 0 THEN
2030: oe_debug_pub.add('populate_req_interface: ' || 'Get_opm_charge_account RET STATUS : '||x_return_status,1);
2031: END IF;
2032: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2033: END IF;
2034:
2130:
2131: EXCEPTION
2132: WHEN OTHERS THEN
2133: IF PG_DEBUG <> 0 THEN
2134: oe_debug_pub.add('populate_req_interface: ' ||
2135: 'insert into the req interface table failed interface_source_line_id'||
2136: to_char(p_interface_source_line_id),1);
2137:
2138: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error:: In the insert statment::'||
2134: oe_debug_pub.add('populate_req_interface: ' ||
2135: 'insert into the req interface table failed interface_source_line_id'||
2136: to_char(p_interface_source_line_id),1);
2137:
2138: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error:: In the insert statment::'||
2139: to_char(l_stmt_num)||'::'||sqlerrm,1);
2140: END IF;
2141: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2142: END;
2144:
2145: EXCEPTION
2146: WHEN FND_API.G_EXC_ERROR THEN
2147: IF PG_DEBUG <> 0 THEN
2148: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
2149: END IF;
2150: x_return_status := FND_API.G_RET_STS_ERROR;
2151: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2152: IF PG_DEBUG <> 0 THEN
2149: END IF;
2150: x_return_status := FND_API.G_RET_STS_ERROR;
2151: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2152: IF PG_DEBUG <> 0 THEN
2153: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
2154: END IF;
2155: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2156: WHEN OTHERS THEN
2157: IF PG_DEBUG <> 0 THEN
2154: END IF;
2155: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2156: WHEN OTHERS THEN
2157: IF PG_DEBUG <> 0 THEN
2158: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::other error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
2159: END IF;
2160: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2161: END populate_req_interface;
2162:
2191: Return (v_rsv_quantity);
2192: EXCEPTION
2193: WHEN OTHERS THEN
2194: IF PG_DEBUG <> 0 THEN
2195: oe_debug_pub.add('get_reserved_qty: ' || 'GET_RESERVED_QTY::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
2196: END IF;
2197: Return (0);
2198: END; -- get_reserved_qty
2199:
2327: from oe_order_lines_all oel
2328: where oel.line_id = p_interface_source_line_id;
2329:
2330: IF PG_DEBUG <> 0 THEN
2331: oe_debug_pub.add('get_new_order_qty: ' || 'The Order Line Shipped Quantity: '|| to_char(l_oel_shipped_qty));
2332: END IF;
2333:
2334: if l_oel_shipped_qty > 0 then
2335: return(0);
2339: p_po_quantity := nvl(p_order_qty,0) - nvl(v_rsv_quantity,0) - nvl(l_quantity_interface,0)-nvl(l_open_flow_qty,0);
2340:
2341: -- Log the quantities.
2342: IF PG_DEBUG <> 0 THEN
2343: oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
2344:
2345: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
2346:
2347: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
2341: -- Log the quantities.
2342: IF PG_DEBUG <> 0 THEN
2343: oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
2344:
2345: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
2346:
2347: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
2348:
2349: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
2343: oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
2344:
2345: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
2346:
2347: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
2348:
2349: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
2350:
2351: oe_debug_pub.add('get_new_order_qty: ' || 'The Order Line Shipped Quantity: '|| to_char(l_oel_shipped_qty));
2345: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
2346:
2347: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
2348:
2349: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
2350:
2351: oe_debug_pub.add('get_new_order_qty: ' || 'The Order Line Shipped Quantity: '|| to_char(l_oel_shipped_qty));
2352:
2353: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
2347: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
2348:
2349: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
2350:
2351: oe_debug_pub.add('get_new_order_qty: ' || 'The Order Line Shipped Quantity: '|| to_char(l_oel_shipped_qty));
2352:
2353: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
2354:
2355: oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
2349: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
2350:
2351: oe_debug_pub.add('get_new_order_qty: ' || 'The Order Line Shipped Quantity: '|| to_char(l_oel_shipped_qty));
2352:
2353: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
2354:
2355: oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
2356: END IF;
2357:
2351: oe_debug_pub.add('get_new_order_qty: ' || 'The Order Line Shipped Quantity: '|| to_char(l_oel_shipped_qty));
2352:
2353: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
2354:
2355: oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
2356: END IF;
2357:
2358: RETURN nvl(p_po_quantity, 0);
2359:
2561: x_return_status := FND_API.G_RET_STS_SUCCESS;
2562: lStmtNumber := 10;
2563: g_pg_level := 3;
2564: IF PG_DEBUG <> 0 THEN
2565: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2566: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2567: || '******************************',1);
2568:
2569: oe_debug_pub.add('Create_Purchasing_Doc: '
2562: lStmtNumber := 10;
2563: g_pg_level := 3;
2564: IF PG_DEBUG <> 0 THEN
2565: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2566: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2567: || '******************************',1);
2568:
2569: oe_debug_pub.add('Create_Purchasing_Doc: '
2570: || ' CREATING PURCHASING DOCUMENT ',1);
2565: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2566: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2567: || '******************************',1);
2568:
2569: oe_debug_pub.add('Create_Purchasing_Doc: '
2570: || ' CREATING PURCHASING DOCUMENT ',1);
2571: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2572:
2573: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2567: || '******************************',1);
2568:
2569: oe_debug_pub.add('Create_Purchasing_Doc: '
2570: || ' CREATING PURCHASING DOCUMENT ',1);
2571: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2572:
2573: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2574: || '******************************',1);
2575: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2569: oe_debug_pub.add('Create_Purchasing_Doc: '
2570: || ' CREATING PURCHASING DOCUMENT ',1);
2571: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2572:
2573: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2574: || '******************************',1);
2575: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2576: END IF;
2577:
2571: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2572:
2573: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2574: || '******************************',1);
2575: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2576: END IF;
2577:
2578: IF PG_DEBUG <> 0 THEN
2579: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2575: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2576: END IF;
2577:
2578: IF PG_DEBUG <> 0 THEN
2579: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2580: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2581: END IF;
2582: lStmtNumber := 20;
2583: If Pg_Debug <> 0 Then
2576: END IF;
2577:
2578: IF PG_DEBUG <> 0 THEN
2579: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2580: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2581: END IF;
2582: lStmtNumber := 20;
2583: If Pg_Debug <> 0 Then
2584: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create Purchasing Doc: Before calling get_config_details',5);
2580: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2581: END IF;
2582: lStmtNumber := 20;
2583: If Pg_Debug <> 0 Then
2584: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create Purchasing Doc: Before calling get_config_details',5);
2585: End if;
2586:
2587:
2588: l_Comp_exists := 'N';
2600: Loop
2601:
2602: l_comp_exists := 'Y';
2603: if PG_DEBUG <> 0 Then
2604: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Processing config item = '
2605: ||buy_configs_rec.component_item_id,5);
2606: End if;
2607: lStmtNumber := 40;
2608:
2639: Exception When no_data_found then
2640: l_comp_exists := 'N';
2641: End;
2642: If PG_DEBUG <> 0 Then
2643: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Processing config item = '
2644: ||p_config_item_id,5);
2645: End if;
2646: lStmtNumber := 60;
2647:
2671:
2672: lStmtNumber := 80;
2673:
2674: If PG_DEBUG <> 0 Then
2675: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Before calling Submit Pdoi',5);
2676: End if;
2677:
2678: Submit_pdoi_conc_prog(
2679: p_oper_unit_list => x_oper_unit_list,
2687:
2688: end if; /* l_comp_exits = 'Y' */
2689:
2690: IF PG_DEBUG <> 0 THEN
2691: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2692: || '****************************',1);
2693:
2694: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2695: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2690: IF PG_DEBUG <> 0 THEN
2691: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2692: || '****************************',1);
2693:
2694: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2695: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2696:
2697: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2698: || '****************************',1);
2691: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2692: || '****************************',1);
2693:
2694: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2695: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2696:
2697: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2698: || '****************************',1);
2699: END IF;
2693:
2694: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2695: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2696:
2697: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2698: || '****************************',1);
2699: END IF;
2700: g_pg_level := g_pg_level - 3;
2701:
2702: exception
2703:
2704: when FND_API.G_EXC_UNEXPECTED_ERROR then
2705: IF PG_DEBUG <> 0 THEN
2706: oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::unexp error::'||lStmtNumber||sqlerrm,1);
2707: END IF;
2708: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2709: CTO_MSG_PUB.Count_And_Get
2710: (p_msg_count => x_msg_count
2712: );
2713:
2714: when FND_API.G_EXC_ERROR then
2715: IF PG_DEBUG <> 0 THEN
2716: oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::exp error::'||lStmtNumber||sqlerrm,1);
2717: END IF;
2718: x_return_status := FND_API.G_RET_STS_ERROR;
2719: CTO_MSG_PUB.Count_And_Get
2720: (p_msg_count => x_msg_count
2721: ,p_msg_data => x_msg_data);
2722:
2723: when others then
2724: IF PG_DEBUG <> 0 THEN
2725: oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::others::'||lStmtNumber||sqlerrm,1);
2726: END IF;
2727: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2728: CTO_MSG_PUB.Count_And_Get
2729: (p_msg_count => x_msg_count
2818: lStmtNumber := 10;
2819:
2820: x_rolled_price := 0;
2821: If PG_DEBUG <> 0 Then
2822: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2823: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2824: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2825: End if;
2826:
2819:
2820: x_rolled_price := 0;
2821: If PG_DEBUG <> 0 Then
2822: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2823: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2824: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2825: End if;
2826:
2827: lStmtNumber := 20;
2820: x_rolled_price := 0;
2821: If PG_DEBUG <> 0 Then
2822: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2823: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2824: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2825: End if;
2826:
2827: lStmtNumber := 20;
2828: For pur_comp in purchase_comp
2828: For pur_comp in purchase_comp
2829: Loop
2830:
2831: IF PG_DEBUG <> 0 THEN
2832: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' || 'Component item id = '
2833: ||to_char(pur_comp.comp_item_id),2);
2834: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price per unit = '||pur_comp.list_price_per_unit,5);
2835: END IF;
2836:
2830:
2831: IF PG_DEBUG <> 0 THEN
2832: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' || 'Component item id = '
2833: ||to_char(pur_comp.comp_item_id),2);
2834: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price per unit = '||pur_comp.list_price_per_unit,5);
2835: END IF;
2836:
2837: lStmtNumber := 30;
2838:
2847: quantity => pur_comp.comp_qty,
2848: item_id => pur_comp.comp_item_id);
2849: l_price := l_prim_uom_qty * pur_comp.list_price_per_unit;
2850: If PG_DEBUG <> 0 Then
2851: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Primary UOM quantity = '||l_prim_uom_qty,5);
2852: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price for order qty = '||l_price,5);
2853: End if;
2854: x_rolled_price := x_rolled_price + l_price;
2855: else
2848: item_id => pur_comp.comp_item_id);
2849: l_price := l_prim_uom_qty * pur_comp.list_price_per_unit;
2850: If PG_DEBUG <> 0 Then
2851: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Primary UOM quantity = '||l_prim_uom_qty,5);
2852: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price for order qty = '||l_price,5);
2853: End if;
2854: x_rolled_price := x_rolled_price + l_price;
2855: else
2856: IF PG_DEBUG <> 0 THEN
2853: End if;
2854: x_rolled_price := x_rolled_price + l_price;
2855: else
2856: IF PG_DEBUG <> 0 THEN
2857: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price is defined as 0',5);
2858: END IF;
2859:
2860: end if;
2861:
2860: end if;
2861:
2862: lStmtNumber := 50;
2863: If PG_DEBUG <> 0 Then
2864: oe_debug_pub.add(lpad(' ',g_pg_level)
2865: ||'ROLLUP_LIST_PRICE : Get child configs rollup price, which are not enabled in this org',5);
2866: end if;
2867:
2868: For child_configs in child_configs_cur
2867:
2868: For child_configs in child_configs_cur
2869: Loop
2870: If PG_DEBUG <> 0 Then
2871: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Processing Child config = '
2872: ||child_configs.comp_item_id,5);
2873: End if;
2874: lStmtNumber := 60;
2875: Rollup_list_price (
2883:
2884: lStmtNumber := 70;
2885: x_rolled_price := x_rolled_price + l_rolled_price*child_configs.comp_qty; /* Renga need to add qty */
2886: IF PG_DEBUG <> 0 Then
2887: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Configs rolled up price = '||l_rolled_price,5);
2888: End if;
2889: End Loop;
2890: End Loop;
2891:
2890: End Loop;
2891:
2892:
2893: IF PG_DEBUG <> 0 THEN
2894: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up organization = '||to_char(p_org_id),2);
2895: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up price = '
2896: ||to_char(x_rolled_price),2);
2897: END IF;
2898: g_pg_level := g_pg_level - 3;
2891:
2892:
2893: IF PG_DEBUG <> 0 THEN
2894: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up organization = '||to_char(p_org_id),2);
2895: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up price = '
2896: ||to_char(x_rolled_price),2);
2897: END IF;
2898: g_pg_level := g_pg_level - 3;
2899: exception
2899: exception
2900:
2901: when FND_API.G_EXC_UNEXPECTED_ERROR then
2902: IF PG_DEBUG <> 0 THEN
2903: oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::unexp error::'||lStmtNumber||sqlerrm,1);
2904: END IF;
2905: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2906: CTO_MSG_PUB.Count_And_Get
2907: (p_msg_count => x_msg_count
2910: g_pg_level := g_pg_level - 3;
2911:
2912: when FND_API.G_EXC_ERROR then
2913: IF PG_DEBUG <> 0 THEN
2914: oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::exp error::'||lStmtNumber||sqlerrm,1);
2915: END IF;
2916: x_return_status := FND_API.G_RET_STS_ERROR;
2917: CTO_MSG_PUB.Count_And_Get
2918: (p_msg_count => x_msg_count
2920: g_pg_level := g_pg_level - 3;
2921:
2922: when others then
2923: IF PG_DEBUG <> 0 THEN
2924: oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::others::'||lStmtNumber||sqlerrm,1);
2925: END IF;
2926: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2927: CTO_MSG_PUB.Count_And_Get
2928: (p_msg_count => x_msg_count
2995: lStmtNumber := 10;
2996:
2997: g_pg_level := g_pg_level + 3;
2998: If pg_debug <> 0 then
2999: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Inside Rollup Purchase Price API',1);
3000: End if;
3001: select base_item_id,
3002: config_orgs
3003: into l_model_item_id,
3005: from mtl_system_items
3006: where inventory_item_id = p_config_item_id
3007: and rownum =1;
3008: IF PG_DEBUG <> 0 THEN
3009: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
3010: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: '||'Model item id = '||l_model_item_id,1);
3011: END IF;
3012:
3013:
3006: where inventory_item_id = p_config_item_id
3007: and rownum =1;
3008: IF PG_DEBUG <> 0 THEN
3009: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
3010: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: '||'Model item id = '||l_model_item_id,1);
3011: END IF;
3012:
3013:
3014: -- Get the vendor and vendor site information from
3026: x_msg_data => x_msg_data);
3027:
3028: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3029: IF PG_DEBUG <> 0 THEN
3030: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Expected Error in Get_all_item_asl.',1);
3031: END IF;
3032: raise FND_API.G_EXC_ERROR;
3033:
3034: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3032: raise FND_API.G_EXC_ERROR;
3033:
3034: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3035: IF PG_DEBUG <> 0 THEN
3036: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: UnExpected Error in Get_all_item_asl.',1);
3037: END IF;
3038: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3039:
3040: END IF;
3041:
3042: lStmtNumber := 40;
3043: if l_model_vendors.count = 0 then
3044: IF PG_DEBUG <> 0 THEN
3045: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: No ASL defined for model...',5);
3046: END IF;
3047: g_pg_level := g_pg_level - 3;
3048: return;
3049: end if;
3048: return;
3049: end if;
3050:
3051: IF PG_DEBUG <> 0 THEN
3052: oe_debug_pub.add('ROLLUP_PURCHASE_PRICE: ' || 'Calling get_all_item_asl for config..',1);
3053: END IF;
3054: -- Get all the ASL's Defined for Config item (You may have some
3055: -- in the case of matching)
3056: lstmtNumber := 50;
3065:
3066:
3067: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3068: IF PG_DEBUG <> 0 THEN
3069: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Expected Error in Get_all_item_asl.',1);
3070: END IF;
3071: raise FND_API.G_EXC_ERROR;
3072:
3073: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3071: raise FND_API.G_EXC_ERROR;
3072:
3073: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3074: IF PG_DEBUG <> 0 THEN
3075: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: UnExpected Error in Get_all_item_asl.',1);
3076: END IF;
3077: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3078:
3079: END IF;
3079: END IF;
3080:
3081:
3082: IF PG_DEBUG <> 0 THEN
3083: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Started looping....',5);
3084: END IF;
3085: Reduce_vendor_by_ou(
3086: p_vendor_details => l_model_vendors,
3087: p_config_item_id => p_config_item_id,
3091: -- x_model_vendors := l_model_vendors;
3092: i := x_model_vendors.first;
3093:
3094: if PG_DEBUG <> 0 then
3095: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: BEGIN ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
3096: end if;
3097:
3098: -- Start processing each vendor and vendor sites for th model ASL
3099: lStmtNumber := 60;
3099: lStmtNumber := 60;
3100: while (i is not null)
3101: loop
3102: IF PG_DEBUG <> 0 THEN
3103: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3104: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
3105: ||to_char(x_model_vendors(i).vendor_id),5);
3106:
3107: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
3100: while (i is not null)
3101: loop
3102: IF PG_DEBUG <> 0 THEN
3103: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3104: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
3105: ||to_char(x_model_vendors(i).vendor_id),5);
3106:
3107: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
3108: ||to_char(x_model_vendors(i).vendor_site_id),1);
3103: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3104: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
3105: ||to_char(x_model_vendors(i).vendor_id),5);
3106:
3107: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
3108: ||to_char(x_model_vendors(i).vendor_site_id),1);
3109:
3110: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
3111: ||to_char(x_model_vendors(i).asl_id),1);
3106:
3107: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
3108: ||to_char(x_model_vendors(i).vendor_site_id),1);
3109:
3110: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
3111: ||to_char(x_model_vendors(i).asl_id),1);
3112:
3113: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Index variable ='||to_char(i),1);
3114: END IF;
3109:
3110: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
3111: ||to_char(x_model_vendors(i).asl_id),1);
3112:
3113: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Index variable ='||to_char(i),1);
3114: END IF;
3115: -- CAll the custom API with Vendor and Vendor site information
3116: -- If the custom API returns TRUE we should not do anything, It is assumed
3117: -- That the custom API would have taken care of everything. IN this case we will skip this
3123: p_item_id => p_config_item_id,
3124: p_vendor_id => x_model_vendors(i).vendor_id,
3125: p_vendor_site_id => x_model_vendors(i).vendor_site_id) then
3126: IF PG_DEBUG <> 0 THEN
3127: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Custom API returned value...',1);
3128: END IF;
3129: else
3130:
3131: l_purchase_uom := x_model_vendors(i).Purchasing_uom;
3146: x_index => x_index);
3147:
3148: if l_doc_exists then
3149: IF PG_DEBUG <> 0 THEN
3150: oe_debug_pub.add(lpad(' ',g_pg_level)
3151: ||'ROLLUP_PURCHASE_PRICE: ASL exists for blanket, checking to see the blanket..',5);
3152: END IF;
3153:
3154: l_doc_line_id := null;
3181: x_msg_data => x_msg_data);
3182:
3183: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3184: IF PG_DEBUG <> 0 THEN
3185: oe_debug_pub.add(lpad(' ',g_pg_level)
3186: ||'ROLLUP_PURCHASE_PRICE: Expected Error in Blanket_Document_sourcing.',1);
3187: END IF;
3188: raise FND_API.G_EXC_ERROR;
3189:
3188: raise FND_API.G_EXC_ERROR;
3189:
3190: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3191: IF PG_DEBUG <> 0 THEN
3192: oe_debug_pub.add(lpad(' ',g_pg_level)
3193: ||'ROLLUP_PURCHASE_PRICE: UnExpected Error in blanket_document_sourcing.',1);
3194: END IF;
3195: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3196:
3198:
3199: lStmtNumber := 100;
3200: If l_doc_return = 'Y' then
3201: IF PG_DEBUG <> 0 THEN
3202: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Valid Blanket found for config ..',2);
3203: END IF;
3204: l_doc_exists := TRUE;
3205: else
3206: IF PG_DEBUG <> 0 THEN
3203: END IF;
3204: l_doc_exists := TRUE;
3205: else
3206: IF PG_DEBUG <> 0 THEN
3207: oe_debug_pub.add(lpad(' ',g_pg_level)
3208: ||'ROLLUP_PURCHASE_PRICE: Valid Blanket not found for this config',2);
3209: END IF;
3210: l_doc_exists := FALSE;
3211: end if;
3214:
3215: lStmtNumber := 110;
3216: if l_doc_exists then
3217: IF PG_DEBUG <> 0 THEN
3218: oe_debug_pub.add(lpad(' ',g_pg_level)
3219: ||'ROLLUP_PURCHAE_PRICE: Valid Asl blanket already exists for configuration....',2);
3220:
3221: END IF;
3222: elsif x_model_vendors(i).vendor_site_id is null then
3220:
3221: END IF;
3222: elsif x_model_vendors(i).vendor_site_id is null then
3223: IF PG_DEBUG <> 0 THEN
3224: oe_debug_pub.add(lpad(' ',g_pg_level)
3225: ||'ROLLUP_PURCHASE_PRICE: Vendor site id is null need not process..',2);
3226: END IF;
3227: else
3228: l_doc_line_id := null;
3254:
3255:
3256: IF( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3257: IF PG_DEBUG <> 0 THEN
3258: oe_debug_pub.add(lpad(' ',g_pg_level)
3259: ||'success status false for po_autosource_sv.blanket_document_sourcing...',1);
3260:
3261: END IF;
3262:
3266: lStmtNumber := 130;
3267:
3268: If l_doc_return = 'N' then
3269: IF PG_DEBUG <> 0 THEN
3270: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHAES_PRICE: No blankets returned..',2);
3271: END IF;
3272: else
3273: IF PG_DEBUG <> 0 THEN
3274: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
3270: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHAES_PRICE: No blankets returned..',2);
3271: END IF;
3272: else
3273: IF PG_DEBUG <> 0 THEN
3274: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
3275: ||to_char(l_doc_line_id),2);
3276:
3277: oe_debug_pub.add(lpad(' ',g_pg_level)
3278: ||'ROLLUP_PURCHASE_PRICE: ROLLUP_PURCHASE_PRICE: Blanket doc header id ='
3273: IF PG_DEBUG <> 0 THEN
3274: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
3275: ||to_char(l_doc_line_id),2);
3276:
3277: oe_debug_pub.add(lpad(' ',g_pg_level)
3278: ||'ROLLUP_PURCHASE_PRICE: ROLLUP_PURCHASE_PRICE: Blanket doc header id ='
3279: ||to_char(l_doc_header_id),2);
3280: END IF;
3281:
3284: p_doc_header_id => l_doc_header_id)
3285: then
3286:
3287: IF PG_DEBUG <> 0 THEN
3288: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE '
3289: || 'Config line already exists in blanket..',2);
3290: END IF;
3291: else
3292: lStmtNumber := 140;
3303: and fsp.org_id = poh.org_id);
3304: Exception when no_data_found then
3305: l_config_exists := 'N';
3306: if pg_debug <> 0 Then
3307: oe_debug_pub.add(lpad(' ',g_pg_level)
3308: ||'ROLLUP_PURCHASE_PRICE: Config item does not exist in Po validation org. ',5);
3309: End if;
3310: End;
3311:
3321: x_return_status => x_return_status,
3322: x_msg_count => x_msg_count,
3323: x_msg_data => x_msg_data);
3324:
3325: oe_debug_pub.add('Rolled up blanket price =
3326: '||x_rolled_price);
3327:
3328: lStmtNumber := 160;
3329:
3343: where inventory_item_id = p_config_item_id
3344: and rownum = 1;
3345:
3346: IF PG_DEBUG <> 0 THEN
3347: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Item Name ='||x_segment1,2);
3348: END IF;
3349: lStmtNumber := 180;
3350: Derive_start_end_date(
3351: p_item_id => p_config_item_id,
3371: -- Record the operating unit in a table
3372: -- This tbale is used for launching the concurrent program
3373:
3374: IF PG_DEBUG <> 0 THEN
3375: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket Operating unit = '
3376: ||to_char(x_org_id),2);
3377: END IF;
3378:
3379: -- We should take the OU from Blanket instead of rcv org OU.
3395:
3396: end loop;
3397:
3398: if PG_DEBUG <> 0 then
3399: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3400: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: END ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
3401: end if;
3402:
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3396: end loop;
3397:
3398: if PG_DEBUG <> 0 then
3399: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3400: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: END ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
3401: end if;
3402:
3403: x_return_status := FND_API.G_RET_STS_SUCCESS;
3404: g_pg_level := g_pg_level - 3;
3406: exception
3407:
3408: when FND_API.G_EXC_UNEXPECTED_ERROR then
3409: IF PG_DEBUG <> 0 THEN
3410: oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::unexp error::'||lStmtNumber||sqlerrm,1);
3411: END IF;
3412: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3413: CTO_MSG_PUB.Count_And_Get
3414: (p_msg_count => x_msg_count
3416: );
3417:
3418: when FND_API.G_EXC_ERROR then
3419: IF PG_DEBUG <> 0 THEN
3420: oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::exp error::'||lStmtNumber||sqlerrm,1);
3421: END IF;
3422: x_return_status := FND_API.G_RET_STS_ERROR;
3423: CTO_MSG_PUB.Count_And_Get
3424: (p_msg_count => x_msg_count
3425: ,p_msg_data => x_msg_data);
3426:
3427: when others then
3428: IF PG_DEBUG <> 0 THEN
3429: oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::others::'||lStmtNumber||sqlerrm,1);
3430: END IF;
3431: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3432: CTO_MSG_PUB.Count_And_Get
3433: (p_msg_count => x_msg_count
3497: when no_data_found then
3498: return False;
3499: when others then
3500: IF PG_DEBUG <> 0 THEN
3501: oe_debug_pub.add('config_exists_in_blanket: ' || 'When others error occured in sql ..',1);
3502: END IF;
3503:
3504: end config_exists_in_blanket;
3505:
3557: from mtl_system_items
3558: where inventory_item_id = p_config_item_id
3559: and rownum = 1;
3560:
3561: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3562: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Po doc line id = '||p_doc_line_id,5);
3563:
3564: End if;
3565:
3558: where inventory_item_id = p_config_item_id
3559: and rownum = 1;
3560:
3561: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3562: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Po doc line id = '||p_doc_line_id,5);
3563:
3564: End if;
3565:
3566: For buy_comps in buy_comps_cur
3645: End;
3646: x_rolled_price := nvl(x_rolled_price,0) + l_unit_price;
3647:
3648: IF PG_DEBUG <> 0 THEN
3649: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Item Id = '|| buy_comps.comp_item_id,1);
3650:
3651: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Blanket price ='|| to_char(l_unit_price),1);
3652: END IF;
3653:
3647:
3648: IF PG_DEBUG <> 0 THEN
3649: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Item Id = '|| buy_comps.comp_item_id,1);
3650:
3651: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Blanket price ='|| to_char(l_unit_price),1);
3652: END IF;
3653:
3654: End loop; /* Buy_comps */
3655:
3680: p_batch_id := x_int_header_id;
3681: end if;
3682:
3683: IF PG_DEBUG <> 0 THEN
3684: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Interface header id ='||to_char(x_int_header_id),1);
3685: END IF;
3686:
3687:
3688: Insert into Po_headers_interface(
3757: where po_header_id = p_doc_header_id;
3758:
3759:
3760: IF PG_DEBUG <> 0 THEN
3761: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'No of records inserted in headers = '||to_char(sql%rowcount),1);
3762:
3763: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Operating unit for the Blanket Doc = '||to_char(x_org_id),1);
3764: END IF;
3765:
3759:
3760: IF PG_DEBUG <> 0 THEN
3761: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'No of records inserted in headers = '||to_char(sql%rowcount),1);
3762:
3763: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Operating unit for the Blanket Doc = '||to_char(x_org_id),1);
3764: END IF;
3765:
3766: g_pg_level := g_pg_level - 3;
3767:
3800:
3801:
3802:
3803: IF PG_DEBUG <> 0 THEN
3804: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3805:
3806: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3807:
3808: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3802:
3803: IF PG_DEBUG <> 0 THEN
3804: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3805:
3806: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3807:
3808: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3809:
3810: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Calling convert_currency routine');
3804: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3805:
3806: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3807:
3808: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3809:
3810: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Calling convert_currency routine');
3811: END IF;
3812:
3806: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3807:
3808: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3809:
3810: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Calling convert_currency routine');
3811: END IF;
3812:
3813: --Begin Bugfix 10145427
3814: l_new_price := convert_currency(p_int_header_id => p_int_header_id,
3814: l_new_price := convert_currency(p_int_header_id => p_int_header_id,
3815: p_price => p_price);
3816:
3817: IF PG_DEBUG <> 0 THEN
3818: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line::l_new_price = '|| l_new_price);
3819: END IF;
3820:
3821: -- Bug fix 3589150
3822: -- Done by Renga Kannan on 04/30/04
3892: where pol.po_line_id =p_doc_line_id
3893: and poh.po_header_id = pol.po_header_id;
3894:
3895: IF PG_DEBUG <> 0 THEN
3896: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in lines = '||to_char(sql%rowcount),1);
3897: END IF;
3898:
3899: -- Insert the rows from po_line_locations_all
3900:
3995: where pol.po_line_id = p_doc_line_id
3996: and pol.po_line_id = poll.po_line_id;
3997:
3998: IF PG_DEBUG <> 0 THEN
3999: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in locations = '||to_char(sql%rowcount),1);
4000: END IF;
4001:
4002: g_pg_level := g_pg_level - 3;
4003:
4064: -- from mtl system items. Both these attributes
4065: -- in master org level, hence u can get the attribute
4066: -- from any organization.
4067:
4068: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
4069:
4070: Select option_specific_sourced
4071: into l_option_specific
4072: from mtl_system_items
4079: from bom_cto_order_lines
4080: where line_id = p_line_id;
4081: End if;
4082:
4083: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
4084: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
4085: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
4086:
4087: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
4080: where line_id = p_line_id;
4081: End if;
4082:
4083: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
4084: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
4085: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
4086:
4087: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
4088: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
4081: End if;
4082:
4083: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
4084: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
4085: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
4086:
4087: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
4088: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
4089:
4084: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
4085: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
4086:
4087: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
4088: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
4089:
4090: For oper_unit_rec in oper_unit_cur
4091: Loop
4092: l_oper_unit_list(oper_unit_rec.organization_id) := oper_unit_rec.organization_id;
4104:
4105: End if;
4106:
4107: If l_oper_unit_list.exists(l_oper_unit) then
4108: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is part of the sourcing chain',1);
4109: x_vendor_details(j) := p_vendor_details(i);
4110: j := j + 1;
4111: else
4112: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is not part of the sourcing chain',1);
4108: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is part of the sourcing chain',1);
4109: x_vendor_details(j) := p_vendor_details(i);
4110: j := j + 1;
4111: else
4112: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is not part of the sourcing chain',1);
4113: End if;
4114: i := p_vendor_details.next(i);
4115: End Loop;
4116: Else
4117: -- Added for MOAC
4118: -- Reducing records which are not having vendor site
4119:
4120: If PG_DEBUG <> 0 Then
4121: oe_debug_pub.add('Reduce_vendor_by_ou: cib based on model', 3);
4122: End if;
4123:
4124: i := p_vendor_details.first;
4125:
4129: x_vendor_details(j) := p_vendor_details(i);
4130: j := j + 1;
4131: else
4132: If PG_DEBUG <> 0 Then
4133: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor='
4134: ||p_vendor_details(i).vendor_id||' as the vendor site is null',3);
4135: End if;
4136: end if;
4137: i := p_vendor_details.next(i);
4139: -- End of MOAC change
4140: End if;
4141:
4142: If nvl(l_option_specific,3) in (1,2) then
4143: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors by OSS vendors',1);
4144:
4145: For oss_vendor_rec in oss_vendor_cur(l_assg_set_id)
4146: Loop
4147: l_vendor_list(oss_vendor_rec.vendor_id) := oss_vendor_rec.vendor_id;
4152: While (i is not null)
4153: Loop
4154: If l_vendor_list.exists(x_vendor_details(i).vendor_id)
4155: and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
4156: oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
4157: Else
4158: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
4159: oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
4160: x_vendor_details.delete(i);
4154: If l_vendor_list.exists(x_vendor_details(i).vendor_id)
4155: and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
4156: oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
4157: Else
4158: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
4159: oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
4160: x_vendor_details.delete(i);
4161: End if;
4162: i := x_vendor_details.next(i);
4155: and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
4156: oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
4157: Else
4158: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
4159: oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
4160: x_vendor_details.delete(i);
4161: End if;
4162: i := x_vendor_details.next(i);
4163: End Loop;
4338:
4339: -- for all the sales order lines (entered, booked )
4340: -- Given parameters.
4341: IF PG_DEBUG <> 0 THEN
4342: oe_debug_pub.add('Create_purchase_doc_batch: '
4343: || '+---------------------------------------------------------------------------+',1);
4344:
4345: oe_debug_pub.add('Create_purchase_doc_batch: '
4346: || '+------------------ Parameters passed into the procedure ------------------+',1);
4341: IF PG_DEBUG <> 0 THEN
4342: oe_debug_pub.add('Create_purchase_doc_batch: '
4343: || '+---------------------------------------------------------------------------+',1);
4344:
4345: oe_debug_pub.add('Create_purchase_doc_batch: '
4346: || '+------------------ Parameters passed into the procedure ------------------+',1);
4347:
4348: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4344:
4345: oe_debug_pub.add('Create_purchase_doc_batch: '
4346: || '+------------------ Parameters passed into the procedure ------------------+',1);
4347:
4348: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4345: oe_debug_pub.add('Create_purchase_doc_batch: '
4346: || '+------------------ Parameters passed into the procedure ------------------+',1);
4347:
4348: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4346: || '+------------------ Parameters passed into the procedure ------------------+',1);
4347:
4348: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4347:
4348: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4348: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4349: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4350: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4358: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
4351: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4358: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
4359:
4352: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4358: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
4359:
4360: oe_debug_pub.add('Create_purchase_doc_batch: '
4353: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4358: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
4359:
4360: oe_debug_pub.add('Create_purchase_doc_batch: '
4361: || '+---------------------------------------------------------------------------+',1);
4354: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
4355: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4358: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
4359:
4360: oe_debug_pub.add('Create_purchase_doc_batch: '
4361: || '+---------------------------------------------------------------------------+',1);
4362: END IF;
4356: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
4358: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
4359:
4360: oe_debug_pub.add('Create_purchase_doc_batch: '
4361: || '+---------------------------------------------------------------------------+',1);
4362: END IF;
4363:
4364: drive_mark := 0;
4368:
4369: /* sales order number or line id or offset is NOT passed */
4370:
4371: IF PG_DEBUG <> 0 THEN
4372: oe_debug_pub.add('Create_purchase_doc_batch: '||'Regular cursor.');
4373: END IF;
4374: sql_stmt := 'select distinct msi.inventory_item_id '||
4375: 'from mtl_system_items msi '||
4376: 'where msi.base_item_id is not null '||
4406: sql_stmt := sql_stmt || ' and msi.base_item_id is not null ';
4407: End if;
4408:
4409: If PG_DEBUG <> 0 Then
4410: oe_debug_pub.add('Create_purchasing_doc_batch: Dynamic SqlStmt : '||sql_stmt,1);
4411: End if;
4412: If drive_mark = 1 Then
4413: OPEN pprollup_cur FOR sql_stmt using p_organization_id;
4414: elsif drive_mark = 2 then
4447:
4448: /* sales order number or line id or offset is passed */
4449:
4450: IF PG_DEBUG <> 0 THEN
4451: oe_debug_pub.add('Create_purchase_doc_batch: '||'OE cursor.');
4452: END IF;
4453:
4454: OPEN pprollup_oe_cur FOR
4455: SELECT oel.line_id, oel.inventory_item_id,oel.ato_line_id
4549:
4550: elsif (p_upgrade = 1 ) then
4551:
4552: IF PG_DEBUG <> 0 THEN
4553: oe_debug_pub.add('Create_purchase_doc_batch: '||'Upgrade');
4554: END IF;
4555:
4556: if p_perform_rollup = 2 then
4557: oe_debug_pub.add('Perform Rollup parameter is set to NO.');
4553: oe_debug_pub.add('Create_purchase_doc_batch: '||'Upgrade');
4554: END IF;
4555:
4556: if p_perform_rollup = 2 then
4557: oe_debug_pub.add('Perform Rollup parameter is set to NO.');
4558: RETCODE := 0 ;
4559: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
4560: return;
4561: end if;
4609: v_rec_count := v_rec_count + 1;
4610:
4611: -- Log all the record being processed.
4612: IF PG_DEBUG <> 0 THEN
4613: oe_debug_pub.add('Create_purchase_doc_batch: '
4614: || '+-------- Processing for --------------------------------------------------+',1);
4615:
4616: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4617: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4612: IF PG_DEBUG <> 0 THEN
4613: oe_debug_pub.add('Create_purchase_doc_batch: '
4614: || '+-------- Processing for --------------------------------------------------+',1);
4615:
4616: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4617: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4618: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4619: oe_debug_pub.add('Create_purchase_doc_batch: '
4620: || '+--------------------------------------------------------------------------+',1);
4613: oe_debug_pub.add('Create_purchase_doc_batch: '
4614: || '+-------- Processing for --------------------------------------------------+',1);
4615:
4616: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4617: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4618: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4619: oe_debug_pub.add('Create_purchase_doc_batch: '
4620: || '+--------------------------------------------------------------------------+',1);
4621: END IF;
4614: || '+-------- Processing for --------------------------------------------------+',1);
4615:
4616: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4617: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4618: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4619: oe_debug_pub.add('Create_purchase_doc_batch: '
4620: || '+--------------------------------------------------------------------------+',1);
4621: END IF;
4622:
4615:
4616: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4617: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4618: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4619: oe_debug_pub.add('Create_purchase_doc_batch: '
4620: || '+--------------------------------------------------------------------------+',1);
4621: END IF;
4622:
4623: --- 4172156
4644:
4645: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4646: RETCODE := 1;
4647: IF PG_DEBUG <> 0 THEN
4648: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Expected Error in Create_purchasing_doc.',1);
4649: END IF;
4650: err_counter := err_counter + 1;
4651: erroredItems(err_counter).config_item_id := ll_inventory_item_id;
4652: ROLLBACK TO pp_rollup;
4652: ROLLBACK TO pp_rollup;
4653:
4654: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4655: IF PG_DEBUG <> 0 THEN
4656: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'UnExpected Error in Create_purchasing_doc.',1);
4657: END IF;
4658: ROLLBACK TO pp_rollup;
4659: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4660: ELSE
4658: ROLLBACK TO pp_rollup;
4659: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4660: ELSE
4661: IF PG_DEBUG <> 0 THEN
4662: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Successfully processed.',1);
4663: END IF;
4664: pass_counter := pass_counter + 1;
4665: passedItems(pass_counter).config_item_id := ll_inventory_item_id;
4666: END IF;
4671: END LOOP;
4672:
4673: if pprollup_cur%ISOPEN then
4674: IF PG_DEBUG <> 0 THEN
4675: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Fetched '|| pprollup_cur%ROWCOUNT ||' rows');
4676: END IF;
4677:
4678: close pprollup_cur;
4679: end if;
4679: end if;
4680:
4681: if pprollup_oe_cur%ISOPEN then
4682: IF PG_DEBUG <> 0 THEN
4683: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Fetched '|| pprollup_oe_cur%ROWCOUNT ||' rows');
4684: END IF;
4685: close pprollup_oe_cur;
4686: end if;
4687:
4687:
4688:
4689: -- Print the successfully processed items..
4690: if (pass_counter > 0 AND PG_DEBUG <> 0) then
4691: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4692: oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4693: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4694:
4695: for j in 1 .. pass_counter
4688:
4689: -- Print the successfully processed items..
4690: if (pass_counter > 0 AND PG_DEBUG <> 0) then
4691: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4692: oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4693: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4694:
4695: for j in 1 .. pass_counter
4696: loop
4689: -- Print the successfully processed items..
4690: if (pass_counter > 0 AND PG_DEBUG <> 0) then
4691: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4692: oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4693: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4694:
4695: for j in 1 .. pass_counter
4696: loop
4697: if ( passedItems(j).config_item_id > 0 ) then
4705: FROM mtl_system_items_kfv kfv
4706: WHERE kfv.inventory_item_id = passedItems(j).config_item_id
4707: AND rownum = 1;
4708:
4709: oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4710:
4711: exception
4712: when OTHERS then
4713: oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4709: oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4710:
4711: exception
4712: when OTHERS then
4713: oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4714: end;
4715: end if;
4716: end loop;
4717: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4713: oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4714: end;
4715: end if;
4716: end loop;
4717: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4718: end if;
4719:
4720:
4721: -- Print the errored records..
4719:
4720:
4721: -- Print the errored records..
4722: if (err_counter > 0 AND PG_DEBUG <> 0) then
4723: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4724: oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4725: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4726:
4727: for j in 1 .. err_counter
4720:
4721: -- Print the errored records..
4722: if (err_counter > 0 AND PG_DEBUG <> 0) then
4723: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4724: oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4725: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4726:
4727: for j in 1 .. err_counter
4728: loop
4721: -- Print the errored records..
4722: if (err_counter > 0 AND PG_DEBUG <> 0) then
4723: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4724: oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4725: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4726:
4727: for j in 1 .. err_counter
4728: loop
4729: if ( erroredItems(j).config_item_id > 0 ) then
4737: FROM mtl_system_items_kfv kfv
4738: WHERE kfv.inventory_item_id = erroredItems(j).config_item_id
4739: AND rownum = 1;
4740:
4741: oe_debug_pub.add (' '|| j ||'.'||' '|| l_err_config_description ||
4742: '(item id '||erroredItems(j).config_item_id ||')');
4743:
4744: exception
4745: when OTHERS then
4742: '(item id '||erroredItems(j).config_item_id ||')');
4743:
4744: exception
4745: when OTHERS then
4746: oe_debug_pub.add ('**Failed to get description for item id '|| erroredItems(j).config_item_id );
4747: end;
4748: end if;
4749: end loop;
4750: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4746: oe_debug_pub.add ('**Failed to get description for item id '|| erroredItems(j).config_item_id );
4747: end;
4748: end if;
4749: end loop;
4750: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4751: end if;
4752:
4753:
4754: -- Launch the concurrent program as needed
4753:
4754: -- Launch the concurrent program as needed
4755:
4756: IF PG_DEBUG <> 0 THEN
4757: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Calling Submit_pdoi_conc_prog.');
4758: END IF;
4759:
4760: Submit_pdoi_conc_prog(
4761: p_oper_unit_list => x_oper_unit_list,
4765: x_msg_data => x_msg_data);
4766:
4767: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4768: IF PG_DEBUG <> 0 THEN
4769: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Expected Error in Submit_pdoi_conc_prog.',1);
4770: END IF;
4771: raise FND_API.G_EXC_ERROR;
4772:
4773: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4771: raise FND_API.G_EXC_ERROR;
4772:
4773: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4774: IF PG_DEBUG <> 0 THEN
4775: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'UnExpected Error in Submit_pdoi_conc_prog.',1);
4776: END IF;
4777: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4778: END IF;
4779:
4787:
4788: END IF;
4789:
4790: IF PG_DEBUG <> 0 THEN
4791: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Search for the following string to look at the blanket price rollup process log for an item...');
4792: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'ROLLUP BLANKETS FOR
4793: END IF;
4794:
4795: COMMIT;
4788: END IF;
4789:
4790: IF PG_DEBUG <> 0 THEN
4791: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Search for the following string to look at the blanket price rollup process log for an item...');
4792: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'ROLLUP BLANKETS FOR
4794:
4795: COMMIT;
4796:
4797: EXCEPTION
4798:
4799: when FND_API.G_EXC_UNEXPECTED_ERROR then
4800: IF PG_DEBUG <> 0 THEN
4801: oe_debug_pub.add('Create_purchase_doc_batch:: unexp error::'||l_stmt_num||sqlerrm,1);
4802: END IF;
4803: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4804: CTO_MSG_PUB.Count_And_Get
4805: (p_msg_count => x_msg_count
4809: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4810:
4811: when FND_API.G_EXC_ERROR then
4812: IF PG_DEBUG <> 0 THEN
4813: oe_debug_pub.add('Create_purchase_doc_batch::exp error::'||l_stmt_num||sqlerrm,1);
4814: END IF;
4815: x_return_status := FND_API.G_RET_STS_ERROR;
4816: CTO_MSG_PUB.Count_And_Get
4817: (p_msg_count => x_msg_count
4820: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4821:
4822: when others then
4823: IF PG_DEBUG <> 0 THEN
4824: oe_debug_pub.add('Create_purchase_doc_batch::others::'||l_stmt_num||sqlerrm,1);
4825: END IF;
4826: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4827: CTO_MSG_PUB.Count_And_Get
4828: (p_msg_count => x_msg_count
4857: while (i is not null)
4858: loop
4859:
4860: --- Launch the concurrent program
4861: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Launching program for oper unit ='||to_char(p_oper_unit_list(i).oper_unit));
4862: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Batch id = '||to_char(p_batch_id),1);
4863:
4864: l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4865: If l_release_method is null then
4858: loop
4859:
4860: --- Launch the concurrent program
4861: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Launching program for oper unit ='||to_char(p_oper_unit_list(i).oper_unit));
4862: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Batch id = '||to_char(p_batch_id),1);
4863:
4864: l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4865: If l_release_method is null then
4866: l_rel_method_value := null;
4863:
4864: l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4865: If l_release_method is null then
4866: l_rel_method_value := null;
4867: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is null...',1);
4868: elsif l_release_method = 2 then
4869: l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4870: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4871: elsif l_release_method = 1 then
4866: l_rel_method_value := null;
4867: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is null...',1);
4868: elsif l_release_method = 2 then
4869: l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4870: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4871: elsif l_release_method = 1 then
4872: l_rel_method_value := 'CREATE';
4873: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4874: elsif l_release_method = 3 then
4869: l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4870: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4871: elsif l_release_method = 1 then
4872: l_rel_method_value := 'CREATE';
4873: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4874: elsif l_release_method = 3 then
4875: l_rel_method_value := 'MANUAL';
4876: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Release Using AutoCreate ...',1);
4877: end if;
4872: l_rel_method_value := 'CREATE';
4873: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4874: elsif l_release_method = 3 then
4875: l_rel_method_value := 'MANUAL';
4876: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Release Using AutoCreate ...',1);
4877: end if;
4878:
4879: -- fnd_client_info.set_org_context(p_oper_unit_list(i).oper_unit);
4880:
4916:
4917:
4918:
4919:
4920: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'pdoi concurrent request = '||to_char(l_request_id)||' is submitted',1);
4921: i := p_oper_unit_list.next(i);
4922:
4923: end loop;
4924:
4988:
4989: g_pg_level := g_pg_level + 3;
4990: lstmtNumber := 10;
4991: if PG_DEBUG <> 0 Then
4992: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Inside Process Purchase Price',5);
4993: end if;
4994:
4995: lstmtNumber := 20;
4996: select distinct nvl(fsp.inventory_organization_id,0)
5011: where inventory_item_id = p_config_item_id
5012: and rownum = 1;
5013:
5014: If PG_DEBUG <> 0 Then
5015: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of Validation orgs = '||l_orgs_list.count,5);
5016: End if;
5017: lStmtNumber := 30;
5018: If l_orgs_list.count <> 0 Then
5019: for i in l_orgs_list.first..l_orgs_list.last
5017: lStmtNumber := 30;
5018: If l_orgs_list.count <> 0 Then
5019: for i in l_orgs_list.first..l_orgs_list.last
5020: Loop
5021: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Validation org = '||l_orgs_list(i),5);
5022: lStmtNumber := 40;
5023: Select list_price_per_unit
5024: into l_list_price
5025: from mtl_system_items
5026: where inventory_item_id = p_config_item_id
5027: and organization_id = l_orgs_list(i);
5028:
5029: If pg_debug <> 0 Then
5030: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: List price in item master = '||l_list_price,5);
5031: End if;
5032: -- Renga Talk to val regarding the custom hook
5033:
5034: x_rolled_price := CTO_CUSTOM_LIST_PRICE_PK.get_list_price(
5051: x_msg_data => x_msg_data);
5052:
5053: if x_return_status = FND_API.G_RET_STS_ERROR then
5054: IF PG_DEBUG <> 0 THEN
5055: oe_debug_pub.add('Create_Purchasing_Doc: '
5056: || ' Expected error in Rollup_list_procedure',1);
5057: END IF;
5058: raise FND_API.G_EXC_ERROR;
5059: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5057: END IF;
5058: raise FND_API.G_EXC_ERROR;
5059: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5060: IF PG_DEBUG <> 0 THEN
5061: oe_debug_pub.add('Create_Purchasing_Doc: '
5062: || ' Unexpected error in rollup_list_price ',1);
5063: END IF;
5064: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5065: end if;
5076:
5077: lStmtNumber := 50;
5078: If l_list_price is null or p_overwrite_list_price = 'Y' Then
5079: If PG_DEBUG <> 0 Then
5080: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Updating item master with list price ',5);
5081: End if;
5082:
5083: Update Mtl_system_items
5084: set list_price_per_unit = x_rolled_price
5085: where inventory_item_id = p_config_item_id
5086: and organization_id = l_orgs_list(i)
5087: and (P_overwrite_list_price = 'Y' or list_price_per_unit is null);
5088: If pg_debug <> 0 Then
5089: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of rows updated = '||sql%rowcount,1);
5090: End if;
5091: End if; /* l_list_price is null or p_overwrite_list_price = 'Y' */
5092: End Loop;
5093: End if;
5109: Exception when no_data_found then
5110: l_buy_found := 'N';
5111: End;
5112: If l_buy_found = 'N' then
5113: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
5114: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
5115: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
5116: return;
5117: End if;
5110: l_buy_found := 'N';
5111: End;
5112: If l_buy_found = 'N' then
5113: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
5114: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
5115: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
5116: return;
5117: End if;
5118: End if;
5111: End;
5112: If l_buy_found = 'N' then
5113: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
5114: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
5115: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
5116: return;
5117: End if;
5118: End if;
5119:
5129: x_msg_data => x_msg_data);
5130:
5131: if x_return_status = FND_API.G_RET_STS_ERROR then
5132: IF PG_DEBUG <> 0 THEN
5133: oe_debug_pub.add('Create_Purchasing_Doc: '
5134: || ' Expected error in Rollup_purchase_price procedure',1);
5135: END IF;
5136: raise FND_API.G_EXC_ERROR;
5137: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5135: END IF;
5136: raise FND_API.G_EXC_ERROR;
5137: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
5138: IF PG_DEBUG <> 0 THEN
5139: oe_debug_pub.add('Create_Purchasing_Doc: '
5140: || ' Unexpected error in rollup_purchase_price procedure',1);
5141: EnD IF;
5142: raise FND_API.G_EXC_UNEXPECTED_ERROR;
5143: end if;
5147: exception
5148:
5149: when FND_API.G_EXC_UNEXPECTED_ERROR then
5150: IF PG_DEBUG <> 0 THEN
5151: oe_debug_pub.add('PROCESS_PURCHASE_PRICE:: unexp error::'||lStmtNumber||sqlerrm,1);
5152: END IF;
5153: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5154: CTO_MSG_PUB.Count_And_Get
5155: (p_msg_count => x_msg_count
5158: g_pg_level := g_pg_level - 3;
5159:
5160: when FND_API.G_EXC_ERROR then
5161: IF PG_DEBUG <> 0 THEN
5162: oe_debug_pub.add('PROCESS_PURCHASE_PRICE::exp error::'||lStmtNumber||sqlerrm,1);
5163: END IF;
5164: x_return_status := FND_API.G_RET_STS_ERROR;
5165: CTO_MSG_PUB.Count_And_Get
5166: (p_msg_count => x_msg_count
5168: g_pg_level := g_pg_level - 3;
5169:
5170: when others then
5171: IF PG_DEBUG <> 0 THEN
5172: oe_debug_pub.add('PROCESS_PURCHASE_PRICE::others::'||lStmtNumber||sqlerrm,1);
5173: END IF;
5174: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5175: CTO_MSG_PUB.Count_And_Get
5176: (p_msg_count => x_msg_count
5310: and cal2.calendar_date = trunc(oel.schedule_ship_date))) ';
5311: end if;
5312:
5313: IF PG_DEBUG <> 0 THEN
5314: oe_debug_pub.add ('The dyanamic sql generated is');
5315: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
5316: oe_debug_pub.add (substr(sql_stmt,1501,3000));
5317: oe_debug_pub.add ('The drive_mark is '||drive_mark);
5318: END IF;
5311: end if;
5312:
5313: IF PG_DEBUG <> 0 THEN
5314: oe_debug_pub.add ('The dyanamic sql generated is');
5315: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
5316: oe_debug_pub.add (substr(sql_stmt,1501,3000));
5317: oe_debug_pub.add ('The drive_mark is '||drive_mark);
5318: END IF;
5319:
5312:
5313: IF PG_DEBUG <> 0 THEN
5314: oe_debug_pub.add ('The dyanamic sql generated is');
5315: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
5316: oe_debug_pub.add (substr(sql_stmt,1501,3000));
5317: oe_debug_pub.add ('The drive_mark is '||drive_mark);
5318: END IF;
5319:
5320: /*
5313: IF PG_DEBUG <> 0 THEN
5314: oe_debug_pub.add ('The dyanamic sql generated is');
5315: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
5316: oe_debug_pub.add (substr(sql_stmt,1501,3000));
5317: oe_debug_pub.add ('The drive_mark is '||drive_mark);
5318: END IF;
5319:
5320: /*
5321: Below, we execute the sql statement according to which parameters
5387: -- Order_Number, Line_id, Organization_id and Offset_Days is passed
5388: EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id, p_offset_days;
5389:
5390: else
5391: oe_debug_pub.add ('INCORRECT COMBINATION of parameters');
5392:
5393: end if;
5394:
5395:
5393: end if;
5394:
5395:
5396: IF PG_DEBUG <> 0 THEN
5397: oe_debug_pub.add('load_lines_into_bcolt: ' || 'no. of records in temp table '||SQL%ROWCOUNT,1);
5398: END IF;
5399:
5400: EXCEPTION
5401: WHEN OTHERS THEN
5398: END IF;
5399:
5400: EXCEPTION
5401: WHEN OTHERS THEN
5402: oe_debug_pub.add('load_lines_into_bcolt: ' || 'others excpn::'||sqlerrm,1);
5403: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5404:
5405:
5406: END load_lines_into_bcolt;
5418: where line_id = p_line_id;
5419:
5420: EXCEPTION
5421: WHEN OTHERS THEN
5422: oe_debug_pub.add('update_bcolt_line_status: ' || 'others excpn::'||sqlerrm,1);
5423: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5424:
5425: END update_bcolt_line_status;
5426:
5472: FROM oe_order_lines_all
5473: WHERE line_id = p_interface_source_line_id;
5474:
5475: IF PG_DEBUG <> 0 THEN
5476: oe_debug_pub.add('Get_opm_charge_account: ' || 'Customer ID : '||to_char(l_cust_id),3);
5477: oe_debug_pub.add('Get_opm_charge_account: ' || 'customer site ID : '||to_char(l_cust_site_id),3);
5478: END IF;
5479:
5480:
5473: WHERE line_id = p_interface_source_line_id;
5474:
5475: IF PG_DEBUG <> 0 THEN
5476: oe_debug_pub.add('Get_opm_charge_account: ' || 'Customer ID : '||to_char(l_cust_id),3);
5477: oe_debug_pub.add('Get_opm_charge_account: ' || 'customer site ID : '||to_char(l_cust_site_id),3);
5478: END IF;
5479:
5480:
5481:
5494: AND mic.category_set_id = mdsv.category_set_id
5495: AND mdsv.functional_area_id = 2;
5496:
5497: IF PG_DEBUG <> 0 THEN
5498: oe_debug_pub.add('Get_opm_charge_account: ' || 'Category id: '||to_char(l_category_id),5);
5499: END IF;
5500:
5501:
5502: --added this api after taking to divya reddy(PO)
5506: --inorder to be consistent will pass it as done by PO.
5507:
5508: l_stmt_num := 30;
5509: IF PG_DEBUG <> 0 THEN
5510: oe_debug_pub.add('Get_opm_charge_account: ' || 'Calling MRP_SOURCING_API_PK.mrp_sourcing',5);
5511: END IF;
5512: --call mrp_sourcing to get vendor and vendor_site_id
5513: l_return_code := MRP_SOURCING_API_PK.mrp_sourcing(
5514: arg_mode =>p_source_type_code,
5525: arg_error_message =>l_error_message);
5526:
5527: IF l_return_code THEN
5528: IF PG_DEBUG <> 0 THEN
5529: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5530: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5531: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5532: END IF;
5533:
5526:
5527: IF l_return_code THEN
5528: IF PG_DEBUG <> 0 THEN
5529: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5530: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5531: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5532: END IF;
5533:
5534:
5527: IF l_return_code THEN
5528: IF PG_DEBUG <> 0 THEN
5529: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5530: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5531: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5532: END IF;
5533:
5534:
5535: ELSE
5533:
5534:
5535: ELSE
5536: IF PG_DEBUG <> 0 THEN
5537: oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5538: oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5539: --talked to usha arora. This error is not important enough to
5540: --stop process
5541: oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5534:
5535: ELSE
5536: IF PG_DEBUG <> 0 THEN
5537: oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5538: oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5539: --talked to usha arora. This error is not important enough to
5540: --stop process
5541: oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5542: END IF;
5537: oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5538: oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5539: --talked to usha arora. This error is not important enough to
5540: --stop process
5541: oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5542: END IF;
5543: END IF;
5544:
5545: --updating the global record structure variable
5566:
5567:
5568: l_stmt_num := 40;
5569: IF PG_DEBUG <> 0 THEN
5570: oe_debug_pub.add('Get_opm_charge_account: ' || 'calling GMF_transaction_accounts_PUB.get_accounts',5);
5571:
5572: END IF;
5573: --SLA api to get charge and accrual account
5574: --Reference aru#4610085
5588: x_charge_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).target_ccid;
5589:
5590: IF x_charge_account_id IS NULL THEN
5591: IF PG_DEBUG <> 0 THEN
5592: oe_debug_pub.add('Get_opm_charge_account: ' || 'charge account id is NULL in opm org',1);
5593: oe_debug_pub.add('Get_opm_charge_account: ' || 'USER SHOULD CHECK THE SETUP',1);
5594: END IF;
5595:
5596:
5589:
5590: IF x_charge_account_id IS NULL THEN
5591: IF PG_DEBUG <> 0 THEN
5592: oe_debug_pub.add('Get_opm_charge_account: ' || 'charge account id is NULL in opm org',1);
5593: oe_debug_pub.add('Get_opm_charge_account: ' || 'USER SHOULD CHECK THE SETUP',1);
5594: END IF;
5595:
5596:
5597: END IF;
5598:
5599: x_accrual_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).target_ccid;
5600:
5601: IF PG_DEBUG <> 0 THEN
5602: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5603: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5604: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5605: END IF;
5606:
5599: x_accrual_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).target_ccid;
5600:
5601: IF PG_DEBUG <> 0 THEN
5602: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5603: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5604: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5605: END IF;
5606:
5607:
5600:
5601: IF PG_DEBUG <> 0 THEN
5602: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5603: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5604: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5605: END IF;
5606:
5607:
5608: ELSE
5612: EXCEPTION
5613:
5614: WHEN FND_API.G_EXC_ERROR THEN
5615: IF PG_DEBUG <> 0 THEN
5616: oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5617: END IF;
5618: x_return_status := FND_API.G_RET_STS_ERROR;
5619: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5620: IF PG_DEBUG <> 0 THEN
5617: END IF;
5618: x_return_status := FND_API.G_RET_STS_ERROR;
5619: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5620: IF PG_DEBUG <> 0 THEN
5621: oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5622: END IF;
5623: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5624: WHEN OTHERS THEN
5625: IF PG_DEBUG <> 0 THEN
5622: END IF;
5623: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5624: WHEN OTHERS THEN
5625: IF PG_DEBUG <> 0 THEN
5626: oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::others error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5627: END IF;
5628: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5629:
5630: END Get_opm_charge_account;