從事oracle erp系統工作建立的固定函式

多年來應用oracle erp中建到的function

[@more@]

1, get_frist_line_id
2, get_from_primal_line_id
3, get_om_mtr_reason_name
4, get_om_mtr_reason_description
5, get_shipped_subinventory
6, get_supname
7, gobo_char_to_mon
8, gobo_convert
9, gobo_getnumber
10, gobo_get_available
11, gobo_get_bucket_information
12, gobo_get_container,
13, gobo_get_container_item
14, gobo_get_container_unit_volume
15, gobo_get_container_weight
16, gobo_get_customer_item
17, gobo_get_customer_packing
18, gobo_get_day_on
19, gobo_get_item_cost
20, gobo_get_item_number
21, gobo_get_ordered_item
22, gobo_get_pi_by_shipno
23, gobo_get_pi_invoice_number
24, gobo_get_pi_ship_number
25, gobo_get_po_by_shipno
26, gobo_get_ship_header_id
27, gobo_get_uom_rate
28, gobo_item_org
29, gobo_om_get_contain
30, numbertoenglish

/*
1:
在oracle erp的order lines中,由於分批出貨,常會把一個line分成幾個(如把1.1分成

1.1,1.2,1.3等).
又因為往往只有1.1才會有來源可追蹤,如訂單來自po,edi或otc等,相應的1.2,1.3則要通

過1.1來跟蹤
下面就是透過匯入訂單頭id與單身line_number(只要1,不要1.1/1.3等),返回1.1這個

line_id
*/
CREATE OR REPLACE FUNCTION get_frist_line_id
(p_header_id in number,p_line_number number) RETURN number IS
retu_num number;
BEGIN
select line_id into retu_num
from oe_order_lines_all
where header_id=p_header_id
and line_number=p_line_number
and shipment_number=1;
return (retu_num);
END;
/

/*
2:
功用同上,這裡多了個沒有取消的判斷.
*/
CREATE OR REPLACE FUNCTION GET_FROM_PRIMAL_LINE_ID
(IN_HEADER_ID IN NUMBER,IN_LINE_NUMBER IN NUMBER,IN_LINE_ID NUMBER)
RETURN NUMBER
IS
PRIMAL_LINE_ID NUMBER;
BEGIN
SELECT distinct LINE_ID
INTO PRIMAL_LINE_ID
FROM OE_ORDER_LINES_ALL
WHERE HEADER_ID=IN_HEADER_ID
AND LINE_NUMBER=IN_LINE_NUMBER
AND FLOW_STATUS_CODE<>'CANCELLED'
AND SHIPMENT_NUMBER=1;
IF PRIMAL_LINE_ID = '' OR PRIMAL_LINE_ID IS NULL THEN
PRIMAL_LINE_ID:=IN_LINE_ID;
END IF;
RETURN PRIMAL_LINE_ID;
END;
/

/*
3:
取得在收料時輸入退貨原因
因為同一訂單line可能會有多次退貨,多種原因,故用此來取得,多個時用' / '分開
*/
CREATE OR REPLACE function get_om_mtr_reason_name
(p_line_id in number,p_header_id in number)
return varchar2 is
c_return varchar2(2000);
cursor cur is
SELECT distinct MTR.REASON_NAME REASON_CODE,mtr.DESCRIPTION
FROM RCV_TRANSACTIONS RT ,
MTL_TRANSACTION_REASONS MTR,
RCV_ROUTING_HEADERS RRH
WHERE RT.TRANSACTION_TYPE != 'UNORDERED' AND
RT.SOURCE_DOCUMENT_CODE='RMA' and
MTR.REASON_NAME is not null and
RRH.ROUTING_HEADER_ID = RT.ROUTING_HEADER_ID AND
MTR.REASON_ID = RT.REASON_ID and
rt.OE_ORDER_LINE_ID=p_line_id AND
rt.OE_ORDER_HEADER_ID=p_header_id
order by MTR.REASON_NAME;

begin

for rec in cur loop
c_return:=c_return||rec.reason_code||' / ';
end loop;
if length(c_return)>3 then
c_return:=substr(c_return,1,length(c_return)-3);
end if;
return c_return;
end ;
/

/*
4:
同上
取得在收料時輸入退貨原因的敘述
因為同一訂單line可能會有多次退貨,多種原因,故用此來取得,多個時用' / '分開
*/
CREATE OR REPLACE function get_om_mtr_reason_DESCRIPTION (p_line_id in

number,p_header_id in number)
return varchar2 is
c_return varchar2(2000);
cursor cur is
SELECT distinct MTR.REASON_NAME REASON_CODE,mtr.DESCRIPTION
FROM RCV_TRANSACTIONS RT ,
MTL_TRANSACTION_REASONS MTR,
RCV_ROUTING_HEADERS RRH
WHERE RT.TRANSACTION_TYPE != 'UNORDERED' AND
RT.SOURCE_DOCUMENT_CODE='RMA' and
MTR.REASON_NAME is not null and
RRH.ROUTING_HEADER_ID = RT.ROUTING_HEADER_ID AND
MTR.REASON_ID = RT.REASON_ID and
rt.OE_ORDER_LINE_ID=p_line_id AND
rt.OE_ORDER_HEADER_ID=p_header_id
order by MTR.REASON_NAME;

begin
for rec in cur loop
c_return:=c_return||rec.DESCRIPTION||' / ';
end loop;
if length(c_return)>3 then
c_return:=substr(c_return,1,length(c_return)-3);
end if;
return c_return;
end ;
/

/*
5:
一個訂單line的分批出貨往往造成會從不同的庫別出貨
這就是匯入line_id,返回出貨的所有庫別,多個則用','分開
*/
CREATE OR REPLACE function get_shipped_subinventory
(order_org_id in number,order_header_id in number,order_line_id

in number)
return varchar2 is

hubie varchar2(100);

cursor hbcur is
select distinct subinventory
from wsh_delivery_details
where nvl(top_model_line_id,source_line_id)=nvl(order_line_id,-1)
and source_header_id=nvl(order_header_id,-1)
and org_id=nvl(order_org_id,-1)

order by subinventory;

begin
for rec in hbcur loop
hubie := hubie||rec.subinventory||',';
end loop;
if length(hubie)>=3 then
hubie := substr(hubie,1,length(hubie)-1);
end if;
return hubie;
end;
/

/*
6:
gobo_om_supply是因應otc的客製而建立的
取得一個訂單中分發給的所有工廠名,多個用'/'分開
*/
CREATE OR REPLACE FUNCTION get_supname (p_order_number in number,
p_request_date in varchar2,
p_ship_date in varchar2,
p_sold_to_org_id in number)
RETURN varchar2 IS
retu_supname varchar2(2000);
ls_sup varchar2(240);

cursor ls_cur is
SELECT distinct
C.SUP_NAME
FROM OE_ORDER_HEADERS_ALL A,
OE_ORDER_LINES_ALL B,
GOBO_OM_SUPPLY C
WHERE
A.ORDER_NUMBER=p_ORDER_NUMBER
AND B.HEADER_ID=A.HEADER_ID
and

to_char(nvl(B.REQUEST_DATE,sysdate+10000),'yy/mm/dd')=nvl(p_request_date,to_ch

ar(sysdate+10000,'yy/mm/dd'))
and

to_char(nvl(b.actual_shipment_date,sysdate+10000),'yy/mm/dd')=nvl(p_ship_date,

to_char(sysdate+10000,'yy/mm/dd'))
and b.SOLD_TO_ORG_ID=p_SOLD_TO_ORG_ID
AND C.SUP_CODE=B.ATTRIBUTE5
order by C.SUP_NAME ;

BEGIN
open ls_cur;
loop
fetch ls_cur into ls_sup;
exit when ls_cur%notfound;
retu_supname := retu_supname||'/'||ls_sup;
end loop;
retu_supname := substr(retu_supname,2);
return retu_supname;
END;
/

/*
7:
傳入月份英文簡稱,返回數字月份
*/
CREATE OR REPLACE FUNCTION GOBO_CHAR_TO_MON (C_MON IN VARCHAR) RETURN VARCHAR2

IS
N_MON VARCHAR2(2);
BEGIN
IF C_MON IS NULL THEN
RETURN NULL;
END IF;
SELECT DECODE(C_MON,
'JAN','01',
'FEB','02',
'MAR','03',
'APR','04',
'MAY','05',
'JUN','06',
'JUL','07',
'AUG','08',
'SEG','09',
'OCT','10',
'NOV','11',
'DEC','12',
NULL) INTO N_MON
FROM DUAL;
RETURN N_MON;
END;
/

/*
8:
傳入數字返回中文大寫
*/
CREATE OR REPLACE function gobo_convert(num number) return varchar2 is
v_shuzi varchar2(10);
v_fenwei varchar2(10);
v_input varchar2(20);
v_output varchar2(200);
v_1 varchar2(100);
v_2 varchar2(100);
v_3 varchar2(100);
v_len number(10);
i number(3);
begin
v_input:=to_char(round(num,2));
if instr(v_input,'.')>0 then
v_1:=substr(v_input,1,instr(v_input,'.')-1);
v_2:=substr(v_input,instr(v_input,'.')+1);
else
v_1:=v_input;
end if;
v_len:=length(v_1);
if v_len>10 then
v_len:=10;
end if;
if v_len>1 then
for i in 2..v_len loop
if substr(v_1,-i,1)>'0' then
v_shuzi:=gobo_getnumber(to_number(substr(v_1,-i,1)));
v_fenwei:=gobo_getfenwei(i);
v_output:=v_shuzi||v_fenwei||v_output;
else
if substr(v_1,-i+1,1)>'0' then
v_output:='零'||v_output;
end if;
if i=5 and (substr(v_1,-(i+1),1)>'0' or substr(v_1,-(i+2),1)>'0' or

substr(v_1,-(i+3),1)>'0') then
v_output:='萬'||v_output;
end if;
if i=9 and (substr(v_1,-i-1,1)>'0' or substr(v_1,-i-2,1)>'0' or

substr(v_1,-i-3,1)>'0' or substr(v_1,-i-4,1)>'0') then
v_output:='億'||v_output;
end if;
end if ;
end loop;
if substr(v_1,-1,1)>'0' then
v_shuzi:=gobo_getnumber(substr(v_1,-1,1));
v_output:=v_output||v_shuzi||'元';
else
v_output:=v_output||'元';
end if;
else
if v_1>'0' then
v_shuzi:=gobo_getnumber(to_number(v_1));
v_output:=v_shuzi||'角';
end if;
end if;
if v_2 is not null then
if substr(v_2,1,1)>'0' then
v_shuzi:=gobo_getnumber(to_number(substr(v_2,1,1)));
v_3:=v_shuzi||'¨零';
end if;
if substr(v_2,2,1)>'0' then
v_shuzi:=gobo_getnumber(to_number(substr(v_2,2,1)));
if v_3 is null then
v_3:='¹s';
end if;
v_3:=v_3||v_shuzi||'分';
else
v_3:=v_3||'零分';
end if;
end if;
if v_3 is null then
if v_output is null then
v_output:='';
else
v_output:=v_output||'整';
end if;
else
v_output:=v_output||v_3;
end if;
return v_output;
end;
/

/*
9:
*/
CREATE OR REPLACE function gobo_getNumber(num number) return varchar2 is
v_result varchar2(5);
begin
select decode(num,
0,'零',
1,'壹',
2,'貳',
3,'參',
4,'肆',
5,'伍',
6,'陸',
7,'柒',
8,'捌',
9,'玖')
into v_result
from dual;
return v_result;
end;
/

/*
10:
傳入item_id與分廠id,返回可用庫存量(扣除保留)
*/
CREATE OR REPLACE FUNCTION GOBO_GET_AVAILABLE(IN_ITEM_ID IN

NUMBER,IN_FROM_ORG_ID IN NUMBER) RETURN NUMBER AS
AVAI_QTY NUMBER;
BEGIN
SELECT NVL(SUM(NVL(MOQ.TRANSACTION_QUANTITY,0)),0)
-NVL(SUM(NVL(MR.RESERVATION_QUANTITY,0)),0) INTO AVAI_QTY
FROM MTL_ONHAND_QUANTITIES MOQ,
MTL_RESERVATIONS MR,
MTL_ITEM_REVISIONS MIR,
MTL_SECONDARY_INVENTORIES MS
WHERE MOQ.INVENTORY_ITEM_ID=NVL(IN_ITEM_ID,-1)
AND MOQ.ORGANIZATION_ID=NVL(IN_FROM_ORG_ID,-1)
AND MR.STAGED_FLAG IS NULL
AND MR.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
AND MR.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID
AND MS.ORGANIZATION_ID(+)=MOQ.ORGANIZATION_ID
AND MS.SECONDARY_INVENTORY_NAME(+)=MOQ.SUBINVENTORY_CODE
AND MS.RESERVABLE_TYPE(+) = 1
AND NVL(MR.REVISION,'-1')=MIR.REVISION(+)
AND MIR.INVENTORY_ITEM_ID(+)=MR.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID(+)=MR.ORGANIZATION_ID
AND SYSDATE <= NVL(MIR.ECN_INITIATION_DATE(+),SYSDATE)
AND SYSDATE >= NVL(MIR.IMPLEMENTATION_DATE(+),SYSDATE);

IF AVAI_QTY IS NULL THEN
AVAI_QTY:=0;
END IF;
RETURN AVAI_QTY;
END;
/

/*
11:
為dashboard管理報表整理資料而建
傳入兩個日期,取得差值,返回範圍表示方式
*/
CREATE OR REPLACE FUNCTION GOBO_GET_BUCKET_INFORMATION(BASEDAY DATE,DUEDAY

DATE) RETURN VARCHAR2 IS
V_RESULT VARCHAR2(20);
BEGIN
IF BASEDAY - DUEDAY <1 THEN
RETURN 'CURRENT';
END IF;
IF BASEDAY - DUEDAY >=1 AND BASEDAY - DUEDAY <31 THEN
RETURN '1~30';
END IF;
IF BASEDAY - DUEDAY >=31 AND BASEDAY - DUEDAY <61 THEN
RETURN '31~60';
END IF;
IF BASEDAY - DUEDAY >=61 AND BASEDAY - DUEDAY <91 THEN
RETURN '61~90';
END IF;
IF BASEDAY - DUEDAY >=91 AND BASEDAY - DUEDAY <181 THEN
RETURN '91~180';
END IF;
IF BASEDAY - DUEDAY >=181 AND BASEDAY - DUEDAY <366 THEN
RETURN '181~365';
END IF;
IF BASEDAY - DUEDAY >=366 AND BASEDAY - DUEDAY <731 THEN
RETURN '366~730';
END IF;
IF BASEDAY - DUEDAY >=731 THEN
RETURN 'OVER 2';
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
/

/*
12:
傳入item_id與分廠id,返回箱容
*/
CREATE OR REPLACE FUNCTION GOBO_GET_CONTAINER (V_INVENTORY_ITEM_ID IN

NUMBER,V_ORGANIZATION_ID IN NUMBER)
RETURN NUMBER AS
LOAD_QTY NUMBER;
CURSOR CUR IS
SELECT MAX_LOAD_QUANTITY
FROM wsh_container_items c
WHERE 1=1
AND EXISTS
(
SELECT 1
FROM WSH_CONTAINER_ITEMS CI
WHERE 1=1
AND CI.MASTER_ORGANIZATION_ID =

C.MASTER_ORGANIZATION_ID
AND CI.LOAD_ITEM_ID = C.LOAD_ITEM_ID
AND CI.MASTER_LOCATION_ID = C.MASTER_LOCATION_ID
GROUP BY CI.MASTER_ORGANIZATION_ID , CI.LOAD_ITEM_ID ,

CI.MASTER_LOCATION_ID
HAVING MAX(

CI.PREFERRED_FLAG||TO_CHAR(cI.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss') )
=

C.PREFERRED_FLAG||TO_CHAR(c.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss')
)
AND c.master_organization_id=V_organization_id
and c.load_item_id=V_inventory_item_id;
BEGIN
open cur;
fetch cur into load_qty;
close cur;
return load_qty;
end;
/

/*
13:
傳入item_id與分廠id,返回外箱item
*/
CREATE OR REPLACE FUNCTION GOBO_GET_CONTAINER_ITEM (V_INVENTORY_ITEM_ID IN

NUMBER,V_ORGANIZATION_ID IN NUMBER)
RETURN VARCHAR AS
CONTAINER_ITEM mtl_system_items_b.SEGMENT1%TYPE;
CURSOR CUR IS
SELECT msi.segment1
FROM wsh_container_items c,
mtl_system_items_b msi
WHERE 1=1
and msi.inventory_item_id=c.CONTAINER_ITEM_ID
and msi.organization_id=c.MASTER_ORGANIZATION_ID
AND EXISTS
(
SELECT 1
FROM WSH_CONTAINER_ITEMS CI
WHERE 1=1
AND CI.MASTER_ORGANIZATION_ID =

C.MASTER_ORGANIZATION_ID
AND CI.LOAD_ITEM_ID = C.LOAD_ITEM_ID
AND CI.MASTER_LOCATION_ID = C.MASTER_LOCATION_ID
GROUP BY CI.MASTER_ORGANIZATION_ID , CI.LOAD_ITEM_ID ,

CI.MASTER_LOCATION_ID
HAVING MAX(

CI.PREFERRED_FLAG||TO_CHAR(cI.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss') )
=

C.PREFERRED_FLAG||TO_CHAR(c.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss')
)
AND c.master_organization_id=V_organization_id
and c.load_item_id=V_inventory_item_id
;
BEGIN
open cur;
fetch cur into CONTAINER_ITEM;
close cur;
return CONTAINER_ITEM;
end;
/

/*
14:
傳入item_id與分廠id,返回外箱材質
*/
CREATE OR REPLACE FUNCTION GOBO_GET_CONTAINER_UNIT_VOLUME(V_INVENTORY_ITEM_ID

IN NUMBER,V_ORGANIZATION_ID IN NUMBER)
RETURN NUMBER AS
UNIT_VOLUME NUMBER;
CURSOR CUR IS
SELECT msi.UNIT_VOLUME
FROM wsh_container_items c,
mtl_system_items_b msi
WHERE 1=1
and msi.inventory_item_id=c.CONTAINER_ITEM_ID
and msi.organization_id=c.MASTER_ORGANIZATION_ID
AND EXISTS
(
SELECT 1
FROM WSH_CONTAINER_ITEMS CI
WHERE 1=1
AND CI.MASTER_ORGANIZATION_ID =

C.MASTER_ORGANIZATION_ID
AND CI.LOAD_ITEM_ID = C.LOAD_ITEM_ID
AND CI.MASTER_LOCATION_ID = C.MASTER_LOCATION_ID
GROUP BY CI.MASTER_ORGANIZATION_ID , CI.LOAD_ITEM_ID ,

CI.MASTER_LOCATION_ID
HAVING MAX(

CI.PREFERRED_FLAG||TO_CHAR(cI.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss') )
=

C.PREFERRED_FLAG||TO_CHAR(c.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss')
)
AND c.master_organization_id=V_organization_id
and c.load_item_id=V_inventory_item_id
;
BEGIN
open cur;
fetch cur into UNIT_VOLUME;
close cur;
return UNIT_VOLUME;
end;
/

/*
15:
傳入item_id與分廠id,返回外箱重量
*/
CREATE OR REPLACE FUNCTION GOBO_GET_CONTAINER_WEIGHT (V_INVENTORY_ITEM_ID IN

NUMBER,V_ORGANIZATION_ID IN NUMBER)
RETURN NUMBER AS
CONTAINER_WEIGHT NUMBER;
CURSOR CUR IS
SELECT msi.UNIT_WEIGHT
FROM wsh_container_items c,
mtl_system_items_b msi
WHERE 1=1
and msi.inventory_item_id=c.CONTAINER_ITEM_ID
and msi.organization_id=c.MASTER_ORGANIZATION_ID
AND EXISTS
(
SELECT 1
FROM WSH_CONTAINER_ITEMS CI
WHERE 1=1
AND CI.MASTER_ORGANIZATION_ID =

C.MASTER_ORGANIZATION_ID
AND CI.LOAD_ITEM_ID = C.LOAD_ITEM_ID
AND CI.MASTER_LOCATION_ID = C.MASTER_LOCATION_ID
GROUP BY CI.MASTER_ORGANIZATION_ID , CI.LOAD_ITEM_ID ,

CI.MASTER_LOCATION_ID
HAVING MAX(

CI.PREFERRED_FLAG||TO_CHAR(cI.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss') )
=

C.PREFERRED_FLAG||TO_CHAR(c.LAST_UPDATE_DATE,'yyyy.mm.dd hh24:mm:ss')
)
AND c.master_organization_id=V_organization_id
and c.load_item_id=V_inventory_item_id
;
BEGIN
open cur;
fetch cur into CONTAINER_WEIGHT;
close cur;
return CONTAINER_WEIGHT;
end;
/

/*
16:
傳入工廠item與客戶id,返回客戶item
*/
CREATE OR REPLACE function gobo_get_customer_item(v_item_id in

number,v_customer_id in number) return varchar2 is
cursor c1 is (select distinct b.CUSTOMER_ITEM_NUMBER
from MTL_CUSTOMER_ITEM_XREFS_V b
where v_ITEM_ID=b.INVENTORY_ITEM_ID
and v_CUSTOMER_ID=b.CUSTOMER_ID
and b.INACTIVE_FLAG='N'
and b.RANK = ( SELECT MIN(a1.RANK) FROM MTL_CUSTOMER_ITEM_XREFS_V a1
WHERE a1.CUSTOMER_ID =b.CUSTOMER_ID
AND a1.INVENTORY_ITEM_ID = b.INVENTORY_ITEM_ID
AND a1.INACTIVE_FLAG = 'N')
);
prno varchar2(50);
result varchar2(200);
begin
open c1;
loop
fetch c1 into prno;
exit when c1%notfound ;
if result is null then
result:=prno;
else
result:=result||','||prno;
end if;
end loop;
close c1;
return result;
exception
when no_data_found then
return '';
when others then
return '';
end;
/

/*
17:
傳入工廠item與訂單id,返回包裝說明
*/
CREATE OR REPLACE function gobo_get_customer_packing
(item_id in number,so_header_id in number) return varchar2 is
cursor c1 is (select distinct ol.ATTRIBUTE1
from oe_order_lines_all ol
where ol.HEADER_ID=so_header_id
and ol.INVENTORY_ITEM_ID=item_id);
prno varchar2(50);
result varchar2(200);
begin
open c1;
loop
fetch c1 into prno;
exit when c1%notfound ;
if result is null then
result:=prno;
else
result:=result||','||prno;
end if;
end loop;
close c1;
return result;
exception
when no_data_found then
return '';
when others then
return '';
end;
/

/*
18:
返回分廠的有效工作日數
*/
CREATE OR REPLACE function GOBO_GET_DAY_ON(P_DATE date, ORG_CODE VARCHAR2)
return Date is
v_returnd Date:= p_date;
v_counts integer:= 0;

BEGIN

loop

if to_char(v_returnd,'D')=7 then
v_returnd := v_returnd + 2;
--elsif to_char(v_returnd,'D')=1 then
-- v_returnd := v_returnd + 1;
else
v_returnd := v_returnd + 1;
end if;

select CNTS
INTO v_counts
from
(
SELECT COUNT(*) CNTS
FROM BOM_CALENDAR_EXCEPTIONS
WHERE CALENDAR_CODE = ORG_CODE
AND ( trunc(EXCEPTION_DATE) = trunc(v_returnd) )
) ;

if (v_counts=0) and (to_char(v_returnd,'D')>=2 and

to_char(v_returnd,'D')<=6) then
exit;
end if;

end loop;

return(v_returnd);
end GOBO_GET_DAY_ON;
/

/*
19:
返回item cost
*/
CREATE OR REPLACE function gobo_get_item_cost(org_id in number,item_id in

number,ctype_id in number) return number is
V_t_prdnm varchar2(15);
V_l_prdnm varchar2(15);
result number;
V_ou_id number;
begin
V_t_prdnm:=to_char(sysdate,'MON-YY');
select decode(V_t_prdnm,
'JAN-02','DEC-01',
'JAN-03','DEC-02',
'JAN-04','DEC-03',
'JAN-05','DEC-04',
'JAN-06','DEC-05',
'JAN-07','DEC-06',
'JAN-08','DEC-07',
'JAN-09','DEC-08',
'JAN-10','DEC-09',
'JAN-11','DEC-10',
decode(substr(V_t_prdnm,1,3),
'JAN','DEC',
'FEB','JAN',
'MAR','FEB',
'APR','MAR',
'MAY','APR',
'JUN','MAY',
'JUL','JUN',
'AUG','JUL',
'SEP','AUG',
'OCT','SEP',
'NOV','OCT',
'DEC','NOV')||'-'||substr(V_t_prdnm,5,2)
) into V_l_prdnm
from dual;
--
begin
select operating_unit into V_ou_id
from org_organization_definitions
where organization_id=org_id;
exception
when no_data_found then
return null;
when others then
return null;
end;
--
begin
select gcmth.END_COST into result
from gobo_cst_mtl_txn_headers gcmth
where gcmth.ORG_ID=V_ou_id
and gcmth.INVENTORY_ITEM_ID=item_id
and gcmth.PERIOD_NAME=V_l_prdnm;
exception
when no_data_found then
result:=null;
when others then
result:=null;
end;
--
if result is null or result =0 then
begin
select cic.item_cost into result
from cst_item_costs cic
where cic.organization_id=org_id
and cic.INVENTORY_ITEM_ID=item_id
and cic.COST_TYPE_ID=ctype_id;
exception
when no_data_found then
return null;
when others then
return null;
end;
end if;
--
if result is null or result =0 then
begin
select cic.item_cost into result
from cst_item_costs cic
where cic.organization_id=org_id
and cic.INVENTORY_ITEM_ID=item_id
and cic.COST_TYPE_ID=1000;
exception
when no_data_found then
return null;
when others then
return null;
end;
end if;
--
return result;
exception
when no_data_found then
return null;
when others then
return null;
end;
/

/*
20:
依傳入引數1或非1返回item料號或敘述
*/
CREATE OR REPLACE function gobo_get_item_number
(org_id integer,item_id number,reco integer) return varchar2 is
item_num varchar2(20);
item_desc varchar2(240);
begin
select msi.segment1,msi.description
into item_num,item_desc
from mtl_system_items_b msi
where msi.organization_id=org_id and
msi.inventory_item_id=item_id;
if reco=1 then
return(item_num);
else
return(item_desc);
end if;
end gobo_get_item_number;
/

/*
21:
返回訂單的來源單號/客戶單號/下單料號
*/
CREATE OR REPLACE function gobo_get_ordered_item(p_LINE_LOCATION_ID in number)

return varchar2 is
cursor c1 is (
select distinct prl.REFERENCE_NUM ,oh.CUST_PO_NUMBER,ol.ordered_item
from PO_REQUISITION_LINES_ALL PRL,oe_order_headers_all

oh,oe_order_lines_all ol
where LINE_LOCATION_ID=p_LINE_LOCATION_ID
and oh.ORDER_NUMBER(+) = prl.REFERENCE_NUM
and ol.line_id(+)=prl.ATTRIBUTE14);
prno1 varchar2(100);
prno2 varchar2(100);
prno3 varchar2(100);
result1 varchar2(100);
result2 varchar2(100);
result3 varchar2(100);
result varchar2(200);
begin
open c1;
loop
fetch c1 into prno1,prno2,prno3;
exit when c1%notfound ;
if result1 is null then
result1:=prno1;
result2:=prno2;
result3:=prno3;
else
result1:=result1||','||prno1;
result2:=result2||','||prno2;
result3:=result3||','||prno3;
end if;
end loop;
close c1;
result:=result1||';'||result2||';'||result3;
return result;
exception
when no_data_found then
return '';
when others then
return '';
end;
/

/*
22:
返回客製的編排箱號中同一個ship_no中的所有訂單號
*/
CREATE OR REPLACE FUNCTION GOBO_GET_PI_BY_SHIPNO (P_SHIP_HEADER_ID IN NUMBER)

RETURN VARCHAR2 IS
CURSOR CUR IS
SELECT DISTINCT OH.ORDER_NUMBER
FROM OE_ORDER_LINES_ALL OL,
OE_ORDER_HEADERS_ALL OH,
GOBO_OM_SHIP_LINE_ALL GL
WHERE OH.HEADER_ID=OL.HEADER_ID
AND OH.ORG_ID=OL.ORG_ID
AND OL.LINE_ID=GL.ORDER_LINED_ID
AND GL.SHIP_HEADER_ID=P_SHIP_HEADER_ID
ORDER BY OH.ORDER_NUMBER ;
RETU_PI VARCHAR2(3000);

BEGIN
FOR REC IN CUR LOOP
RETU_PI := RETU_PI||REC.ORDER_NUMBER||' , ';
END LOOP;
IF LENGTH(RETU_PI)>3 THEN
RETU_PI := SUBSTR(RETU_PI,1,LENGTH(RETU_PI)-3);
END IF;
RETURN RETU_PI;
END;
/

/*
23:
*/
CREATE OR REPLACE function gobo_get_pi_invoice_number (orgid in

number,ship_headerid in number,delivery_detailid in number)
return varchar2 is
cursor order_list is
select

order_no||gobo_get_pi_ship_number(org_id,ship_header_id,order_no) pi
from (
select distinct org_id,ship_header_id,order_no
from gobo_om_ship_line_all
where 1=1
and org_id=orgid
and ship_header_id=ship_headerid
and delivery_detail_id=delivery_detailid
);
return_invoice varchar2(500);
begin
FOR REC IN order_list LOOP
return_invoice:=return_invoice||rec.pi;
end loop;
if length(return_invoice)>5 then
return_invoice:=substr(return_invoice,1,length(return_invoice)-3);
end if;
if length(return_invoice)>500 then
return_invoice:=substr(return_invoice,1,500);
end if;
return return_invoice;

EXCEPTION
WHEN NO_DATA_FOUND THEN
return '';
when others then
return '';
end ;
/

/*
24:
透過客製的編排箱號的處理先後判別訂單的出貨批次
*/
CREATE OR REPLACE function gobo_get_pi_ship_number
(orgid in number,ship_headerid in number,
orderno in gobo_om_ship_line_all.order_no%type)
return varchar2 is

order_qty number;

curr_shipno_qty number;

ship_number number;
return_no varchar2(10);
begin

SELECT sum(TOTAL_QTY) into curr_shipno_qty
FROM GOBO_OM_SHIP_LINE_ALL
WHERE 1=1
AND ORDER_NO = orderno
AND ORG_ID = ORGID
AND SHIP_HEADER_ID = SHIP_HEADERID;

SELECT sum(ordered_quantity) into order_qty
FROM oe_order_lines_ALL
WHERE 1=1
AND ORG_ID = ORGID
AND HEADER_ID =(select HEADER_ID from oe_order_headers_all

where org_id=orgid and order_number=orderno)
and flow_status_code<>'CANCELLED';
begin
SELECT count(*) into ship_number
FROM (
SELECT DISTINCT SHIP_HEADER_ID
FROM GOBO_OM_SHIP_LINE_ALL
WHERE 1=1
AND ORDER_NO =orderno
and org_id=orgid
and SHIP_HEADER_ID <=

SHIP_HEADERID
) ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
return ' , ';
when others then
return ' , ';
end;

if nvl(ship_number,0)=0 then
return ' , ';
else
if nvl(order_qty,0)=nvl(curr_shipno_qty,0) then
return ' , ';
else
return_no:='_'||lpad(ship_number,2,'0')||' , ';
return return_no;
end if;
end if;
end ;
/

/*
25:
返回客製的編排箱號同一ship_no對應訂單的所有客戶單號
*/
CREATE OR REPLACE FUNCTION GOBO_GET_PO_BY_SHIPNO (P_SHIP_HEADER_ID IN NUMBER)

RETURN VARCHAR2 IS
CURSOR CUR IS
SELECT DISTINCT OH.CUST_PO_NUMBER
FROM OE_ORDER_LINES_ALL OL,
OE_ORDER_HEADERS_ALL OH,
GOBO_OM_SHIP_LINE_ALL GL
WHERE OH.HEADER_ID=OL.HEADER_ID
AND OH.ORG_ID=OL.ORG_ID
AND OL.LINE_ID=GL.ORDER_LINED_ID
AND GL.SHIP_HEADER_ID=P_SHIP_HEADER_ID
ORDER BY OH.CUST_PO_NUMBER ;
RETU_PO VARCHAR2(3000);

BEGIN
FOR REC IN CUR LOOP
RETU_PO := RETU_PO||REC.CUST_PO_NUMBER||' , ';
END LOOP;
IF LENGTH(RETU_PO)>3 THEN
RETU_PO := SUBSTR(RETU_PO,1,LENGTH(RETU_PO)-3);
END IF;
RETURN RETU_PO;
END;
/

/*
26:
傳order line id返回客製的編排箱號之ship header id
*/
CREATE OR REPLACE function gobo_get_ship_header_id (in_line_id in number)

return number
is
n_ship_header_id gobo_om_ship_header_all.ship_header_id%type;
cursor ship_cursor is
select distinct gol.ship_header_id --ship_no
from gobo_om_ship_line_all gol--,
-- gobo_om_ship_header_all goh
where 1=1 and
-- goh.ship_header_id=gol.ship_header_id and
gol.order_lined_id=in_line_id and
exists (select 1
from WSH_DLVY_DELIVERABLES_V
where source_line_id=in_line_id

and
delivery_detail_id=gol.delivery_detail_id
) and
exists (select 1
from wsh_delivery_assignments
where

delivery_detail_id=gol.delivery_detail_id
and delivery_id=gol.delivery_id
)
order by gol.ship_header_id desc ;
begin
open ship_cursor ;
fetch ship_cursor into n_ship_header_id;
close ship_cursor;
return (n_ship_header_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
close ship_cursor;
n_ship_header_id := NULL;
RETURN (n_ship_header_id);
WHEN OTHERS THEN
close ship_cursor;
return (n_ship_header_id);
end;
/

/*
27:
返回item的單位換算率
*/
CREATE OR REPLACE function gobo_get_uom_rate
(P_UOM_CODE in VARCHAR2,P_ORG_ID IN NUMBER,P_ITEM_ID IN NUMBER) return number

is
result number;
begin
SELECT muc1.conversion_rate/muc2.conversion_rate
into result
from mtl_uom_conversions muc1,mtl_uom_conversions muc2,mtl_system_items_b

msi
where muc1.inventory_item_id=0
and muc1.UNIT_OF_MEASURE=P_UOM_CODE
and muc2.inventory_item_id=0
and muc2.uom_code=msi.primary_uom_code
and msi.inventory_item_id=P_ITEM_ID
and msi.organization_id=P_ORG_ID ;
if result is null then
result:=1;
end if;
return result;
exception
when no_data_found then
return 1;
when others then
return 1;
end;
/

/*
返回item的所有分廠
*/
CREATE OR REPLACE FUNCTION GOBO_ITEM_ORG(P_ITEM_ID IN NUMBER) RETURN VARCHAR2

IS
CURSOR C IS
SELECT OOD.ORGANIZATION_CODE
FROM MTL_SYSTEM_ITEMS_B MSI,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE MSI.INVENTORY_ITEM_ID=P_ITEM_ID
AND MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_STATUS_CODE='Active'
AND OOD.DISABLE_DATE IS NULL
AND MSI.ORGANIZATION_ID<>1
ORDER BY OOD.ORGANIZATION_CODE;
C_TEMP VARCHAR2(5);
RESULT VARCHAR2(1000);
BEGIN
OPEN C;
LOOP
FETCH C INTO C_TEMP;
EXIT WHEN C%NOTFOUND;
IF RESULT IS NULL THEN
RESULT:=C_TEMP;
ELSIF C_TEMP IS NOT NULL OR C_TEMP<>'' THEN
RESULT:=RESULT||','||C_TEMP;
END IF;
END LOOP;
CLOSE C;
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '';
WHEN OTHERS THEN
RETURN '';
END;
/

/*
29:
返回客製的編排箱號中的貨櫃號
*/
CREATE OR REPLACE function gobo_om_get_contain (v_line_id in number) return

varchar2 is
cursor cur is
SELECT DISTINCT GOP.CONTAIN
FROM GOBO_OM_PACKING_ALL GOP,
GOBO_OM_SHIP_LINE_ALL GOSL
WHERE GOP.SHIP_HEADER_ID=GOSL.SHIP_HEADER_ID
and GOP.CONTAIN is not null
aND GOSL.ORDER_LINED_ID=v_LINE_ID
order by gop.contain;
return_vc varchar2(300);
begin
for rec in cur loop
return_vc:=return_vc||rec.contain||' / ';
end loop;
if length(return_vc)>3 then
return_vc:=substr(return_vc,1,length(return_vc)-3);
end if;
return return_vc;
end;
/

/*
傳入數字,返回國際船務英文表示法(小數點最多取兩位)
如12345678.904,
將返回:TWELVE MILLION THREE HUNDRED FORTY FIVE THOUSAND SIX HUNDRED SEVENTY

EIGHT AND CENTS NINETY
*/
CREATE OR REPLACE FUNCTION NumberToEnglish(V_NUM IN NUMBER) RETURN CHAR IS
TYPE SA_ARR IS VARRAY(20) OF VARCHAR2(20);
TYPE SB_ARR IS VARRAY(10) OF VARCHAR2(20);
TYPE SC_ARR IS VARRAY(19) OF VARCHAR2(30);
-- 1 到 19
AA SA_ARR :=

SA_ARR('ONE','TWO','THREE','FOUR','FIVE','SIX','SEVEN','EIGHT','NINE','TEN','E

LEVEN','TWELVE','THIRTEEN','FOURTEEN','FIFTEEN','SIXTEEN','SEVENTEEN','EIGHTEE

N','NINETEEN','TWENTY');
-- 20 到 100 每個都是 10 * NUM
BB SB_ARR :=

SB_ARR('','TWENTY','THIRTY','FORTY','FIFTY','SIXTY','SEVENTY','EIGHTY','NINETY

','HUNDRED');
-- 100 到 十億 每個都是 10的 NUM次方
CC SC_ARR :=

SC_ARR(NULL,NULL,'HUNDRED','THOUSAND',null,null,'MILLION',NULL,null,'BILLION',

NULL,null,'THOUSAND BILLION',null,null,'MILLION BILLION',null,null,'BILLION

BILLION');
C_NUM VARCHAR2(40); --把數字轉換成字串
d_num varchar2(2);
V_LENGTH number;
vall_length NUMBER;
RETURN_VALUE VARCHAR2(2000);
RETURN_dVALUE VARCHAR2(200);
V_cA varchar2(3);
V_nA NUMBER;
V_UNIT VARCHAR2(40);
getmod int; --v_length除3求餘(百十個)
getdiv int; --v_length除3取整(v_unit)
v_cnum varchar2(20);
v_npoint int;
BEGIN --先找出小數點所在位置
V_CNUM := TO_CHAR(V_NUM);
V_NPOINT:=INSTR(V_CNUM,'.');
IF V_NPOINT > 0 THEN
--依小數點位置位減一
c_num := SUBSTR(V_CNUM,1,V_NPOINT-1);
d_num := substr(v_cnum,v_npoint+1,2);
ELSE
c_num := V_CNUM;
d_num := null;
END IF;

d_num:=rpad(d_num,2,'0');

-------- 整數部分 -----------------------------
--C_NUM := INTE(V_NUM); --TO_CHAR(INT(V_NUM));
vall_LENGTH := LENGTH(C_NUM);
while c_num is not null loop
--設原值1為 123456
--設原值2為12345678
-- 10034506
V_LENGTH := LENGTH(C_NUM);
EXIT WHEN V_LENGTH <= 0;
select mod(v_length,3),floor(v_length/3) into getmod,getdiv from dual ;
--getmod=0 getdiv=2
--getmod=2 getdiv=2
--srw.message(0,c_num);
if getmod=0 then
v_ca:=substr(c_num,1,v_length-(getdiv-1)*3);
--v_a=('123456',1,6-(2-1)*3)=('123456',1,3)='123'
v_na:=to_number(v_ca);
c_num:=substr(c_num,4);
--c_num=substr('123456',4)='456'
if getdiv<=1 then
v_unit:=null;
--百十個
else
if v_na>0 then
v_unit:=cc(getdiv*3-2);
--cc(2*3-2)=cc(4)='THOUSAND'
else
v_unit:=null;
end if;
end if;
else
v_ca:=substr(c_num,1,v_length-getdiv*3);
--v_a=('12345678',1,8-(2*3))=('12345678',1,2)='12'
v_na:=to_number(v_ca);
c_num:=substr(c_num,v_length-getdiv*3+1);
--c_num:=substr(c_num,8-2*3+1)=substr('12345678',3)='345678'
if getdiv<1 then
v_unit:=null;
--百十個
else
if v_na>0 then
v_unit:=cc(getdiv*3+1);
--cc(2*3+1)=cc(7)='MILLION'
else
v_unit:=null;
end if;
end if;
end if;
if v_na>=100 then
return_value:=return_value||aa(floor(v_na/100))||' '||'HUNDRED ';
v_ca:=to_char(v_na);
v_ca:=substr(v_ca,2);
v_na:=to_number(v_ca);
end if;
if v_na>20 then
--im:='v_ca:'||v_ca;
--srw.message(0,im);
return_value:=return_value||bb(floor(v_na/10))||' ';
v_ca:=to_char(v_na);
v_ca:=substr(v_ca,2);
v_na:=to_number(v_ca);
end if;
--FND_MESSAGE.SET_STRING(v_ca);
--FND_MESSAGE.SHOW;
if v_na>0 then
return_value:=return_value||aa(v_na)||' ';
v_ca:=null;
end if;
if v_unit is not null then
return_value:=return_value||v_unit||' ';
end if;
if c_num is null or length(c_num)=0 then
exit;
end if;
end loop ;
--以下為小數部分.(只取兩位小數)
V_LENGTH := LENGTH(d_NUM);
v_na:=to_number(d_num);

if v_na>20 then
return_dvalue:=return_dvalue||bb(floor(v_na/10))||' ';
v_ca:=to_char(v_na);
v_ca:=substr(v_ca,2);
v_na:=to_number(v_ca);
end if;
if v_na>0 then
return_dvalue:=return_dvalue||aa(v_na)||' ';
v_ca:=null;
end if;
-------- 整數部分++小數部分 -----------------------------
IF RETURN_VALUE IS NOT NULL AND RETURN_dVALUE IS NOT NULL THEN
RETURN_VALUE := RETURN_VALUE || ' AND CENTS '||return_dvalue;
ELSE IF RETURN_VALUE IS NOT NULL THEN
RETURN_VALUE := RETURN_VALUE;
ELSE IF RETURN_DVALUE IS NOT NULL THEN
RETURN_VALUE := 'CENTS '||return_dvalue;
END IF;
END IF;
END IF;

RETURN RETURN_VALUE;
END;
/