PP庫存資料查詢報表的簡單開發

1) 用PL/SQL建立PP料號庫存量視圖

CREATE OR REPLACE VIEW INVENTORY_PART_IN_STOCK_PP AS
SELECT contract?????????????????????? contract,
?????? part_no??????????????????????? part_no,
?????? configuration_id?????????????? configuration_id,
?????? location_no??????????????????? location_no,
?????? lot_batch_no?????????????????? lot_batch_no,
?????? serial_no????????????????????? serial_no,
?????? eng_chg_level????????????????? eng_chg_level,
?????? waiv_dev_rej_no??????????????? waiv_dev_rej_no,
?????? vendor_no????????????????????? vendor_no,
?????? avg_unit_transit_cost????????? avg_unit_transit_cost,
?????? count_variance???????????????? count_variance,
?????? del_type?????????????????????? del_type,
?????? department???????????????????? department,
?????? expiration_date??????????????? expiration_date,
?????? substrb(Inventory_Part_Freeze_Code_API.Decode(freeze_flag),1,200) freeze_flag,
?????? freeze_flag??????????????????? freeze_flag_db,
?????? last_activity_date???????????? last_activity_date,
?????? last_count_date??????????????? last_count_date,
?????? location_class???????????????? location_class,
?????? substrb(Inventory_Location_Type_API.Decode(location_type),1,200) location_type,
?????? location_type????????????????? location_type_db,
?????? low_level_code???????????????? low_level_code,
?????? ownership????????????????????? ownership,
?????? qty_in_transit???????????????? qty_in_transit,
?????? qty_onhand???????????????????? qty_onhand,
?????? qty_reserved?????????????????? qty_reserved,
?????? receipt_date?????????????????? receipt_date,
?????? source???????????????????????? source,
?????? warehouse????????????????????? warehouse,
?????? bay_no???????????????????????? bay_no,
?????? row_no???????????????????????? row_no,
?????? tier_no??????????????????????? tier_no,
?????? bin_no???????????????????????? bin_no,
?????? availability_control_id??????? availability_control_id,
?????? rowid???????????????????????? objid,
?????? ltrim(lpad(to_char(rowversion,'YYYYMMDDHH24MISS'),2000))??????????????????? objversion
FROM?? inventory_part_in_stock_tab
WHERE PART_NO like '7%'
AND (NOT (???? ( qty_onhand = 0)
????????? AND? ( qty_reserved = 0)
????????? AND? ( qty_in_transit= 0)
???????? )
??? )

WITH?? read only

2) 取報表中相關資料;查詢條件為:庫位,開始料號,結束料號,開始日期,結束日期
參數設置:
booleanVar bSQL := TRUE;

if {?sLocationNo} <> '' then
? bSQL := bSQL AND ({INVENTORY_PART_IN_STOCK_PP.LOCATION_NO} like {?sLocationNo});

if {?sBeginPartNo} <> '' then
? bSQL := bSQL AND ({INVENTORY_PART_IN_STOCK_PP.PART_NO} >= {?sBeginPartNo});
if {?sEndPartNo} <> '' then
? bSQL := bSQL AND ({INVENTORY_PART_IN_STOCK_PP.PART_NO} <= {?sEndPartNo});
bSQL := bSQL AND (Date({INVENTORY_PART_IN_STOCK_PP.RECEIPT_DATE}) >= {?dtBeginDate});
bSQL := bSQL AND (Date({INVENTORY_PART_IN_STOCK_PP.RECEIPT_DATE}) <= {?dtEndDate});

bSQL;

3) PP料號批號表:
第一分組:料號;第二分組:批號

4) PP庫位料號表:
第一分組:庫位;第二分組:料號

最後顯示結果:

?