Shipping lock fix –> WSH_DELIVERY_DETAILS Column has leading or trailing spaces

SO# 无法 Shipping，有时由于发货表列内容左右带空格导致。

 

 

set serveroutput onsize 1000000

 

 

set verify off

 

 

 

 

ACCEPT DEL_DETAIL_ID NUMBERPROMPT'PLEASE ENTER DELIVERY_DETAIL_ID : '

 

 

 

 

DECLARECURSOR C1 isSelect column_name from fnd_columns Where table_id =(Select table_id from fnd_tables Where table_name ='WSH_DELIVERY_DETAILS');

 

 

 

 

Cnt NUMBER(10):= 0;

 

 

DynSql VARCHAR2(1000);

 

 

DelDetId WSH_DELIVERY_DETAILS.Delivery_Detail_Id %TYPE;

 

 

 

 

BEGIN

 

 

dbms_output.put_line('Script to identify the problematic fields :');

 

 

dbms_output.put_line('============================================');

 

 

 

 

FOR i IN C1 LOOP

 

 

 

 

DelDetId := &DEL_DETAIL_ID;

 

 

DynSql :='SELECT COUNT(*) FROM WSH_DELIVERY_DETAILS ';

 

 

DynSql := DynSql || 'WHERE NVL(LENGTH(LTRIM(RTRIM(' || i.column_name || '))), 0) <> ';

 

 

DYNSql := DynSql || ' LENGTH(' || i.column_name || ') ';

 

 

DynSql := DynSql || 'AND Delivery_Detail_Id = ' || DelDetId;

 

 

 

 

EXECUTEIMMEDIATE DynSql INTO Cnt;

 

 

 

 

IF( cnt > 0 )THEN

 

 

  dbms_output.put_line('Field ' ||i.COLUMN_NAME|| ' for delivery detail '|| DelDetId || ' has leading or trailing spaces');

 

 

 

 

ENDIF;

 

 

 

 

ENDLOOP;

 

 

 

 

dbms_output.put_line('Script completed succefully.......');

 

 

 

 

END;