oracle销售退货业务,Oracle EBS OM RMA销售退货异常处理(Datafix)

declare

l_line_id

number:=263115;

l_ordered_qty

number;

cursorline_infois

selectline_id, ordered_quantity

fromoe_order_lines_all ool

whereool.line_id = l_line_id

andool.flow_status_code =‘AWAITING_RETURN‘

andexists

(select‘x‘

frommtl_material_transactions mmt, rcv_transactions rcv

wheremmt.trx_source_line_id = ool.line_id

andmmt.transaction_type_id =15

andrcv.oe_order_line_id = ool.line_id

andmmt.rcv_transaction_id = rcv.transaction_id)

forupdatenowait;

l_user_id

number;

l_resp_id

number;

l_appl_id

number;

x_return_status

varchar2(10);

x_msg_count

number;

x_msg_data

varchar2(2000);

begin

ifnvl(l_line_id,0) >0then

openline_info;

fetchline_info

intol_line_id, l_ordered_qty;

ifline_info%notfoundthen

closeline_info;

dbms_output.put_line(‘Error: Invalid Line Id, Re-enter.‘);

return;

endif;

closeline_info;

else

return;

endif;

Begin

selectnumber_value

intol_user_id

fromwf_item_attribute_values

whereitem_type =‘OEOL‘

anditem_key = l_line_id

andname=‘USER_ID‘;

selectnumber_value

intol_resp_id

fromwf_item_attribute_values

whereitem_type =‘OEOL‘

anditem_key = l_line_id

andname=‘RESPONSIBILITY_ID‘;

selectnumber_value

intol_appl_id

fromwf_item_attribute_values

whereitem_type =‘OEOL‘

anditem_key = l_line_id

andname=‘APPLICATION_ID‘;

Exception

WhenNo_Data_FoundThen

dbms_output.put_line(‘Error: Line flow does not exist.‘);

return;

End;

fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);

updateoe_order_lines

setfulfilled_quantity =null,

shipped_quantity   =

null,

last_updated_by    = -99999999,

last_update_date   =

sysdate

whereline_id = l_line_id;

begin

oe_rma_receiving.push_receiving_info(l_line_id,

l_ordered_qty,

‘NO PARENT‘,

‘RECEIVE‘,

‘N‘,

x_return_status,

x_msg_count,

x_msg_data);

ifx_return_status =‘S‘then

oe_rma_receiving.push_receiving_info(l_line_id,

l_ordered_qty,

‘RECEIVE‘,

‘DELIVER‘,

‘N‘,

x_return_status,

x_msg_count,

x_msg_data);

endif;

oe_debug_pub.add(‘no. of OE messages :‘|| x_msg_count,1);

dbms_output.put_line(‘no. of OE messages :‘|| x_msg_count);

forkin1.. x_msg_countloop

x_msg_data := oe_msg_pub.get(p_msg_index => k, p_encoded =>

‘F‘);

oe_debug_pub.add(substr(x_msg_data,1,255));

oe_debug_pub.add(substr(x_msg_data,255,length(x_msg_data)));

dbms_output.put_line(‘Error msg: ‘||substr(x_msg_data,1,200));

endloop;

fnd_msg_pub.count_and_get(p_encoded =>

‘F‘,

p_count   => x_msg_count,

p_data    => x_msg_data);

oe_debug_pub.add(‘no. of FND messages :‘|| x_msg_count,1);

dbms_output.put_line(‘no. of FND messages :‘|| x_msg_count);

forkin1.. x_msg_countloop

x_msg_data := fnd_msg_pub.get(p_msg_index => k, p_encoded =>

‘F‘);

dbms_output.put_line(‘Error msg: ‘||substr(x_msg_data,1,200));

oe_debug_pub.add(substr(x_msg_data,1,255));

endloop;

ifx_return_status <>‘S‘then

oe_debug_pub.add(‘Error occurred, rolling back changes.‘,1);

dbms_output.put_line(‘Error occurred, please fix the errors and retry.‘);

rollback;

else

commit;

endif;

end;

dbms_output.put_line(‘For details, see OM Debug File: ‘||

OE_DEBUG_PUB.G_DIR || ‘/‘|| OE_DEBUG_PUB.G_FILE);

end;