-- CREATE TEMP TABLE FOR ROUTINGS...
CREATE TABLE DKC_ROUTINGS_STG
(
OWNER_ORGN_CODE VARCHAR2(4),
ROUTING_NO VARCHAR2(32),
ROUTING_VERSION NUMBER(5),
ROUTING_QTY NUMBER,
ITEM_UOM VARCHAR2(4),
ROUTING_DESC VARCHAR2(40),
OWNER VARCHAR2(10),
ROUTING_CLASS VARCHAR2(4),
STEP_NO NUMBER,
STEP_QTY NUMBER,
OPERATION VARCHAR2(16),
PLANNED_LOSS NUMBER,
PROCESS_STATUS CHAR(1),
ERROR_TEXT VARCHAR2(2000 )
)
/
--- ROUTING GENERATION API
DECLARE
l_message_count number;
l_message_list varchar2(2000);
l_return_status varchar2(1) ;
l_user_id number := 1130; -- fnd_global.user_id;
l_owner_id number;
l_owner_organization_id number;
l_operation_id varchar2 (16) ;
gc_routing_status number := 700;
l_tbl_count number;
v_routing_rec_type gmd_routings%rowtype;
v_routing_step_tbl_type gmd_routings_pub.gmd_routings_step_tab;
v_update_tbl_rec_type gmd_routings_pub.gmd_routings_step_dep_tab;
v_routing_step_tbl_dummy gmd_routings_pub.gmd_routings_step_tab;
v_update_tbl_rec_dummy gmd_routings_pub.gmd_routings_step_dep_tab;
l_rej_rec_cnt number;
l_suc_rec_cnt number;
l_out_index number :=0;
l_owner number:=1130;
cursor c_rtg_hdr is
select distinct owner_orgn_code
,routing_no
,routing_version
,routing_qty
,item_uom
,routing_desc
,owner
,routing_class
from dkc_routings_stg
where owner_orgn_code = '404'
AND routing_no not in (select routing_no from gmd_routings)
and operation in (select oprn_no from gmd_operations)
order by routing_no;
cursor routing_cur(p_routing_no varchar2,p_routing_ver number)
is
select distinct
step_no
,step_qty
,routing_version
,operation
from dkc_routings_stg
where routing_no = p_routing_no
and routing_version = p_routing_ver
and nvl(process_status,'E') IN('E','U')
order by step_no;
l_temp varchar2(50);
BEGIN
FND_GLOBAL.APPS_INITIALIZE('1130', '50886','552');
dbms_output.put_line('l_user_id '||l_user_id);
FOR routing_cur_rec IN c_rtg_hdr LOOP
v_routing_step_tbl_type :=v_routing_step_tbl_dummy;
v_update_tbl_rec_type :=v_update_tbl_rec_dummy;
dbms_output.put_line('ROUTING NO '||routing_cur_rec.routing_no);
BEGIN
SELECT user_id
INTO l_owner_id
FROM fnd_user
WHERE user_name = routing_cur_rec.owner;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log,'NO DATA ERROR: WHILE GETTING OWNER ID:');
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'OTHERS ERROR: WHILE GETTING OWNER ID:'||SUBSTR (SQLERRM,1,200) );
END;
begin
select organization_id
into l_owner_organization_id
from mtl_parameters
where organization_code =routing_cur_rec.owner_orgn_code;
dbms_output.put_line('l_owner_organization_id '||l_owner_organization_id);
exception when others then
dbms_output.put_line('ERROR OCCURED WHILE PICKING DATA FOR ORGANIZATION');
end;
v_routing_rec_type.OWNER_ORGANIZATION_ID := 107; --l_owner_organization_id;
v_routing_rec_type.routing_no := routing_cur_rec.routing_no;
v_routing_rec_type.routing_vers := routing_cur_rec.routing_version;
v_routing_rec_type.routing_qty := routing_cur_rec.routing_qty;
v_routing_rec_type.routing_uom := routing_cur_rec.item_uom;
v_routing_rec_type.routing_class := routing_cur_rec.routing_class;
v_routing_rec_type.creation_date := sysdate;
v_routing_rec_type.created_by := 1130;
v_routing_rec_type.last_update_date := sysdate;
v_routing_rec_type.last_updated_by := 1130;--l_user_id;
v_routing_rec_type.owner_id := 1130; --l_owner_id;
v_routing_rec_type.routing_status := 100;
v_routing_rec_type.routing_desc := routing_cur_rec.routing_desc;
v_routing_rec_type.delete_mark := 0;
l_tbl_count := 1;
--fnd_file.put_line(fnd_file.log,'v_routing_rec_type.routing_qty values is :'||v_routing_rec_type.routing_qty);
For rotg_dtl_rec IN routing_cur(routing_cur_rec.routing_no,routing_cur_rec.routing_version) Loop
Begin
BEGIN
SELECT oprn_id
INTO l_operation_id
FROM gmd_operations_vl
WHERE oprn_no = rotg_dtl_rec.operation
and oprn_vers = rotg_dtl_rec.routing_version;
dbms_output.put_line('l_operation_id '||l_operation_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log,'NO DATA ERROR: WHILE GETTING OPERATION ID:');
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'OTHERS ERROR: WHILE GETTING OPERATION ID:'||SUBSTR (SQLERRM,1,200) );
END;
v_routing_step_tbl_type(l_tbl_count).routingstep_no := rotg_dtl_rec.step_no;
v_routing_step_tbl_type(l_tbl_count).oprn_id := 107; -- l_operation_id;
v_routing_step_tbl_type(l_tbl_count).step_qty := rotg_dtl_rec.step_qty;
v_routing_step_tbl_type(l_tbl_count).last_updated_by := 1130; --l_user_id;
v_routing_step_tbl_type(l_tbl_count).created_by := 1130; --l_user_id;
v_routing_step_tbl_type(l_tbl_count).last_update_date:= sysdate;
v_routing_step_tbl_type(l_tbl_count).creation_date := sysdate;
l_tbl_count := l_tbl_count + 1;
End;
End Loop;
l_temp := routing_cur_rec.routing_no;
FND_GLOBAL.APPS_INITIALIZE('1130', '50886','552');
gmd_routings_pub.insert_routing ( p_api_version => 1
, p_init_msg_list => TRUE
, p_commit => TRUE
, p_routings => v_routing_rec_type --v_routing_stg_tbl_type
, p_routings_step_tbl => v_routing_step_tbl_type --v_routing_step_stg_tbl_type
, p_routings_step_dep_tbl => v_update_tbl_rec_type
, x_message_count => l_message_count
, x_message_list => l_message_list
, x_return_status => l_return_status
);
dbms_output.put_line('l_return_status '||l_return_status);
dbms_output.put_line('l_message_list '||l_message_list);
update dkc_routings_stg
SET process_status = l_return_status
, error_text = l_message_list
WHERE routing_no = routing_cur_rec.routing_no
AND routing_version = routing_cur_rec.routing_version;
fnd_file.put_line(fnd_file.log,'Return Status is : '||l_return_status||''||'Error Message is:'||l_message_list);
IF l_message_count > 0 AND l_return_status <> 'S' THEN
fnd_file.put_line(fnd_file.log,'Error Message in INSERTION OF ROUTING :'||SUBSTR(SQLERRM, 1, 255));
END IF;
IF l_return_status ='S' THEN
Begin
Update gmd_routings_b
set routing_status = 700
where routing_no = routing_cur_rec.routing_no
and routing_vers = routing_cur_rec.routing_version;
Exception when Others Then
Null;
End;
ELSif l_return_status in ('U','E') THEN
For i IN 1 .. l_message_count Loop
FND_MSG_PUB.get(p_msg_index => i,
p_encoded => 'F',
p_data => l_message_list,
p_msg_index_out => l_out_index);
dbms_output.put_line('l_message_list = '||l_message_list);
end loop;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log,'No Data Found Exception Message: ');
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error Message: ' || SUBSTR(SQLERRM, 1, 255));
END;