RepairPrice - Apply 9i OODB

研一高等資料庫課程時
就有用Oracle 9i進行OODB的設計
如下
以維修料的維修價格查詢為例
可以建出維修料的物件
再設計此物件時就完全考慮它會發生的功能
應用時只要呼叫它的method就可以了...
應用時比較直觀而程式有可讀性

然而缺點是
目前用的是Developer 6i
部份OODB的SQL語法要等到Developer 9i才被支援
Form6i和Report6i無法直接應用到這樣的設計


CREATE OR REPLACE
TYPE RepairPrice_obj AS OBJECT
( CONDITION_id NUMBER,
CONDITION VARCHAR2(100),
SERVICE_RATE NUMBER
);
/

CREATE OR REPLACE
TYPE RepairPrice_tbl AS TABLE OF RepairPrice_obj;
/

CREATE OR REPLACE
TYPE repairitem AS OBJECT (
ITEM_ID NUMBER,
ITEM_NO VARCHAR2(50),
ORG_ID NUMBER,
ITEM_COST NUMBER,
CATEGORY_NAME VARCHAR2(50),
PRICE_SET RepairPrice_tbl,
CONSTRUCTOR FUNCTION
repairitem(i_item_no varchar2,i_org_id NUMBER) RETURN SELF AS RESULT,
MEMBER FUNCTION
repair_price_ori(i_condition varchar2) return NUMBER,
MEMBER FUNCTION
repair_price_frn(i_condition varchar2,i_currency varchar2) return NUMBER
) FINAL;
/


CREATE OR REPLACE TYPE BODY repairitem AS
CONSTRUCTOR FUNCTION repairitem(i_item_no varchar2,i_org_id NUMBER) RETURN SELF AS RESULT IS
v_count NUMBER;
v_i NUMBER;
BEGIN
SELF.ORG_ID:=i_org_id;

BEGIN
select inventory_item_id
into SELF.ITEM_ID
from mtl_system_items
where segment1=i_item_no
and organization_id=SELF.ORG_ID;

SELF.ITEM_NO:=i_item_no;

EXCEPTION WHEN OTHERS THEN
SELF.ITEM_ID:=-1;
SELF.ITEM_NO:=i_item_no' ERROR!!';
END;

BEGIN
select nvl(item_cost,0)
into SELF.ITEM_COST
from cst_item_costs
where organization_id = SELF.ORG_ID
and inventory_item_id = SELF.ITEM_ID
and cost_type_id = 1;
EXCEPTION WHEN OTHERS THEN
SELF.ITEM_COST:=-1;
END;

BEGIN
select element_value
into SELF.CATEGORY_NAME
from mtl_descr_element_values
where inventory_item_id=SELF.ITEM_ID
and element_name='ºØÃþ';
EXCEPTION WHEN OTHERS THEN
SELF.CATEGORY_NAME:='ERROR';
SELF.ITEM_NO:=SELF.ITEM_NO'(CATEGORY ERROR)';
END;

--price set
--init
SELF.PRICE_SET:=RepairPrice_tbl(RepairPrice_obj(0,'00000',0.0));

select count('Y')
into v_count
from TW_CRR_REPAIR_COST_HEADS
where CATEGORY_NAME=SELF.CATEGORY_NAME
and ORGANIZATION_ID=SELF.ORG_ID;

if v_count=0 then
SELF.CATEGORY_NAME:='一般性材料' ;
end if;


select
RepairPrice_obj(L.line_id,
(CASE WHEN H.CATEGORY_NAME='一般性材料' THEN 'N/A' ELSE L.SERVICE_YEAR END),
(CASE WHEN H.CATEGORY_NAME='一般性材料' THEN L.PRICE_RATE ELSE L.SERVICE_RATE END))
BULK COLLECT INTO
SELF.PRICE_SET
FROM TW_CRR_REPAIR_COST_HEADS H,
TW_CRR_REPAIR_COST_LINES L
WHERE (SELF.CATEGORY_NAME='¤@¯ë©Ê§÷®Æ'
AND H.ORGANIZATION_ID=SELF.ORG_ID
AND H.CATEGORY_NAME=SELF.CATEGORY_NAME
AND H.HEAD_ID=L.HEAD_ID
AND L.LOW_PRICE <>= SELF.ITEM_COST)
OR (SELF.CATEGORY_NAME<>'¤@¯ë©Ê§÷®Æ'
AND H.ORGANIZATION_ID=SELF.ORG_ID
AND H.CATEGORY_NAME=SELF.CATEGORY_NAME
AND H.HEAD_ID=L.HEAD_ID);

RETURN; --return self object..

END repairitem;


/*
* Function repair_price_ori
* @Author: Zhxiang
* ----------------------------
* Return Value meaning:
* "-1" means service rate error
* "-2" means item cost error
*/
MEMBER FUNCTION repair_price_ori(i_condition varchar2) RETURN NUMBER IS
r_price NUMBER;
s_rate NUMBER;
BEGIN
IF SELF.ITEM_COST<0>'¤@¯ë©Ê§÷®Æ' then
select SERVICE_RATE
into s_rate
from TABLE(SELF.PRICE_SET)
where CONDITION=i_condition;
else
select SERVICE_RATE
into s_rate
from TABLE(SELF.PRICE_SET)
where ROWNUM=1;
end if;
EXCEPTION WHEN OTHERS THEN
r_price:=-1; -- "-1" means service rate error
return r_price;
END;
r_price:=SELF.ITEM_COST*s_rate;
END IF;
return r_price;

END repair_price_ori;

/*
* Function repair_price_frn
* @Author: Zhxiang
* ----------------------------
* Return Value meaning:
* "-1" means service rate error
* "-2" means item cost error
* "-3" means convertion rate error
*/
MEMBER FUNCTION repair_price_frn(i_condition varchar2,i_currency varchar2) RETURN NUMBER IS
c_rate NUMBER;
ori_price NUMBER;
r_price NUMBER;
BEGIN
--A.get currency rate
BEGIN
SELECT round(A.CONVERSION_RATE,6)
INTO c_rate
FROM GL_DAILY_RATES A
WHERE A.FROM_CURRENCY=(select CURRENCY_CODE
from GL_SETS_OF_BOOKS
where SET_OF_BOOKS_ID=
(select SET_OF_BOOKS_ID
from ORG_ORGANIZATION_DEFINITIONS
where ORGANIZATION_ID=SELF.ORG_ID))
AND A.TO_CURRENCY=i_currency
AND A.CONVERSION_TYPE='Corporate'
AND TO_CHAR(A.CONVERSION_DATE,'DD-MON-YYYY')
=TO_CHAR(SYSDATE,'DD-MON-YYYY');
EXCEPTION WHEN OTHERS THEN
r_price:=-3; -- "-3" means convertion rate error
return r_price;
END;
--B.get price_ori
ori_price:=repair_price_ori(i_condition);
if ori_price<0 c="A*B">
* 說明:
1. RepairPrice_obj
為維修價格型態...包括保固條件與價格加成率...
是基本型態Number/varchar2/date等的複合型態
2. RepairPrice_tbl
為維修價格物件構成的table...本身也是一個物件型態
3. repairitem
為維修料物件..包括料號/分類/成本等屬性
另外有其Method..包括建構子/取得本幣成本/取得外幣成本等功能..

應用例子如下
Declare
v_text varchar2(10);
v_xi repairitem:= NULL;
v_test01 varchar2(100);
Begin
dbms_output.put_line('Start ....');
v_xi:=repairitem('76+010004+00',104); --Constructor
dbms_output.put_line('Object item_id: 'to_char(v_xi.ITEM_ID));
dbms_output.put_line('Object item_no: 'v_xi.ITEM_NO);
dbms_output.put_line('Object item_cost: 'to_char(v_xi.ITEM_COST));
dbms_output.put_line('Object item_cate: 'v_xi.CATEGORY_NAME);
End;