[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;
564: -- begin the main procedure.
565: BEGIN
566:
567: IF PG_DEBUG <> 0 THEN
568: oe_debug_pub.add('auto_create_pur_req_cr: entered ' ,1);
569: END IF;
570:
571: -- initialize the program_id and the request_id from the concurrent req
572: l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
589:
590: savepoint begin_line;
591:
592: IF PG_DEBUG <> 0 THEN
593: oe_debug_pub.add('auto_create_pur_req_cr: going to load eligible lines into bom_cto_order_lines_temp ' ,1);
594: END IF;
595:
596: -- load eligible lines into bcolt
597: load_lines_into_bcolt(p_sales_order_line_id,
600: p_offset_days,
601: x_return_status);
602:
603: if x_return_status <> FND_API.G_RET_STS_SUCCESS then
604: oe_debug_pub.add ('Failed to load the lines into GTT.');
605: raise FND_API.G_EXC_ERROR;
606: end if;
607:
608: -- set the return status.
612: RETCODE := 0;
613:
614: lSourceCode := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
615: IF PG_DEBUG <> 0 THEN
616: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'l_source_code = '||lsourcecode,1);
617: END IF;
618:
619: -- set the batch_id to the request_id
620: l_batch_id := FND_GLOBAL.CONC_REQUEST_ID;
624:
625: -- for all the sales order lines (entered, booked )
626: -- Given parameters.
627: IF PG_DEBUG <> 0 THEN
628: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
629:
630: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
631:
632: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
626: -- Given parameters.
627: IF PG_DEBUG <> 0 THEN
628: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
629:
630: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
631:
632: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
633:
634: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
628: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
629:
630: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
631:
632: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
633:
634: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
635:
636: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
630: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+------------------ Parameters passed into the procedure ------------------+',1);
631:
632: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
633:
634: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
635:
636: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
637:
638: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
632: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,1);
633:
634: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
635:
636: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
637:
638: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
639:
640: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
634: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
635:
636: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
637:
638: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
639:
640: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
641:
642: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
636: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Organization_id : '||p_organization_id,1);
637:
638: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
639:
640: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
641:
642: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
643: END IF;
644:
638: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Offset Days : '||to_char(p_offset_days),1);
639:
640: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Create Req Type : '|| p_create_req_type,1);
641:
642: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
643: END IF;
644:
645:
646: /* bug fix 3590305/3599860 */
654: open eligible_lines_cur;
655: fetch eligible_lines_cur bulk collect into line_id_tab limit c_batch_size;
656:
657: IF PG_DEBUG <> 0 THEN
658: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'records in line_id_tab ' ||to_char(line_id_tab.count),1);
659: END IF;
660: exit when (line_id_tab.count = 0);
661:
662: -- Open loop for processing each eligible line.
664: tab_index := line_id_tab.first;
665:
666:
667: IF PG_DEBUG <> 0 THEN
668: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'starting tab index ' ||to_char(tab_index),1);
669: END IF;
670:
671: while tab_index is not null
672: LOOP
682:
683: -- Log all the record being processed.
684: -- Processing for
685: IF PG_DEBUG <> 0 THEN
686: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
687:
688: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
689:
690: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
684: -- Processing for
685: IF PG_DEBUG <> 0 THEN
686: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
687:
688: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
689:
690: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
691:
692: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
686: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+-------- Processing for --------------------------------------------------+',2);
687:
688: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
689:
690: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
691:
692: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
693: END IF;
694:
688: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales order : '||p_sales_order ,2);
689:
690: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Sales Order Line ID : '||to_char(so_line.line_id),2);
691:
692: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ship from Org : '||to_char(so_line.ship_from_org_id),2);
693: END IF;
694:
695:
696: -- get the sourcing type of the item in the specified organization.
737: ((p_create_req_type = 1 or p_create_req_type is null)
738: and v_source_type <> 3) THEN
739:
740: IF PG_DEBUG <> 0 THEN
741: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
742: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
743: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
744: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
745: END IF;
738: and v_source_type <> 3) THEN
739:
740: IF PG_DEBUG <> 0 THEN
741: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
742: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
743: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
744: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
745: END IF;
746:
739:
740: IF PG_DEBUG <> 0 THEN
741: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
742: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
743: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
744: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
745: END IF;
746:
747: RETCODE := 1;
740: IF PG_DEBUG <> 0 THEN
741: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Ineligible line for processing.',1);
742: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_message.',1);
743: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_can_create_supply = ' || v_can_create_supply, 1);
744: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'v_source_type = ' || v_source_type, 1);
745: END IF;
746:
747: RETCODE := 1;
748: rollback to begin_line;
751: END IF;
752:
753: ELSE
754: IF PG_DEBUG <> 0 THEN
755: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error in the sourcing rule. Status = '|| x_return_status,1);
756: END IF;
757: RETCODE := 1;
758: rollback to begin_line;
759: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
774: so_line.inventory_item_id);
775:
776: IF nvl(p_new_order_quantity, 0) = 0 THEN
777: -- Should throw this into log even if debug is off.
778: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The new order quantity is zero. Not eligible for req creation.',1);
779:
780: rollback to begin_line;
781: update_bcolt_line_status(line_id_tab(tab_index), 4, x_return_status);
782: goto loop1;
788:
789: /*
790: if (lOperUnit <> so_line.org_id ) then
791: IF PG_DEBUG <> 0 THEN
792: oe_debug_pub.add('Setting the Org Context to '||so_line.org_id,1);
793: END IF;
794:
795: OE_ORDER_CONTEXT_GRP.Set_Created_By_Context (
796: p_header_id => NULL
802: ,x_msg_count => x_Msg_Count
803: ,x_msg_data => x_Msg_Data );
804:
805: IF x_return_status <> FND_API.G_RET_STS_SUCCESS Then
806: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error in the set_created_by_context',1);
807: RETCODE := 1;
808: rollback to begin_line;
809: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
810: goto loop1;
831: x_msg_count => v_x_error_msg_count,
832: x_msg_data => v_x_error_msg);
833:
834: IF (v_x_hold_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
835: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Failed in Check Hold ' || v_x_hold_return_status, 1);
836: RETCODE := 1;
837: rollback to begin_line;
838: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
839: goto loop1;
840:
841: ELSE
842:
843: IF PG_DEBUG <> 0 THEN
844: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Success in Check Hold ' || v_x_hold_return_status, 1);
845: END IF;
846:
847: IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
848:
845: END IF;
846:
847: IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
848:
849: 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);
850: fnd_message.set_name('BOM', 'CTO_ORDER_LINE_ON_HOLD');
851: oe_msg_pub.add;
852:
853: RETCODE := 1;
875: x_return_status => x_return_status);
876:
877: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
878: IF PG_DEBUG <> 0 THEN
879: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'success from get_need_by_date' || ' l_need_by_date=' || l_need_by_date, 5);
880: END IF;
881: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
882: IF PG_DEBUG <> 0 THEN
883: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'expected error in get_need_by_date', 5);
879: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'success from get_need_by_date' || ' l_need_by_date=' || l_need_by_date, 5);
880: END IF;
881: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
882: IF PG_DEBUG <> 0 THEN
883: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'expected error in get_need_by_date', 5);
884: END IF;
885: raise fnd_api.g_exc_error;
886: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
887: IF PG_DEBUG <> 0 THEN
884: END IF;
885: raise fnd_api.g_exc_error;
886: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
887: IF PG_DEBUG <> 0 THEN
888: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'unexpected error in get_need_by_date', 5);
889: END IF;
890: raise fnd_api.g_exc_unexpected_error;
891: END IF;
892:
890: raise fnd_api.g_exc_unexpected_error;
891: END IF;
892:
893: IF PG_DEBUG <> 0 THEN
894: oe_debug_pub.add('Need by date: '|| l_need_by_date, 1);
895: ENd IF;
896:
897: -- Insert record into the interface table.
898:
927: l_req_input_data, -- req_interface_input_data
928: x_return_status );
929:
930: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
931: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'populate_req_interface failed with status '|| x_return_status,1);
932: RETCODE := 1;
933: rollback to begin_line;
934: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
935: goto loop1;
935: goto loop1;
936: ELSE
937: v_rec_count_noerr := v_rec_count_noerr + 1;
938: IF PG_DEBUG <> 0 THEN
939: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Insert successful.',1);
940: END IF;
941: END IF;
942:
943:
968: l_rets := CTO_WORKFLOW_API_PK.display_wf_status( p_order_line_id=>so_line.line_id);
969:
970: IF l_rets = 0 THEN
971:
972: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error occurred in display_wf_status - Stmt_num'||to_char(l_stmt_num),1);
973:
974: RETCODE := 1;
975: rollback to begin_line;
976: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
977: goto loop1;
978:
979: ELSE
980: IF PG_DEBUG <> 0 THEN
981: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Order updated to REQ_REQUESTED.',1);
982: END IF;
983: END IF;
984:
985:
1009: -- (FP 5633040) bugfix 5623360: call with RESERVED instead of COMPLETE.
1010:
1011: IF NOT l_res THEN
1012:
1013: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Error occurred in updating the workflow status - Stmt_num'||to_char(l_stmt_num),1);
1014:
1015: RETCODE := 1;
1016: rollback to begin_line;
1017: update_bcolt_line_status(line_id_tab(tab_index), 3, x_return_status);
1053: end loop; -- loop on eligible_lines_cur
1054:
1055:
1056: IF PG_DEBUG <> 0 THEN
1057: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1058:
1059: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1060:
1061: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1055:
1056: IF PG_DEBUG <> 0 THEN
1057: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1058:
1059: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1060:
1061: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1062:
1063: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1057: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1058:
1059: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1060:
1061: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1062:
1063: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1064:
1065: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1059: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'The Batch ID for this run was : '||to_char(l_batch_id),1);
1060:
1061: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1062:
1063: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1064:
1065: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1066: END IF;
1067:
1061: oe_debug_pub.add('auto_create_pur_req_cr: ' || '+---------------------------------------------------------------------------+',1);
1062:
1063: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records Processed : '||to_char(v_rec_count),1);
1064:
1065: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'Number of records inserted : '||to_char(v_rec_count_noerr),1);
1066: END IF;
1067:
1068:
1069: -- The following part of the code
1085: -- removed pg_debug check since messages need to be printed irrespective
1086: -- of debug level.
1087: EXCEPTION
1088: WHEN FND_API.G_EXC_ERROR THEN
1089: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1090: rollback to begin_line; --bug fix 3590305/3599860
1091: x_return_status := FND_API.G_RET_STS_ERROR;
1092: RETCODE := 2;
1093: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1092: RETCODE := 2;
1093: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1094:
1095: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1096: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1097: rollback to begin_line; --bug fix 3590305/3599860
1098: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1099: RETCODE := 2;
1100: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1099: RETCODE := 2;
1100: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1101:
1102: WHEN OTHERS THEN
1103: oe_debug_pub.add('auto_create_pur_req_cr: ' || 'AUTO_CREATE_PUR_REQ_CR::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1104: rollback to begin_line; --bug fix 3590305/3599860
1105: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1106: RETCODE := 2;
1107: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
1166: FETCH delivery_location_cur INTO x_location_id;
1167: CLOSE delivery_location_cur;
1168:
1169: IF PG_DEBUG <> 0 THEN
1170: oe_debug_pub.add('populate_req_interface: ' || 'Location ID : '||to_Char(x_location_id),1);
1171: END IF;
1172:
1173: IF x_location_id IS NULL THEN
1174: RAISE No_Data_Found;
1189: EXCEPTION
1190:
1191: When no_data_found THEN
1192: IF PG_DEBUG <> 0 THEN
1193: oe_debug_pub.add('get_deliver_to_location_id: ' || 'exception No location_id found', 1);
1194: END IF;
1195: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196:
1197: when FND_API.G_EXC_ERROR THEN
1195: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196:
1197: when FND_API.G_EXC_ERROR THEN
1198: IF PG_DEBUG <> 0 THEN
1199: oe_debug_pub.add('get_deliver_to_location_id: ' || 'expected error: ' || sqlerrm, 1);
1200: END IF;
1201: x_return_status := FND_API.G_RET_STS_ERROR;
1202:
1203: when FND_API.G_EXC_UNEXPECTED_ERROR then
1201: x_return_status := FND_API.G_RET_STS_ERROR;
1202:
1203: when FND_API.G_EXC_UNEXPECTED_ERROR then
1204: IF PG_DEBUG <> 0 THEN
1205: oe_debug_pub.add('get_deliver_to_location_id: ' || 'unexpected error: ' || sqlerrm, 1);
1206: END IF;
1207: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1208:
1209: when others then
1207: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1208:
1209: when others then
1210: IF PG_DEBUG <> 0 THEN
1211: oe_debug_pub.add('get_deliver_to_location_id: ' || 'exception others: ' || sqlerrm, 1);
1212: END IF;
1213: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1214:
1215: End get_deliver_to_location_id;
1347: x_return_status);
1348:
1349: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1350: IF PG_DEBUG <> 0 THEN
1351: oe_debug_pub.add('populate_req_interface: ' || 'success from get_deliver_to_location_id' || ' l_location_id = ' || l_location_id, 1);
1352: END IF;
1353: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1354: IF PG_DEBUG <> 0 THEN
1355: oe_debug_pub.add('populate_req_interface: ' || 'expected error in get_deliver_to_location_id', 1);
1351: oe_debug_pub.add('populate_req_interface: ' || 'success from get_deliver_to_location_id' || ' l_location_id = ' || l_location_id, 1);
1352: END IF;
1353: elsif x_return_status = FND_API.G_RET_STS_ERROR THEN
1354: IF PG_DEBUG <> 0 THEN
1355: oe_debug_pub.add('populate_req_interface: ' || 'expected error in get_deliver_to_location_id', 1);
1356: END IF;
1357: raise fnd_api.g_exc_error;
1358: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1359: IF PG_DEBUG <> 0 THEN
1356: END IF;
1357: raise fnd_api.g_exc_error;
1358: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1359: IF PG_DEBUG <> 0 THEN
1360: oe_debug_pub.add('populate_req_interface: ' || 'unexpected error in get_deliver_to_location_id', 1);
1361: END IF;
1362: raise fnd_api.g_exc_unexpected_error;
1363: END IF;
1364:
1371: l_stmt_num := 30;
1372: FOR e_id in emp_id (p_created_by) LOOP
1373: p_preparer_id := e_id.employee_id;
1374: IF PG_DEBUG <> 0 THEN
1375: oe_debug_pub.add('populate_req_interface: ' || 'Preparer ID : '||to_char(p_preparer_id),1);
1376: END IF;
1377: exit;
1378: END LOOP;
1379:
1383: IF p_preparer_id is null and p_interface_source_code in ('CTO','CTO-LOWER LEVEL') THEN
1384: p_preparer_id := oe_sys_parameters.value('ONT_EMP_ID_FOR_SS_ORDERS',p_org_id);
1385:
1386: IF PG_DEBUG <> 0 THEN
1387: oe_debug_pub.add('populate_req_interface: ' || 'Preparer ID (empIdforSSOrders): '||to_char(p_preparer_id),1);
1388: END IF;
1389: END IF;
1390:
1391:
1452:
1453: IF (l_result) THEN
1454:
1455: IF PG_DEBUG <> 0 THEN
1456: oe_debug_pub.add('success after call to FND_INSTALLATION.GET_APP_INFO',1);
1457: oe_debug_pub.add('returned custmer schema name for ONT =>'||l_customer_schema,1);
1458: END IF;
1459: ELSE
1460: null;
1453: IF (l_result) THEN
1454:
1455: IF PG_DEBUG <> 0 THEN
1456: oe_debug_pub.add('success after call to FND_INSTALLATION.GET_APP_INFO',1);
1457: oe_debug_pub.add('returned custmer schema name for ONT =>'||l_customer_schema,1);
1458: END IF;
1459: ELSE
1460: null;
1461: IF PG_DEBUG <> 0 THEN
1458: END IF;
1459: ELSE
1460: null;
1461: IF PG_DEBUG <> 0 THEN
1462: oe_debug_pub.add('FAILED IN call to FND_INSTALLATION.GET_APP_INFO',1);
1463: END IF;
1464:
1465: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1466:
1484: ||' AND msi.organization_id = :p_destination_org_id'
1485: ||' AND msi.allow_item_desc_update_flag = :pflag ';
1486:
1487: IF PG_DEBUG <> 0 THEN
1488: oe_debug_pub.add(sql_stmt,1);
1489: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1490: l_user_item_desc||'-BEFORE EXE IMM',1);
1491: END IF;
1492:
1485: ||' AND msi.allow_item_desc_update_flag = :pflag ';
1486:
1487: IF PG_DEBUG <> 0 THEN
1488: oe_debug_pub.add(sql_stmt,1);
1489: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1490: l_user_item_desc||'-BEFORE EXE IMM',1);
1491: END IF;
1492:
1493: l_stmt_num := 90;
1495: EXECUTE IMMEDIATE sql_stmt INTO l_user_item_desc
1496: USING p_interface_source_line_id,p_destination_org_id,pflag;
1497:
1498: IF PG_DEBUG <> 0 THEN
1499: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1500: l_user_item_desc||'-AFTER EXE IMM',1);
1501: oe_debug_pub.add('User Item Description is : ' || l_user_item_desc ,1);
1502: END IF;
1503: else
1497:
1498: IF PG_DEBUG <> 0 THEN
1499: oe_debug_pub.add(p_interface_source_line_id||'-'||p_destination_org_id||'-'||pflag||'-'||
1500: l_user_item_desc||'-AFTER EXE IMM',1);
1501: oe_debug_pub.add('User Item Description is : ' || l_user_item_desc ,1);
1502: END IF;
1503: else
1504: l_user_item_desc := NULL;
1505:
1503: else
1504: l_user_item_desc := NULL;
1505:
1506: IF PG_DEBUG <> 0 THEN
1507: oe_debug_pub.add('OE_ORDER_LINES_ALL does not have column USER_ITEM_DESCRIPTION , l_chk_col: ' ||
1508: to_char(l_chk_col),1);
1509: END IF;
1510:
1511: end if;
1543:
1544: End;
1545:
1546: IF PG_DEBUG <> 0 THEN
1547: oe_debug_pub.add('Project _id and task_id are =>' ||l_project_id || 'and' || l_task_id);
1548: END IF;
1549:
1550: --IF p_interface_source_line_id is null THEN --bugfix 3042904
1551:
1629: and msi.bom_item_type = 4; --standard item
1630:
1631: Exception when others then
1632: IF PG_DEBUG <> 0 THEN
1633: oe_debug_pub.add('Revision not populated because '||SQLERRM);
1634: END IF;
1635: End;
1636: end if;
1637:
1635: End;
1636: end if;
1637:
1638: IF PG_DEBUG <> 0 THEN
1639: oe_debug_pub.add('Item Revision is ' ||l_item_revision);
1640: END IF;
1641: else
1642: If PG_DEBUG <> 0 Then
1643: oe_debug_pub.add('Popluate_req_interface: Inv Purchase by Revision is set to No or Source type is IR',1);
1639: oe_debug_pub.add('Item Revision is ' ||l_item_revision);
1640: END IF;
1641: else
1642: If PG_DEBUG <> 0 Then
1643: oe_debug_pub.add('Popluate_req_interface: Inv Purchase by Revision is set to No or Source type is IR',1);
1644: End if;
1645: end if;
1646: -- End bugfix 4068164
1647:
1678: -- account from rcv_paramters
1679: -- p_receiving_account_id := ch_act.receiving_account_id;
1680: p_receiving_account_id := ch_act.material_account;
1681: IF PG_DEBUG <> 0 THEN
1682: oe_debug_pub.add('populate_req_interface: ' || 'Charge Account ID : '||to_char(p_receiving_account_id),1);
1683: END IF;
1684: exit;
1685: END LOOP;
1686:
1698: ,x_return_status =>x_return_status);
1699: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1700:
1701: IF PG_DEBUG <> 0 THEN
1702: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(p_receiving_account_id),1);
1703: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
1704: END IF;
1705: ELSE
1706: IF PG_DEBUG <> 0 THEN
1699: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1700:
1701: IF PG_DEBUG <> 0 THEN
1702: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(p_receiving_account_id),1);
1703: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
1704: END IF;
1705: ELSE
1706: IF PG_DEBUG <> 0 THEN
1707: oe_debug_pub.add('populate_req_interface: ' || 'Get_opm_charge_account RET STATUS : '||x_return_status,1);
1703: oe_debug_pub.add('populate_req_interface: ' || 'OPMCharge Account ID : '||to_char(l_accrual_account_id),1);
1704: END IF;
1705: ELSE
1706: IF PG_DEBUG <> 0 THEN
1707: oe_debug_pub.add('populate_req_interface: ' || 'Get_opm_charge_account RET STATUS : '||x_return_status,1);
1708: END IF;
1709: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1710: END IF;
1711:
1806:
1807: EXCEPTION
1808: WHEN OTHERS THEN
1809: IF PG_DEBUG <> 0 THEN
1810: oe_debug_pub.add('populate_req_interface: ' ||
1811: 'insert into the req interface table failed interface_source_line_id'||
1812: to_char(p_interface_source_line_id),1);
1813:
1814: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error:: In the insert statment::'||
1810: oe_debug_pub.add('populate_req_interface: ' ||
1811: 'insert into the req interface table failed interface_source_line_id'||
1812: to_char(p_interface_source_line_id),1);
1813:
1814: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error:: In the insert statment::'||
1815: to_char(l_stmt_num)||'::'||sqlerrm,1);
1816: END IF;
1817: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818: END;
1820:
1821: EXCEPTION
1822: WHEN FND_API.G_EXC_ERROR THEN
1823: IF PG_DEBUG <> 0 THEN
1824: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1825: END IF;
1826: x_return_status := FND_API.G_RET_STS_ERROR;
1827: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1828: IF PG_DEBUG <> 0 THEN
1825: END IF;
1826: x_return_status := FND_API.G_RET_STS_ERROR;
1827: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1828: IF PG_DEBUG <> 0 THEN
1829: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1830: END IF;
1831: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832: WHEN OTHERS THEN
1833: IF PG_DEBUG <> 0 THEN
1830: END IF;
1831: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1832: WHEN OTHERS THEN
1833: IF PG_DEBUG <> 0 THEN
1834: oe_debug_pub.add('populate_req_interface: ' || 'POPULATE_REQ_INTERFACE::other error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1835: END IF;
1836: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1837: END populate_req_interface;
1838:
1867: Return (v_rsv_quantity);
1868: EXCEPTION
1869: WHEN OTHERS THEN
1870: IF PG_DEBUG <> 0 THEN
1871: oe_debug_pub.add('get_reserved_qty: ' || 'GET_RESERVED_QTY::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
1872: END IF;
1873: Return (0);
1874: END; -- get_reserved_qty
1875:
1956: p_po_quantity := nvl(p_order_qty,0) - nvl(v_rsv_quantity,0) - nvl(l_quantity_interface,0)-nvl(l_open_flow_qty,0);
1957:
1958: -- Log the quantities.
1959: IF PG_DEBUG <> 0 THEN
1960: oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
1961:
1962: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
1963:
1964: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
1958: -- Log the quantities.
1959: IF PG_DEBUG <> 0 THEN
1960: oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
1961:
1962: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
1963:
1964: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
1965:
1966: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
1960: oe_debug_pub.add('get_new_order_qty: ' || 'The Order quantity : '||to_char(nvl(p_order_qty,0)),1);
1961:
1962: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
1963:
1964: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
1965:
1966: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
1967:
1968: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
1962: oe_debug_pub.add('get_new_order_qty: ' || 'The Cancelled quantity : '||to_char(nvl(p_cancelled_qty,0)),1);
1963:
1964: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
1965:
1966: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
1967:
1968: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
1969:
1970: oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
1964: oe_debug_pub.add('get_new_order_qty: ' || 'The reservation quantity : '||to_char(nvl(v_rsv_quantity,0)),1);
1965:
1966: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
1967:
1968: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
1969:
1970: oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
1971: END IF;
1972:
1966: oe_debug_pub.add('get_new_order_qty: ' || 'The interfaced quantity : '||to_char(nvl(l_quantity_interface,0)),1);
1967:
1968: oe_debug_pub.add('get_new_order_qty: ' || 'The new Order quantity will be : '||to_char(nvl(p_po_quantity,0)),1);
1969:
1970: oe_debug_pub.add('get_new_order_qty:'||'flow open quantity =>' || to_char(nvl(l_open_flow_qty,0)), 1);
1971: END IF;
1972:
1973: RETURN nvl(p_po_quantity, 0);
1974:
2176: x_return_status := FND_API.G_RET_STS_SUCCESS;
2177: lStmtNumber := 10;
2178: g_pg_level := 3;
2179: IF PG_DEBUG <> 0 THEN
2180: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2181: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2182: || '******************************',1);
2183:
2184: oe_debug_pub.add('Create_Purchasing_Doc: '
2177: lStmtNumber := 10;
2178: g_pg_level := 3;
2179: IF PG_DEBUG <> 0 THEN
2180: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2181: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2182: || '******************************',1);
2183:
2184: oe_debug_pub.add('Create_Purchasing_Doc: '
2185: || ' CREATING PURCHASING DOCUMENT ',1);
2180: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2181: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2182: || '******************************',1);
2183:
2184: oe_debug_pub.add('Create_Purchasing_Doc: '
2185: || ' CREATING PURCHASING DOCUMENT ',1);
2186: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2187:
2188: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2182: || '******************************',1);
2183:
2184: oe_debug_pub.add('Create_Purchasing_Doc: '
2185: || ' CREATING PURCHASING DOCUMENT ',1);
2186: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2187:
2188: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2189: || '******************************',1);
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2184: oe_debug_pub.add('Create_Purchasing_Doc: '
2185: || ' CREATING PURCHASING DOCUMENT ',1);
2186: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2187:
2188: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2189: || '******************************',1);
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2191: END IF;
2192:
2186: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: START TIME '||to_char(sysdate,'hh:mi:ss'),1);
2187:
2188: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2189: || '******************************',1);
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2191: END IF;
2192:
2193: IF PG_DEBUG <> 0 THEN
2194: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2190: oe_debug_pub.add(lpad(' ',g_pg_level)||' ',1);
2191: END IF;
2192:
2193: IF PG_DEBUG <> 0 THEN
2194: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2195: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2196: END IF;
2197: lStmtNumber := 20;
2198: If Pg_Debug <> 0 Then
2191: END IF;
2192:
2193: IF PG_DEBUG <> 0 THEN
2194: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || 'IN batch id = '||to_char(p_batch_number),1);
2195: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2196: END IF;
2197: lStmtNumber := 20;
2198: If Pg_Debug <> 0 Then
2199: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create Purchasing Doc: Before calling get_config_details',5);
2195: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '||'Mode = '||p_mode,1);
2196: END IF;
2197: lStmtNumber := 20;
2198: If Pg_Debug <> 0 Then
2199: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create Purchasing Doc: Before calling get_config_details',5);
2200: End if;
2201:
2202:
2203: l_Comp_exists := 'N';
2215: Loop
2216:
2217: l_comp_exists := 'Y';
2218: if PG_DEBUG <> 0 Then
2219: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Processing config item = '
2220: ||buy_configs_rec.component_item_id,5);
2221: End if;
2222: lStmtNumber := 40;
2223:
2254: Exception When no_data_found then
2255: l_comp_exists := 'N';
2256: End;
2257: If PG_DEBUG <> 0 Then
2258: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Processing config item = '
2259: ||p_config_item_id,5);
2260: End if;
2261: lStmtNumber := 60;
2262:
2286:
2287: lStmtNumber := 80;
2288:
2289: If PG_DEBUG <> 0 Then
2290: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PURCHASING_DOC: Before calling Submit Pdoi',5);
2291: End if;
2292:
2293: Submit_pdoi_conc_prog(
2294: p_oper_unit_list => x_oper_unit_list,
2302:
2303: end if; /* l_comp_exits = 'Y' */
2304:
2305: IF PG_DEBUG <> 0 THEN
2306: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2307: || '****************************',1);
2308:
2309: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2310: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2305: IF PG_DEBUG <> 0 THEN
2306: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2307: || '****************************',1);
2308:
2309: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2310: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2311:
2312: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2313: || '****************************',1);
2306: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2307: || '****************************',1);
2308:
2309: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2310: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2311:
2312: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2313: || '****************************',1);
2314: END IF;
2308:
2309: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: ' || ' END CREATE PURCHASING DOC ',1);
2310: oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_PUCHASING_DOC: '|| ' END TIME : '||to_char(sysdate,'hh:mi:ss'),1);
2311:
2312: oe_debug_pub.add(lpad(' ',g_pg_level)||'Create_Purchasing_Doc: '
2313: || '****************************',1);
2314: END IF;
2315: g_pg_level := g_pg_level - 3;
2316:
2317: exception
2318:
2319: when FND_API.G_EXC_UNEXPECTED_ERROR then
2320: IF PG_DEBUG <> 0 THEN
2321: oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::unexp error::'||lStmtNumber||sqlerrm,1);
2322: END IF;
2323: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2324: CTO_MSG_PUB.Count_And_Get
2325: (p_msg_count => x_msg_count
2327: );
2328:
2329: when FND_API.G_EXC_ERROR then
2330: IF PG_DEBUG <> 0 THEN
2331: oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::exp error::'||lStmtNumber||sqlerrm,1);
2332: END IF;
2333: x_return_status := FND_API.G_RET_STS_ERROR;
2334: CTO_MSG_PUB.Count_And_Get
2335: (p_msg_count => x_msg_count
2336: ,p_msg_data => x_msg_data);
2337:
2338: when others then
2339: IF PG_DEBUG <> 0 THEN
2340: oe_debug_pub.add('Create_Purchasing_Doc: ' || 'Create_purchasing_doc::others::'||lStmtNumber||sqlerrm,1);
2341: END IF;
2342: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2343: CTO_MSG_PUB.Count_And_Get
2344: (p_msg_count => x_msg_count
2433: lStmtNumber := 10;
2434:
2435: x_rolled_price := 0;
2436: If PG_DEBUG <> 0 Then
2437: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2438: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2439: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2440: End if;
2441:
2434:
2435: x_rolled_price := 0;
2436: If PG_DEBUG <> 0 Then
2437: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2438: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2439: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2440: End if;
2441:
2442: lStmtNumber := 20;
2435: x_rolled_price := 0;
2436: If PG_DEBUG <> 0 Then
2437: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Inside Rollup List price API',5);
2438: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLIP_LIST_PRICE: Rollup for config item = '||p_config_item_id,5);
2439: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: PO Validation Org id = '||p_org_id,5);
2440: End if;
2441:
2442: lStmtNumber := 20;
2443: For pur_comp in purchase_comp
2443: For pur_comp in purchase_comp
2444: Loop
2445:
2446: IF PG_DEBUG <> 0 THEN
2447: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' || 'Component item id = '
2448: ||to_char(pur_comp.comp_item_id),2);
2449: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price per unit = '||pur_comp.list_price_per_unit,5);
2450: END IF;
2451:
2445:
2446: IF PG_DEBUG <> 0 THEN
2447: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' || 'Component item id = '
2448: ||to_char(pur_comp.comp_item_id),2);
2449: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price per unit = '||pur_comp.list_price_per_unit,5);
2450: END IF;
2451:
2452: lStmtNumber := 30;
2453:
2462: quantity => pur_comp.comp_qty,
2463: item_id => pur_comp.comp_item_id);
2464: l_price := l_prim_uom_qty * pur_comp.list_price_per_unit;
2465: If PG_DEBUG <> 0 Then
2466: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Primary UOM quantity = '||l_prim_uom_qty,5);
2467: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price for order qty = '||l_price,5);
2468: End if;
2469: x_rolled_price := x_rolled_price + l_price;
2470: else
2463: item_id => pur_comp.comp_item_id);
2464: l_price := l_prim_uom_qty * pur_comp.list_price_per_unit;
2465: If PG_DEBUG <> 0 Then
2466: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Primary UOM quantity = '||l_prim_uom_qty,5);
2467: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price for order qty = '||l_price,5);
2468: End if;
2469: x_rolled_price := x_rolled_price + l_price;
2470: else
2471: IF PG_DEBUG <> 0 THEN
2468: End if;
2469: x_rolled_price := x_rolled_price + l_price;
2470: else
2471: IF PG_DEBUG <> 0 THEN
2472: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: List price is defined as 0',5);
2473: END IF;
2474:
2475: end if;
2476:
2475: end if;
2476:
2477: lStmtNumber := 50;
2478: If PG_DEBUG <> 0 Then
2479: oe_debug_pub.add(lpad(' ',g_pg_level)
2480: ||'ROLLUP_LIST_PRICE : Get child configs rollup price, which are not enabled in this org',5);
2481: end if;
2482:
2483: For child_configs in child_configs_cur
2482:
2483: For child_configs in child_configs_cur
2484: Loop
2485: If PG_DEBUG <> 0 Then
2486: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Processing Child config = '
2487: ||child_configs.comp_item_id,5);
2488: End if;
2489: lStmtNumber := 60;
2490: Rollup_list_price (
2498:
2499: lStmtNumber := 70;
2500: x_rolled_price := x_rolled_price + l_rolled_price*child_configs.comp_qty; /* Renga need to add qty */
2501: IF PG_DEBUG <> 0 Then
2502: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: Configs rolled up price = '||l_rolled_price,5);
2503: End if;
2504: End Loop;
2505: End Loop;
2506:
2505: End Loop;
2506:
2507:
2508: IF PG_DEBUG <> 0 THEN
2509: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up organization = '||to_char(p_org_id),2);
2510: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up price = '
2511: ||to_char(x_rolled_price),2);
2512: END IF;
2513: g_pg_level := g_pg_level - 3;
2506:
2507:
2508: IF PG_DEBUG <> 0 THEN
2509: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up organization = '||to_char(p_org_id),2);
2510: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_LIST_PRICE: ' ||' Rolled up price = '
2511: ||to_char(x_rolled_price),2);
2512: END IF;
2513: g_pg_level := g_pg_level - 3;
2514: exception
2514: exception
2515:
2516: when FND_API.G_EXC_UNEXPECTED_ERROR then
2517: IF PG_DEBUG <> 0 THEN
2518: oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::unexp error::'||lStmtNumber||sqlerrm,1);
2519: END IF;
2520: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521: CTO_MSG_PUB.Count_And_Get
2522: (p_msg_count => x_msg_count
2525: g_pg_level := g_pg_level - 3;
2526:
2527: when FND_API.G_EXC_ERROR then
2528: IF PG_DEBUG <> 0 THEN
2529: oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::exp error::'||lStmtNumber||sqlerrm,1);
2530: END IF;
2531: x_return_status := FND_API.G_RET_STS_ERROR;
2532: CTO_MSG_PUB.Count_And_Get
2533: (p_msg_count => x_msg_count
2535: g_pg_level := g_pg_level - 3;
2536:
2537: when others then
2538: IF PG_DEBUG <> 0 THEN
2539: oe_debug_pub.add('Rollup_list_price: ' || 'Rollup_list_price::others::'||lStmtNumber||sqlerrm,1);
2540: END IF;
2541: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2542: CTO_MSG_PUB.Count_And_Get
2543: (p_msg_count => x_msg_count
2610: lStmtNumber := 10;
2611:
2612: g_pg_level := g_pg_level + 3;
2613: If pg_debug <> 0 then
2614: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Inside Rollup Purchase Price API',1);
2615: End if;
2616: select base_item_id,
2617: config_orgs
2618: into l_model_item_id,
2620: from mtl_system_items
2621: where inventory_item_id = p_config_item_id
2622: and rownum =1;
2623: IF PG_DEBUG <> 0 THEN
2624: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
2625: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: '||'Model item id = '||l_model_item_id,1);
2626: END IF;
2627:
2628:
2621: where inventory_item_id = p_config_item_id
2622: and rownum =1;
2623: IF PG_DEBUG <> 0 THEN
2624: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Calling get_all_item_asl...',5);
2625: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: '||'Model item id = '||l_model_item_id,1);
2626: END IF;
2627:
2628:
2629: -- Get the vendor and vendor site information from
2641: x_msg_data => x_msg_data);
2642:
2643: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2644: IF PG_DEBUG <> 0 THEN
2645: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Expected Error in Get_all_item_asl.',1);
2646: END IF;
2647: raise FND_API.G_EXC_ERROR;
2648:
2649: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2647: raise FND_API.G_EXC_ERROR;
2648:
2649: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2650: IF PG_DEBUG <> 0 THEN
2651: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: UnExpected Error in Get_all_item_asl.',1);
2652: END IF;
2653: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2654:
2655: END IF;
2656:
2657: lStmtNumber := 40;
2658: if l_model_vendors.count = 0 then
2659: IF PG_DEBUG <> 0 THEN
2660: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: No ASL defined for model...',5);
2661: END IF;
2662: g_pg_level := g_pg_level - 3;
2663: return;
2664: end if;
2663: return;
2664: end if;
2665:
2666: IF PG_DEBUG <> 0 THEN
2667: oe_debug_pub.add('ROLLUP_PURCHASE_PRICE: ' || 'Calling get_all_item_asl for config..',1);
2668: END IF;
2669: -- Get all the ASL's Defined for Config item (You may have some
2670: -- in the case of matching)
2671: lstmtNumber := 50;
2680:
2681:
2682: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2683: IF PG_DEBUG <> 0 THEN
2684: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Expected Error in Get_all_item_asl.',1);
2685: END IF;
2686: raise FND_API.G_EXC_ERROR;
2687:
2688: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2686: raise FND_API.G_EXC_ERROR;
2687:
2688: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2689: IF PG_DEBUG <> 0 THEN
2690: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: UnExpected Error in Get_all_item_asl.',1);
2691: END IF;
2692: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2693:
2694: END IF;
2694: END IF;
2695:
2696:
2697: IF PG_DEBUG <> 0 THEN
2698: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Started looping....',5);
2699: END IF;
2700: Reduce_vendor_by_ou(
2701: p_vendor_details => l_model_vendors,
2702: p_config_item_id => p_config_item_id,
2706: -- x_model_vendors := l_model_vendors;
2707: i := x_model_vendors.first;
2708:
2709: if PG_DEBUG <> 0 then
2710: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: BEGIN ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
2711: end if;
2712:
2713: -- Start processing each vendor and vendor sites for th model ASL
2714: lStmtNumber := 60;
2714: lStmtNumber := 60;
2715: while (i is not null)
2716: loop
2717: IF PG_DEBUG <> 0 THEN
2718: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
2719: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
2720: ||to_char(x_model_vendors(i).vendor_id),5);
2721:
2722: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
2715: while (i is not null)
2716: loop
2717: IF PG_DEBUG <> 0 THEN
2718: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
2719: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
2720: ||to_char(x_model_vendors(i).vendor_id),5);
2721:
2722: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
2723: ||to_char(x_model_vendors(i).vendor_site_id),1);
2718: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
2719: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Working for vendor ='
2720: ||to_char(x_model_vendors(i).vendor_id),5);
2721:
2722: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
2723: ||to_char(x_model_vendors(i).vendor_site_id),1);
2724:
2725: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
2726: ||to_char(x_model_vendors(i).asl_id),1);
2721:
2722: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: vendor site id ='
2723: ||to_char(x_model_vendors(i).vendor_site_id),1);
2724:
2725: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
2726: ||to_char(x_model_vendors(i).asl_id),1);
2727:
2728: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Index variable ='||to_char(i),1);
2729: END IF;
2724:
2725: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Asl id ='
2726: ||to_char(x_model_vendors(i).asl_id),1);
2727:
2728: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Index variable ='||to_char(i),1);
2729: END IF;
2730: -- CAll the custom API with Vendor and Vendor site information
2731: -- If the custom API returns TRUE we should not do anything, It is assumed
2732: -- That the custom API would have taken care of everything. IN this case we will skip this
2738: p_item_id => p_config_item_id,
2739: p_vendor_id => x_model_vendors(i).vendor_id,
2740: p_vendor_site_id => x_model_vendors(i).vendor_site_id) then
2741: IF PG_DEBUG <> 0 THEN
2742: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Custom API returned value...',1);
2743: END IF;
2744: else
2745:
2746: l_purchase_uom := x_model_vendors(i).Purchasing_uom;
2761: x_index => x_index);
2762:
2763: if l_doc_exists then
2764: IF PG_DEBUG <> 0 THEN
2765: oe_debug_pub.add(lpad(' ',g_pg_level)
2766: ||'ROLLUP_PURCHASE_PRICE: ASL exists for blanket, checking to see the blanket..',5);
2767: END IF;
2768:
2769: l_doc_line_id := null;
2796: x_msg_data => x_msg_data);
2797:
2798: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2799: IF PG_DEBUG <> 0 THEN
2800: oe_debug_pub.add(lpad(' ',g_pg_level)
2801: ||'ROLLUP_PURCHASE_PRICE: Expected Error in Blanket_Document_sourcing.',1);
2802: END IF;
2803: raise FND_API.G_EXC_ERROR;
2804:
2803: raise FND_API.G_EXC_ERROR;
2804:
2805: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2806: IF PG_DEBUG <> 0 THEN
2807: oe_debug_pub.add(lpad(' ',g_pg_level)
2808: ||'ROLLUP_PURCHASE_PRICE: UnExpected Error in blanket_document_sourcing.',1);
2809: END IF;
2810: raise FND_API.G_EXC_UNEXPECTED_ERROR;
2811:
2813:
2814: lStmtNumber := 100;
2815: If l_doc_return = 'Y' then
2816: IF PG_DEBUG <> 0 THEN
2817: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Valid Blanket found for config ..',2);
2818: END IF;
2819: l_doc_exists := TRUE;
2820: else
2821: IF PG_DEBUG <> 0 THEN
2818: END IF;
2819: l_doc_exists := TRUE;
2820: else
2821: IF PG_DEBUG <> 0 THEN
2822: oe_debug_pub.add(lpad(' ',g_pg_level)
2823: ||'ROLLUP_PURCHASE_PRICE: Valid Blanket not found for this config',2);
2824: END IF;
2825: l_doc_exists := FALSE;
2826: end if;
2829:
2830: lStmtNumber := 110;
2831: if l_doc_exists then
2832: IF PG_DEBUG <> 0 THEN
2833: oe_debug_pub.add(lpad(' ',g_pg_level)
2834: ||'ROLLUP_PURCHAE_PRICE: Valid Asl blanket already exists for configuration....',2);
2835:
2836: END IF;
2837: elsif x_model_vendors(i).vendor_site_id is null then
2835:
2836: END IF;
2837: elsif x_model_vendors(i).vendor_site_id is null then
2838: IF PG_DEBUG <> 0 THEN
2839: oe_debug_pub.add(lpad(' ',g_pg_level)
2840: ||'ROLLUP_PURCHASE_PRICE: Vendor site id is null need not process..',2);
2841: END IF;
2842: else
2843: l_doc_line_id := null;
2869:
2870:
2871: IF( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2872: IF PG_DEBUG <> 0 THEN
2873: oe_debug_pub.add(lpad(' ',g_pg_level)
2874: ||'success status false for po_autosource_sv.blanket_document_sourcing...',1);
2875:
2876: END IF;
2877:
2881: lStmtNumber := 130;
2882:
2883: If l_doc_return = 'N' then
2884: IF PG_DEBUG <> 0 THEN
2885: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHAES_PRICE: No blankets returned..',2);
2886: END IF;
2887: else
2888: IF PG_DEBUG <> 0 THEN
2889: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
2885: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHAES_PRICE: No blankets returned..',2);
2886: END IF;
2887: else
2888: IF PG_DEBUG <> 0 THEN
2889: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
2890: ||to_char(l_doc_line_id),2);
2891:
2892: oe_debug_pub.add(lpad(' ',g_pg_level)
2893: ||'ROLLUP_PURCHASE_PRICE: ROLLUP_PURCHASE_PRICE: Blanket doc header id ='
2888: IF PG_DEBUG <> 0 THEN
2889: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket document line id ='
2890: ||to_char(l_doc_line_id),2);
2891:
2892: oe_debug_pub.add(lpad(' ',g_pg_level)
2893: ||'ROLLUP_PURCHASE_PRICE: ROLLUP_PURCHASE_PRICE: Blanket doc header id ='
2894: ||to_char(l_doc_header_id),2);
2895: END IF;
2896:
2899: p_doc_header_id => l_doc_header_id)
2900: then
2901:
2902: IF PG_DEBUG <> 0 THEN
2903: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE '
2904: || 'Config line already exists in blanket..',2);
2905: END IF;
2906: else
2907: lStmtNumber := 140;
2918: and fsp.org_id = poh.org_id);
2919: Exception when no_data_found then
2920: l_config_exists := 'N';
2921: if pg_debug <> 0 Then
2922: oe_debug_pub.add(lpad(' ',g_pg_level)
2923: ||'ROLLUP_PURCHASE_PRICE: Config item does not exist in Po validation org. ',5);
2924: End if;
2925: End;
2926:
2936: x_return_status => x_return_status,
2937: x_msg_count => x_msg_count,
2938: x_msg_data => x_msg_data);
2939:
2940: oe_debug_pub.add('Rolled up blanket price =
2941: '||x_rolled_price);
2942:
2943: lStmtNumber := 160;
2944:
2958: where inventory_item_id = p_config_item_id
2959: and rownum = 1;
2960:
2961: IF PG_DEBUG <> 0 THEN
2962: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Item Name ='||x_segment1,2);
2963: END IF;
2964: lStmtNumber := 180;
2965: Derive_start_end_date(
2966: p_item_id => p_config_item_id,
2986: -- Record the operating unit in a table
2987: -- This tbale is used for launching the concurrent program
2988:
2989: IF PG_DEBUG <> 0 THEN
2990: oe_debug_pub.add(lpad(' ',g_pg_level)||'ROLLUP_PURCHASE_PRICE: Blanket Operating unit = '
2991: ||to_char(x_org_id),2);
2992: END IF;
2993:
2994: -- We should take the OU from Blanket instead of rcv org OU.
3010:
3011: end loop;
3012:
3013: if PG_DEBUG <> 0 then
3014: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3015: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: END ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
3016: end if;
3017:
3018: x_return_status := FND_API.G_RET_STS_SUCCESS;
3011: end loop;
3012:
3013: if PG_DEBUG <> 0 then
3014: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: ' || '****************************',1);
3015: oe_debug_pub.add(lpad(' ',g_pg_level) || 'ROLLUP_PURCHASE_PRICE: END ROLLUP BLANKETS FOR ' || to_char(p_config_item_id),1);
3016: end if;
3017:
3018: x_return_status := FND_API.G_RET_STS_SUCCESS;
3019: g_pg_level := g_pg_level - 3;
3021: exception
3022:
3023: when FND_API.G_EXC_UNEXPECTED_ERROR then
3024: IF PG_DEBUG <> 0 THEN
3025: oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::unexp error::'||lStmtNumber||sqlerrm,1);
3026: END IF;
3027: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3028: CTO_MSG_PUB.Count_And_Get
3029: (p_msg_count => x_msg_count
3031: );
3032:
3033: when FND_API.G_EXC_ERROR then
3034: IF PG_DEBUG <> 0 THEN
3035: oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::exp error::'||lStmtNumber||sqlerrm,1);
3036: END IF;
3037: x_return_status := FND_API.G_RET_STS_ERROR;
3038: CTO_MSG_PUB.Count_And_Get
3039: (p_msg_count => x_msg_count
3040: ,p_msg_data => x_msg_data);
3041:
3042: when others then
3043: IF PG_DEBUG <> 0 THEN
3044: oe_debug_pub.add('Rollup_purchase_price: ' || 'Rollup_purchase_price::others::'||lStmtNumber||sqlerrm,1);
3045: END IF;
3046: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3047: CTO_MSG_PUB.Count_And_Get
3048: (p_msg_count => x_msg_count
3112: when no_data_found then
3113: return False;
3114: when others then
3115: IF PG_DEBUG <> 0 THEN
3116: oe_debug_pub.add('config_exists_in_blanket: ' || 'When others error occured in sql ..',1);
3117: END IF;
3118:
3119: end config_exists_in_blanket;
3120:
3172: from mtl_system_items
3173: where inventory_item_id = p_config_item_id
3174: and rownum = 1;
3175:
3176: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3177: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Po doc line id = '||p_doc_line_id,5);
3178:
3179: End if;
3180:
3173: where inventory_item_id = p_config_item_id
3174: and rownum = 1;
3175:
3176: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Base model item id = '||l_base_model_id,5);
3177: oe_debug_pub.add(lpad(' ',g_pg_level) || 'Po doc line id = '||p_doc_line_id,5);
3178:
3179: End if;
3180:
3181: For buy_comps in buy_comps_cur
3260: End;
3261: x_rolled_price := nvl(x_rolled_price,0) + l_unit_price;
3262:
3263: IF PG_DEBUG <> 0 THEN
3264: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Item Id = '|| buy_comps.comp_item_id,1);
3265:
3266: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Blanket price ='|| to_char(l_unit_price),1);
3267: END IF;
3268:
3262:
3263: IF PG_DEBUG <> 0 THEN
3264: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Item Id = '|| buy_comps.comp_item_id,1);
3265:
3266: oe_debug_pub.add(lpad(' ',g_pg_level) || 'rollup_blanket_price: ' || 'Blanket price ='|| to_char(l_unit_price),1);
3267: END IF;
3268:
3269: End loop; /* Buy_comps */
3270:
3295: p_batch_id := x_int_header_id;
3296: end if;
3297:
3298: IF PG_DEBUG <> 0 THEN
3299: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Interface header id ='||to_char(x_int_header_id),1);
3300: END IF;
3301:
3302:
3303: Insert into Po_headers_interface(
3369: where po_header_id = p_doc_header_id;
3370:
3371:
3372: IF PG_DEBUG <> 0 THEN
3373: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'No of records inserted in headers = '||to_char(sql%rowcount),1);
3374:
3375: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Operating unit for the Blanket Doc = '||to_char(x_org_id),1);
3376: END IF;
3377:
3371:
3372: IF PG_DEBUG <> 0 THEN
3373: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'No of records inserted in headers = '||to_char(sql%rowcount),1);
3374:
3375: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_header: ' || 'Operating unit for the Blanket Doc = '||to_char(x_org_id),1);
3376: END IF;
3377:
3378: g_pg_level := g_pg_level - 3;
3379:
3408:
3409:
3410:
3411: IF PG_DEBUG <> 0 THEN
3412: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3413:
3414: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3415:
3416: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3410:
3411: IF PG_DEBUG <> 0 THEN
3412: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3413:
3414: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3415:
3416: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3417: END IF;
3418:
3412: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Inerting into po_lines_interface',1);
3413:
3414: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'Start date = '||to_char(p_start_date),2);
3415:
3416: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'End date ='||to_char(p_end_date),2);
3417: END IF;
3418:
3419: -- Bug fix 3589150
3420: -- Done by Renga Kannan on 04/30/04
3488: where pol.po_line_id =p_doc_line_id
3489: and poh.po_header_id = pol.po_header_id;
3490:
3491: IF PG_DEBUG <> 0 THEN
3492: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in lines = '||to_char(sql%rowcount),1);
3493: END IF;
3494:
3495: -- Insert the rows from po_line_locations_all
3496:
3585: where pol.po_line_id = p_doc_line_id
3586: and pol.po_line_id = poll.po_line_id;
3587:
3588: IF PG_DEBUG <> 0 THEN
3589: oe_debug_pub.add(lpad(' ',g_pg_level) || 'insert_blanket_line: ' || 'No of records inserted in locations = '||to_char(sql%rowcount),1);
3590: END IF;
3591:
3592: g_pg_level := g_pg_level - 3;
3593:
3654: -- from mtl system items. Both these attributes
3655: -- in master org level, hence u can get the attribute
3656: -- from any organization.
3657:
3658: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||p_config_item_id,1);
3659:
3660: Select option_specific_sourced
3661: into l_option_specific
3662: from mtl_system_items
3669: from bom_cto_order_lines
3670: where line_id = p_line_id;
3671: End if;
3672:
3673: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
3674: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
3675: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
3676:
3677: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
3670: where line_id = p_line_id;
3671: End if;
3672:
3673: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
3674: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
3675: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
3676:
3677: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
3678: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
3671: End if;
3672:
3673: oe_debug_pub.add('Reduce_vendor_by_ou: Option specific = '||l_option_specific,1);
3674: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
3675: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
3676:
3677: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
3678: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
3679:
3674: oe_debug_pub.add('Reduce_vendor_by_ou: Config Creation = '||l_config_creation,1);
3675: oe_debug_pub.add('Reduce_vendor_by_ou: Mode = '||p_mode,1);
3676:
3677: If p_mode = 'ORDER' and nvl(l_config_creation,1) in (1,2) then
3678: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors for this case based on sourcing chain',1);
3679:
3680: For oper_unit_rec in oper_unit_cur
3681: Loop
3682: l_oper_unit_list(oper_unit_rec.organization_id) := oper_unit_rec.organization_id;
3694:
3695: End if;
3696:
3697: If l_oper_unit_list.exists(l_oper_unit) then
3698: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is part of the sourcing chain',1);
3699: x_vendor_details(j) := p_vendor_details(i);
3700: j := j + 1;
3701: else
3702: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is not part of the sourcing chain',1);
3698: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is part of the sourcing chain',1);
3699: x_vendor_details(j) := p_vendor_details(i);
3700: j := j + 1;
3701: else
3702: oe_debug_pub.add('Vendor site '||p_vendor_details(i).vendor_site_id||' is not part of the sourcing chain',1);
3703: End if;
3704: i := p_vendor_details.next(i);
3705: End Loop;
3706: Else
3707: -- Added for MOAC
3708: -- Reducing records which are not having vendor site
3709:
3710: If PG_DEBUG <> 0 Then
3711: oe_debug_pub.add('Reduce_vendor_by_ou: cib based on model', 3);
3712: End if;
3713:
3714: i := p_vendor_details.first;
3715:
3719: x_vendor_details(j) := p_vendor_details(i);
3720: j := j + 1;
3721: else
3722: If PG_DEBUG <> 0 Then
3723: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor='
3724: ||p_vendor_details(i).vendor_id||' as the vendor site is null',3);
3725: End if;
3726: end if;
3727: i := p_vendor_details.next(i);
3729: -- End of MOAC change
3730: End if;
3731:
3732: If nvl(l_option_specific,3) in (1,2) then
3733: oe_debug_pub.add('Reduce_vendor_by_ou: Need to reduce vendors by OSS vendors',1);
3734:
3735: For oss_vendor_rec in oss_vendor_cur(l_assg_set_id)
3736: Loop
3737: l_vendor_list(oss_vendor_rec.vendor_id) := oss_vendor_rec.vendor_id;
3742: While (i is not null)
3743: Loop
3744: If l_vendor_list.exists(x_vendor_details(i).vendor_id)
3745: and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
3746: oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
3747: Else
3748: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
3749: oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
3750: x_vendor_details.delete(i);
3744: If l_vendor_list.exists(x_vendor_details(i).vendor_id)
3745: and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
3746: oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
3747: Else
3748: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
3749: oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
3750: x_vendor_details.delete(i);
3751: End if;
3752: i := x_vendor_details.next(i);
3745: and l_vendor_site_list.exists(x_vendor_details(i).vendor_site_id) then
3746: oe_debug_pub.add('Reduce_vendor_by_ou: Vendor is valid ',1);
3747: Else
3748: oe_debug_pub.add('Reduce_vendor_by_ou: Removing vendor id = '||x_vendor_details(i).vendor_id,1);
3749: oe_debug_pub.add('Reduce_vendor_by_ou: Removing Vendor site = '||x_vendor_details(i).vendor_site_id,1);
3750: x_vendor_details.delete(i);
3751: End if;
3752: i := x_vendor_details.next(i);
3753: End Loop;
3928:
3929: -- for all the sales order lines (entered, booked )
3930: -- Given parameters.
3931: IF PG_DEBUG <> 0 THEN
3932: oe_debug_pub.add('Create_purchase_doc_batch: '
3933: || '+---------------------------------------------------------------------------+',1);
3934:
3935: oe_debug_pub.add('Create_purchase_doc_batch: '
3936: || '+------------------ Parameters passed into the procedure ------------------+',1);
3931: IF PG_DEBUG <> 0 THEN
3932: oe_debug_pub.add('Create_purchase_doc_batch: '
3933: || '+---------------------------------------------------------------------------+',1);
3934:
3935: oe_debug_pub.add('Create_purchase_doc_batch: '
3936: || '+------------------ Parameters passed into the procedure ------------------+',1);
3937:
3938: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3934:
3935: oe_debug_pub.add('Create_purchase_doc_batch: '
3936: || '+------------------ Parameters passed into the procedure ------------------+',1);
3937:
3938: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3935: oe_debug_pub.add('Create_purchase_doc_batch: '
3936: || '+------------------ Parameters passed into the procedure ------------------+',1);
3937:
3938: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3936: || '+------------------ Parameters passed into the procedure ------------------+',1);
3937:
3938: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3937:
3938: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3938: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3939: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(p_sales_order_line_id),1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3940: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Organization_id : '||p_organization_id,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3941: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Offset Days : '||to_char(p_offset_days),1);
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3949:
3942: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Overwrite flag : '||p_overwrite_list_price,1);
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3949:
3950: oe_debug_pub.add('Create_purchase_doc_batch: '
3943: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Configuration ItemID: '||p_config_id,1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3949:
3950: oe_debug_pub.add('Create_purchase_doc_batch: '
3951: || '+---------------------------------------------------------------------------+',1);
3944: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Base Model Id : '||p_base_model_id,1);
3945: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Created Days ago : '||p_created_days_ago,1);
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3949:
3950: oe_debug_pub.add('Create_purchase_doc_batch: '
3951: || '+---------------------------------------------------------------------------+',1);
3952: END IF;
3946: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Load Type : '||p_load_type,1);
3947: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Upgrade : '||p_upgrade,1);
3948: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Perform Rollup : '||p_perform_rollup,1);
3949:
3950: oe_debug_pub.add('Create_purchase_doc_batch: '
3951: || '+---------------------------------------------------------------------------+',1);
3952: END IF;
3953:
3954: drive_mark := 0;
3958:
3959: /* sales order number or line id or offset is NOT passed */
3960:
3961: IF PG_DEBUG <> 0 THEN
3962: oe_debug_pub.add('Create_purchase_doc_batch: '||'Regular cursor.');
3963: END IF;
3964: sql_stmt := 'select distinct msi.inventory_item_id '||
3965: 'from mtl_system_items msi '||
3966: 'where msi.base_item_id is not null '||
3996: sql_stmt := sql_stmt || ' and msi.base_item_id is not null ';
3997: End if;
3998:
3999: If PG_DEBUG <> 0 Then
4000: oe_debug_pub.add('Create_purchasing_doc_batch: Dynamic SqlStmt : '||sql_stmt,1);
4001: End if;
4002: If drive_mark = 1 Then
4003: OPEN pprollup_cur FOR sql_stmt using p_organization_id;
4004: elsif drive_mark = 2 then
4037:
4038: /* sales order number or line id or offset is passed */
4039:
4040: IF PG_DEBUG <> 0 THEN
4041: oe_debug_pub.add('Create_purchase_doc_batch: '||'OE cursor.');
4042: END IF;
4043:
4044: OPEN pprollup_oe_cur FOR
4045: SELECT oel.line_id, oel.inventory_item_id,oel.ato_line_id
4137:
4138: elsif (p_upgrade = 1 ) then
4139:
4140: IF PG_DEBUG <> 0 THEN
4141: oe_debug_pub.add('Create_purchase_doc_batch: '||'Upgrade');
4142: END IF;
4143:
4144: if p_perform_rollup = 2 then
4145: oe_debug_pub.add('Perform Rollup parameter is set to NO.');
4141: oe_debug_pub.add('Create_purchase_doc_batch: '||'Upgrade');
4142: END IF;
4143:
4144: if p_perform_rollup = 2 then
4145: oe_debug_pub.add('Perform Rollup parameter is set to NO.');
4146: RETCODE := 0 ;
4147: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
4148: return;
4149: end if;
4197: v_rec_count := v_rec_count + 1;
4198:
4199: -- Log all the record being processed.
4200: IF PG_DEBUG <> 0 THEN
4201: oe_debug_pub.add('Create_purchase_doc_batch: '
4202: || '+-------- Processing for --------------------------------------------------+',1);
4203:
4204: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4205: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4200: IF PG_DEBUG <> 0 THEN
4201: oe_debug_pub.add('Create_purchase_doc_batch: '
4202: || '+-------- Processing for --------------------------------------------------+',1);
4203:
4204: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4205: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4206: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4207: oe_debug_pub.add('Create_purchase_doc_batch: '
4208: || '+--------------------------------------------------------------------------+',1);
4201: oe_debug_pub.add('Create_purchase_doc_batch: '
4202: || '+-------- Processing for --------------------------------------------------+',1);
4203:
4204: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4205: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4206: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4207: oe_debug_pub.add('Create_purchase_doc_batch: '
4208: || '+--------------------------------------------------------------------------+',1);
4209: END IF;
4202: || '+-------- Processing for --------------------------------------------------+',1);
4203:
4204: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4205: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4206: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4207: oe_debug_pub.add('Create_purchase_doc_batch: '
4208: || '+--------------------------------------------------------------------------+',1);
4209: END IF;
4210:
4203:
4204: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales order : '||p_sales_order ,1);
4205: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Sales Order Line ID : '||to_char(ll_line_id),1);
4206: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Item : '||to_char(ll_inventory_item_id),1);
4207: oe_debug_pub.add('Create_purchase_doc_batch: '
4208: || '+--------------------------------------------------------------------------+',1);
4209: END IF;
4210:
4211: --- 4172156
4232:
4233: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4234: RETCODE := 1;
4235: IF PG_DEBUG <> 0 THEN
4236: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Expected Error in Create_purchasing_doc.',1);
4237: END IF;
4238: err_counter := err_counter + 1;
4239: erroredItems(err_counter).config_item_id := ll_inventory_item_id;
4240: ROLLBACK TO pp_rollup;
4240: ROLLBACK TO pp_rollup;
4241:
4242: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4243: IF PG_DEBUG <> 0 THEN
4244: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'UnExpected Error in Create_purchasing_doc.',1);
4245: END IF;
4246: ROLLBACK TO pp_rollup;
4247: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4248: ELSE
4246: ROLLBACK TO pp_rollup;
4247: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4248: ELSE
4249: IF PG_DEBUG <> 0 THEN
4250: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Successfully processed.',1);
4251: END IF;
4252: pass_counter := pass_counter + 1;
4253: passedItems(pass_counter).config_item_id := ll_inventory_item_id;
4254: END IF;
4259: END LOOP;
4260:
4261: if pprollup_cur%ISOPEN then
4262: IF PG_DEBUG <> 0 THEN
4263: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Fetched '|| pprollup_cur%ROWCOUNT ||' rows');
4264: END IF;
4265:
4266: close pprollup_cur;
4267: end if;
4267: end if;
4268:
4269: if pprollup_oe_cur%ISOPEN then
4270: IF PG_DEBUG <> 0 THEN
4271: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Fetched '|| pprollup_oe_cur%ROWCOUNT ||' rows');
4272: END IF;
4273: close pprollup_oe_cur;
4274: end if;
4275:
4275:
4276:
4277: -- Print the successfully processed items..
4278: if (pass_counter > 0 AND PG_DEBUG <> 0) then
4279: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4280: oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4281: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4282:
4283: for j in 1 .. pass_counter
4276:
4277: -- Print the successfully processed items..
4278: if (pass_counter > 0 AND PG_DEBUG <> 0) then
4279: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4280: oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4281: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4282:
4283: for j in 1 .. pass_counter
4284: loop
4277: -- Print the successfully processed items..
4278: if (pass_counter > 0 AND PG_DEBUG <> 0) then
4279: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4280: oe_debug_pub.add(' Following items are processed successfully while performing Purchase Price Rollup.' ) ;
4281: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4282:
4283: for j in 1 .. pass_counter
4284: loop
4285: if ( passedItems(j).config_item_id > 0 ) then
4293: FROM mtl_system_items_kfv kfv
4294: WHERE kfv.inventory_item_id = passedItems(j).config_item_id
4295: AND rownum = 1;
4296:
4297: oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4298:
4299: exception
4300: when OTHERS then
4301: oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4297: oe_debug_pub.add (' '|| j ||'.'||' '|| l_pass_config_description || '(item id '||passedItems(j).config_item_id ||')');
4298:
4299: exception
4300: when OTHERS then
4301: oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4302: end;
4303: end if;
4304: end loop;
4305: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4301: oe_debug_pub.add ('**Failed to get description for item id '|| passedItems(j).config_item_id );
4302: end;
4303: end if;
4304: end loop;
4305: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4306: end if;
4307:
4308:
4309: -- Print the errored records..
4307:
4308:
4309: -- Print the errored records..
4310: if (err_counter > 0 AND PG_DEBUG <> 0) then
4311: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4312: oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4313: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4314:
4315: for j in 1 .. err_counter
4308:
4309: -- Print the errored records..
4310: if (err_counter > 0 AND PG_DEBUG <> 0) then
4311: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4312: oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4313: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4314:
4315: for j in 1 .. err_counter
4316: loop
4309: -- Print the errored records..
4310: if (err_counter > 0 AND PG_DEBUG <> 0) then
4311: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4312: oe_debug_pub.add(' Following items failed while performing Purchase Price Rollup.' ) ;
4313: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4314:
4315: for j in 1 .. err_counter
4316: loop
4317: if ( erroredItems(j).config_item_id > 0 ) then
4325: FROM mtl_system_items_kfv kfv
4326: WHERE kfv.inventory_item_id = erroredItems(j).config_item_id
4327: AND rownum = 1;
4328:
4329: oe_debug_pub.add (' '|| j ||'.'||' '|| l_err_config_description ||
4330: '(item id '||erroredItems(j).config_item_id ||')');
4331:
4332: exception
4333: when OTHERS then
4330: '(item id '||erroredItems(j).config_item_id ||')');
4331:
4332: exception
4333: when OTHERS then
4334: oe_debug_pub.add ('**Failed to get description for item id '|| erroredItems(j).config_item_id );
4335: end;
4336: end if;
4337: end loop;
4338: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4334: oe_debug_pub.add ('**Failed to get description for item id '|| erroredItems(j).config_item_id );
4335: end;
4336: end if;
4337: end loop;
4338: oe_debug_pub.add('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++' ) ;
4339: end if;
4340:
4341:
4342: -- Launch the concurrent program as needed
4341:
4342: -- Launch the concurrent program as needed
4343:
4344: IF PG_DEBUG <> 0 THEN
4345: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Calling Submit_pdoi_conc_prog.');
4346: END IF;
4347:
4348: Submit_pdoi_conc_prog(
4349: p_oper_unit_list => x_oper_unit_list,
4353: x_msg_data => x_msg_data);
4354:
4355: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4356: IF PG_DEBUG <> 0 THEN
4357: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'Expected Error in Submit_pdoi_conc_prog.',1);
4358: END IF;
4359: raise FND_API.G_EXC_ERROR;
4360:
4361: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4359: raise FND_API.G_EXC_ERROR;
4360:
4361: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4362: IF PG_DEBUG <> 0 THEN
4363: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'UnExpected Error in Submit_pdoi_conc_prog.',1);
4364: END IF;
4365: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4366: END IF;
4367:
4375:
4376: END IF;
4377:
4378: IF PG_DEBUG <> 0 THEN
4379: 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...');
4380: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'ROLLUP BLANKETS FOR
4381: END IF;
4382:
4383: COMMIT;
4376: END IF;
4377:
4378: IF PG_DEBUG <> 0 THEN
4379: 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...');
4380: oe_debug_pub.add('Create_purchase_doc_batch: ' || 'ROLLUP BLANKETS FOR
4382:
4383: COMMIT;
4384:
4385: EXCEPTION
4386:
4387: when FND_API.G_EXC_UNEXPECTED_ERROR then
4388: IF PG_DEBUG <> 0 THEN
4389: oe_debug_pub.add('Create_purchase_doc_batch:: unexp error::'||l_stmt_num||sqlerrm,1);
4390: END IF;
4391: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4392: CTO_MSG_PUB.Count_And_Get
4393: (p_msg_count => x_msg_count
4397: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4398:
4399: when FND_API.G_EXC_ERROR then
4400: IF PG_DEBUG <> 0 THEN
4401: oe_debug_pub.add('Create_purchase_doc_batch::exp error::'||l_stmt_num||sqlerrm,1);
4402: END IF;
4403: x_return_status := FND_API.G_RET_STS_ERROR;
4404: CTO_MSG_PUB.Count_And_Get
4405: (p_msg_count => x_msg_count
4408: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
4409:
4410: when others then
4411: IF PG_DEBUG <> 0 THEN
4412: oe_debug_pub.add('Create_purchase_doc_batch::others::'||l_stmt_num||sqlerrm,1);
4413: END IF;
4414: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4415: CTO_MSG_PUB.Count_And_Get
4416: (p_msg_count => x_msg_count
4445: while (i is not null)
4446: loop
4447:
4448: --- Launch the concurrent program
4449: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Launching program for oper unit ='||to_char(p_oper_unit_list(i).oper_unit));
4450: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Batch id = '||to_char(p_batch_id),1);
4451:
4452: l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4453: If l_release_method is null then
4446: loop
4447:
4448: --- Launch the concurrent program
4449: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Launching program for oper unit ='||to_char(p_oper_unit_list(i).oper_unit));
4450: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'Batch id = '||to_char(p_batch_id),1);
4451:
4452: l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4453: If l_release_method is null then
4454: l_rel_method_value := null;
4451:
4452: l_release_method := FND_PROFILE.VALUE('CTO_PRICING_RELEASE_METHOD');
4453: If l_release_method is null then
4454: l_rel_method_value := null;
4455: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is null...',1);
4456: elsif l_release_method = 2 then
4457: l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4458: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4459: elsif l_release_method = 1 then
4454: l_rel_method_value := null;
4455: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is null...',1);
4456: elsif l_release_method = 2 then
4457: l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4458: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4459: elsif l_release_method = 1 then
4460: l_rel_method_value := 'CREATE';
4461: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4462: elsif l_release_method = 3 then
4457: l_rel_method_value := 'CREATE_AND_APPROVE'; /* bug#2633259 */
4458: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release ...',1);
4459: elsif l_release_method = 1 then
4460: l_rel_method_value := 'CREATE';
4461: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4462: elsif l_release_method = 3 then
4463: l_rel_method_value := 'MANUAL';
4464: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Release Using AutoCreate ...',1);
4465: end if;
4460: l_rel_method_value := 'CREATE';
4461: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Automatic Release/Review ...',1);
4462: elsif l_release_method = 3 then
4463: l_rel_method_value := 'MANUAL';
4464: oe_debug_pub.add('Submit_pdoi_conc_prog: '||' Release method is Release Using AutoCreate ...',1);
4465: end if;
4466:
4467: -- fnd_client_info.set_org_context(p_oper_unit_list(i).oper_unit);
4468:
4504:
4505:
4506:
4507:
4508: oe_debug_pub.add('Submit_pdoi_conc_prog: '||'pdoi concurrent request = '||to_char(l_request_id)||' is submitted',1);
4509: i := p_oper_unit_list.next(i);
4510:
4511: end loop;
4512:
4576:
4577: g_pg_level := g_pg_level + 3;
4578: lstmtNumber := 10;
4579: if PG_DEBUG <> 0 Then
4580: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Inside Process Purchase Price',5);
4581: end if;
4582:
4583: lstmtNumber := 20;
4584: select distinct nvl(fsp.inventory_organization_id,0)
4599: where inventory_item_id = p_config_item_id
4600: and rownum = 1;
4601:
4602: If PG_DEBUG <> 0 Then
4603: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of Validation orgs = '||l_orgs_list.count,5);
4604: End if;
4605: lStmtNumber := 30;
4606: If l_orgs_list.count <> 0 Then
4607: for i in l_orgs_list.first..l_orgs_list.last
4605: lStmtNumber := 30;
4606: If l_orgs_list.count <> 0 Then
4607: for i in l_orgs_list.first..l_orgs_list.last
4608: Loop
4609: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Validation org = '||l_orgs_list(i),5);
4610: lStmtNumber := 40;
4611: Select list_price_per_unit
4612: into l_list_price
4613: from mtl_system_items
4614: where inventory_item_id = p_config_item_id
4615: and organization_id = l_orgs_list(i);
4616:
4617: If pg_debug <> 0 Then
4618: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: List price in item master = '||l_list_price,5);
4619: End if;
4620: -- Renga Talk to val regarding the custom hook
4621:
4622: x_rolled_price := CTO_CUSTOM_LIST_PRICE_PK.get_list_price(
4639: x_msg_data => x_msg_data);
4640:
4641: if x_return_status = FND_API.G_RET_STS_ERROR then
4642: IF PG_DEBUG <> 0 THEN
4643: oe_debug_pub.add('Create_Purchasing_Doc: '
4644: || ' Expected error in Rollup_list_procedure',1);
4645: END IF;
4646: raise FND_API.G_EXC_ERROR;
4647: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
4645: END IF;
4646: raise FND_API.G_EXC_ERROR;
4647: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
4648: IF PG_DEBUG <> 0 THEN
4649: oe_debug_pub.add('Create_Purchasing_Doc: '
4650: || ' Unexpected error in rollup_list_price ',1);
4651: END IF;
4652: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4653: end if;
4664:
4665: lStmtNumber := 50;
4666: If l_list_price is null or p_overwrite_list_price = 'Y' Then
4667: If PG_DEBUG <> 0 Then
4668: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Updating item master with list price ',5);
4669: End if;
4670:
4671: Update Mtl_system_items
4672: set list_price_per_unit = x_rolled_price
4673: where inventory_item_id = p_config_item_id
4674: and organization_id = l_orgs_list(i)
4675: and (P_overwrite_list_price = 'Y' or list_price_per_unit is null);
4676: If pg_debug <> 0 Then
4677: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Number of rows updated = '||sql%rowcount,1);
4678: End if;
4679: End if; /* l_list_price is null or p_overwrite_list_price = 'Y' */
4680: End Loop;
4681: End if;
4697: Exception when no_data_found then
4698: l_buy_found := 'N';
4699: End;
4700: If l_buy_found = 'N' then
4701: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
4702: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
4703: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
4704: return;
4705: End if;
4698: l_buy_found := 'N';
4699: End;
4700: If l_buy_found = 'N' then
4701: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
4702: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
4703: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
4704: return;
4705: End if;
4706: End if;
4699: End;
4700: If l_buy_found = 'N' then
4701: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config creation type is 1/2',1);
4702: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: No Buy org found',1);
4703: oe_debug_pub.add(lpad(' ',g_pg_level)||'PROCESS_PURCHASE_PRICE: Config will not be rolled up',1);
4704: return;
4705: End if;
4706: End if;
4707:
4717: x_msg_data => x_msg_data);
4718:
4719: if x_return_status = FND_API.G_RET_STS_ERROR then
4720: IF PG_DEBUG <> 0 THEN
4721: oe_debug_pub.add('Create_Purchasing_Doc: '
4722: || ' Expected error in Rollup_purchase_price procedure',1);
4723: END IF;
4724: raise FND_API.G_EXC_ERROR;
4725: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
4723: END IF;
4724: raise FND_API.G_EXC_ERROR;
4725: elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
4726: IF PG_DEBUG <> 0 THEN
4727: oe_debug_pub.add('Create_Purchasing_Doc: '
4728: || ' Unexpected error in rollup_purchase_price procedure',1);
4729: EnD IF;
4730: raise FND_API.G_EXC_UNEXPECTED_ERROR;
4731: end if;
4735: exception
4736:
4737: when FND_API.G_EXC_UNEXPECTED_ERROR then
4738: IF PG_DEBUG <> 0 THEN
4739: oe_debug_pub.add('PROCESS_PURCHASE_PRICE:: unexp error::'||lStmtNumber||sqlerrm,1);
4740: END IF;
4741: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4742: CTO_MSG_PUB.Count_And_Get
4743: (p_msg_count => x_msg_count
4746: g_pg_level := g_pg_level - 3;
4747:
4748: when FND_API.G_EXC_ERROR then
4749: IF PG_DEBUG <> 0 THEN
4750: oe_debug_pub.add('PROCESS_PURCHASE_PRICE::exp error::'||lStmtNumber||sqlerrm,1);
4751: END IF;
4752: x_return_status := FND_API.G_RET_STS_ERROR;
4753: CTO_MSG_PUB.Count_And_Get
4754: (p_msg_count => x_msg_count
4756: g_pg_level := g_pg_level - 3;
4757:
4758: when others then
4759: IF PG_DEBUG <> 0 THEN
4760: oe_debug_pub.add('PROCESS_PURCHASE_PRICE::others::'||lStmtNumber||sqlerrm,1);
4761: END IF;
4762: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4763: CTO_MSG_PUB.Count_And_Get
4764: (p_msg_count => x_msg_count
4889: and cal2.calendar_date = trunc(oel.schedule_ship_date))) ';
4890: end if;
4891:
4892: IF PG_DEBUG <> 0 THEN
4893: oe_debug_pub.add ('The dyanamic sql generated is');
4894: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
4895: oe_debug_pub.add (substr(sql_stmt,1501,3000));
4896: oe_debug_pub.add ('The drive_mark is '||drive_mark);
4897: END IF;
4890: end if;
4891:
4892: IF PG_DEBUG <> 0 THEN
4893: oe_debug_pub.add ('The dyanamic sql generated is');
4894: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
4895: oe_debug_pub.add (substr(sql_stmt,1501,3000));
4896: oe_debug_pub.add ('The drive_mark is '||drive_mark);
4897: END IF;
4898:
4891:
4892: IF PG_DEBUG <> 0 THEN
4893: oe_debug_pub.add ('The dyanamic sql generated is');
4894: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
4895: oe_debug_pub.add (substr(sql_stmt,1501,3000));
4896: oe_debug_pub.add ('The drive_mark is '||drive_mark);
4897: END IF;
4898:
4899: /*
4892: IF PG_DEBUG <> 0 THEN
4893: oe_debug_pub.add ('The dyanamic sql generated is');
4894: oe_debug_pub.add ('SQL: ' || substr(sql_stmt,1, 1500));
4895: oe_debug_pub.add (substr(sql_stmt,1501,3000));
4896: oe_debug_pub.add ('The drive_mark is '||drive_mark);
4897: END IF;
4898:
4899: /*
4900: Below, we execute the sql statement according to which parameters
4966: -- Order_Number, Line_id, Organization_id and Offset_Days is passed
4967: EXECUTE IMMEDIATE sql_stmt USING p_sales_order, p_sales_order_line_id, l_organization_id, p_offset_days;
4968:
4969: else
4970: oe_debug_pub.add ('INCORRECT COMBINATION of parameters');
4971:
4972: end if;
4973:
4974:
4972: end if;
4973:
4974:
4975: IF PG_DEBUG <> 0 THEN
4976: oe_debug_pub.add('load_lines_into_bcolt: ' || 'no. of records in temp table '||SQL%ROWCOUNT,1);
4977: END IF;
4978:
4979: EXCEPTION
4980: WHEN OTHERS THEN
4977: END IF;
4978:
4979: EXCEPTION
4980: WHEN OTHERS THEN
4981: oe_debug_pub.add('load_lines_into_bcolt: ' || 'others excpn::'||sqlerrm,1);
4982: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4983:
4984:
4985: END load_lines_into_bcolt;
4997: where line_id = p_line_id;
4998:
4999: EXCEPTION
5000: WHEN OTHERS THEN
5001: oe_debug_pub.add('update_bcolt_line_status: ' || 'others excpn::'||sqlerrm,1);
5002: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5003:
5004: END update_bcolt_line_status;
5005:
5049: FROM oe_order_lines_all
5050: WHERE line_id = p_interface_source_line_id;
5051:
5052: IF PG_DEBUG <> 0 THEN
5053: oe_debug_pub.add('Get_opm_charge_account: ' || 'Customer ID : '||to_char(l_cust_id),3);
5054: oe_debug_pub.add('Get_opm_charge_account: ' || 'customer site ID : '||to_char(l_cust_site_id),3);
5055: END IF;
5056:
5057:
5050: WHERE line_id = p_interface_source_line_id;
5051:
5052: IF PG_DEBUG <> 0 THEN
5053: oe_debug_pub.add('Get_opm_charge_account: ' || 'Customer ID : '||to_char(l_cust_id),3);
5054: oe_debug_pub.add('Get_opm_charge_account: ' || 'customer site ID : '||to_char(l_cust_site_id),3);
5055: END IF;
5056:
5057:
5058:
5071: AND mic.category_set_id = mdsv.category_set_id
5072: AND mdsv.functional_area_id = 2;
5073:
5074: IF PG_DEBUG <> 0 THEN
5075: oe_debug_pub.add('Get_opm_charge_account: ' || 'Category id: '||to_char(l_category_id),5);
5076: END IF;
5077:
5078:
5079: --added this api after taking to divya reddy(PO)
5083: --inorder to be consistent will pass it as done by PO.
5084:
5085: l_stmt_num := 30;
5086: IF PG_DEBUG <> 0 THEN
5087: oe_debug_pub.add('Get_opm_charge_account: ' || 'Calling MRP_SOURCING_API_PK.mrp_sourcing',5);
5088: END IF;
5089: --call mrp_sourcing to get vendor and vendor_site_id
5090: l_return_code := MRP_SOURCING_API_PK.mrp_sourcing(
5091: arg_mode =>p_source_type_code,
5102: arg_error_message =>l_error_message);
5103:
5104: IF l_return_code THEN
5105: IF PG_DEBUG <> 0 THEN
5106: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5107: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5108: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5109: END IF;
5110:
5103:
5104: IF l_return_code THEN
5105: IF PG_DEBUG <> 0 THEN
5106: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5107: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5108: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5109: END IF;
5110:
5111:
5104: IF l_return_code THEN
5105: IF PG_DEBUG <> 0 THEN
5106: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from MRP_SOURCING_API_PK.mrp_sourcing',3);
5107: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor ID : '||to_char(l_vendor_id),3);
5108: oe_debug_pub.add('Get_opm_charge_account: ' || 'Vendor site ID : '||to_char(l_vendor_site_id),3);
5109: END IF;
5110:
5111:
5112: ELSE
5110:
5111:
5112: ELSE
5113: IF PG_DEBUG <> 0 THEN
5114: oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5115: oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5116: --talked to usha arora. This error is not important enough to
5117: --stop process
5118: oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5111:
5112: ELSE
5113: IF PG_DEBUG <> 0 THEN
5114: oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5115: oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5116: --talked to usha arora. This error is not important enough to
5117: --stop process
5118: oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5119: END IF;
5114: oe_debug_pub.add('Get_opm_charge_account:'||'Failed in MRP_SOURCING_API_PK.mrp_sourcing' ,1);
5115: oe_debug_pub.add('Get_opm_charge_account:'||l_error_message,1);
5116: --talked to usha arora. This error is not important enough to
5117: --stop process
5118: oe_debug_pub.add('Get_opm_charge_account:'||'Ignoring above error and proceeding further',1);
5119: END IF;
5120: END IF;
5121:
5122: --updating the global record structure variable
5143:
5144:
5145: l_stmt_num := 40;
5146: IF PG_DEBUG <> 0 THEN
5147: oe_debug_pub.add('Get_opm_charge_account: ' || 'calling GMF_transaction_accounts_PUB.get_accounts',5);
5148:
5149: END IF;
5150: --SLA api to get charge and accrual account
5151: --Reference aru#4610085
5165: x_charge_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(1).target_ccid;
5166:
5167: IF x_charge_account_id IS NULL THEN
5168: IF PG_DEBUG <> 0 THEN
5169: oe_debug_pub.add('Get_opm_charge_account: ' || 'charge account id is NULL in opm org',1);
5170: oe_debug_pub.add('Get_opm_charge_account: ' || 'USER SHOULD CHECK THE SETUP',1);
5171: END IF;
5172:
5173:
5166:
5167: IF x_charge_account_id IS NULL THEN
5168: IF PG_DEBUG <> 0 THEN
5169: oe_debug_pub.add('Get_opm_charge_account: ' || 'charge account id is NULL in opm org',1);
5170: oe_debug_pub.add('Get_opm_charge_account: ' || 'USER SHOULD CHECK THE SETUP',1);
5171: END IF;
5172:
5173:
5174: END IF;
5175:
5176: x_accrual_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).target_ccid;
5177:
5178: IF PG_DEBUG <> 0 THEN
5179: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5180: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5181: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5182: END IF;
5183:
5176: x_accrual_account_id := GMF_transaction_accounts_PUB.g_gmf_accts_tab_CTO(2).target_ccid;
5177:
5178: IF PG_DEBUG <> 0 THEN
5179: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5180: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5181: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5182: END IF;
5183:
5184:
5177:
5178: IF PG_DEBUG <> 0 THEN
5179: oe_debug_pub.add('Get_opm_charge_account: ' || 'success from GMF_transaction_accounts_PUB.get_accounts',3);
5180: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla charge acct : '||to_char(x_charge_account_id),1);
5181: oe_debug_pub.add('Get_opm_charge_account: ' || 'opm sla accrual acct : '||to_char(x_accrual_account_id),1);
5182: END IF;
5183:
5184:
5185: ELSE
5189: EXCEPTION
5190:
5191: WHEN FND_API.G_EXC_ERROR THEN
5192: IF PG_DEBUG <> 0 THEN
5193: oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::exp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5194: END IF;
5195: x_return_status := FND_API.G_RET_STS_ERROR;
5196: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5197: IF PG_DEBUG <> 0 THEN
5194: END IF;
5195: x_return_status := FND_API.G_RET_STS_ERROR;
5196: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5197: IF PG_DEBUG <> 0 THEN
5198: oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::unexp error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5199: END IF;
5200: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5201: WHEN OTHERS THEN
5202: IF PG_DEBUG <> 0 THEN
5199: END IF;
5200: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5201: WHEN OTHERS THEN
5202: IF PG_DEBUG <> 0 THEN
5203: oe_debug_pub.add('Get_opm_charge_account: ' || 'Get_opm_charge_account::others error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
5204: END IF;
5205: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5206:
5207: END Get_opm_charge_account;