PLSQL: ORACLE表值函数及调用
ORACLE 表值函数应用
程序包头：
create or replace package kl_lead_time_pkg is
-- Author : samrv
-- Created : 2023/5/3 14:23:58
-- Purpose : 预加工周期
-- Public type declarations
type leadtime_rec_type is record(
COMPONENT_ITEM_ID number:= fnd_api.g_miss_num,
PLAN_LEVEL number:= fnd_api.g_miss_num,
GROUP_ID number:= fnd_api.g_miss_num,
ASSEMBLY_ITEM_ID number:= fnd_api.g_miss_num,
COMPONENT_QUANTITY number:= fnd_api.g_miss_num,
TOP_ITEM_ID number:= fnd_api.g_miss_num,
LINE_NUM varchar2(4000):= fnd_api.g_miss_char,
TOTAL_QTY number:= fnd_api.g_miss_num,
ORGANIZATION_ID number:= fnd_api.g_miss_num,
WIP_SUPPLY_TYPE number:= fnd_api.g_miss_num,
PLANNING_MAKE_BUY_CODE number:= fnd_api.g_miss_num,
LEAD_TIME varchar2(4000):= fnd_api.g_miss_char
);
TYPE leadtime_tbl_type IS TABLE OF leadtime_rec_type ;--INDEX BY BINARY_INTEGER;
-- Public constant declarations
--<ConstantName> constant <Datatype> := <Value>;
-- Public variable declarations
--<VariableName> <Datatype>;
-- Public function and procedure declarations
--function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
function get_lead_time_ft(
p_top_item_id in number,
p_organization_id in number,
p_quantity in number
) return leadtime_tbl_type pipelined;
/*
function lead_time_ft(
p_top_item_id in number,
p_organization_id in number,
p_quantity in number
) return sys_refcursor;
*/
end kl_lead_time_pkg;
/
程序包体：
create or replace package body kl_lead_time_pkg is
-- Private type declarations
--type <TypeName> is <Datatype>;
-- Private constant declarations
--<ConstantName> constant <Datatype> := <Value>;
-- Private variable declarations
--<VariableName> <Datatype>;
-- Function and procedure implementations
--function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
-- <LocalVariable> <Datatype>;
--begin
-- <Statement>;
-- return(<Result>);
--end;
function get_lead_time_ft(
p_top_item_id in number,
p_organization_id in number,
p_quantity in number
) return leadtime_tbl_type pipelined is
ref_cursor sys_refcursor;
leadtime_rec leadtime_rec_type;
leadtime_tbl leadtime_tbl_type := leadtime_tbl_type();
cursor c1 is
SELECT betr.component_item_id,
betr.plan_level,
betr.GROUP_ID,
betr.assembly_item_id,
betr.component_quantity,
betr.top_item_id,
TO_CHAR (betr.rn1) || SYS_CONNECT_BY_PATH (TO_CHAR (betr.rn), '.') as line_num,
betr.extended_quantity as total_qty,
betr.organization_id,
betr.wip_supply_type,
betr.planning_make_buy_code,
'0' || SYS_CONNECT_BY_PATH (TO_CHAR (NVL (betr.lead_time, 0)), '*')
as lead_time
FROM (SELECT ROW_NUMBER ()
OVER (
PARTITION BY t.GROUP_ID,
t.plan_level,
t.assembly_item_id
ORDER BY
t.plan_level, t.parent_item, t.component_item_id)
rn,
DENSE_RANK () OVER (ORDER BY t.GROUP_ID) rn1,
t.plan_level,
t.GROUP_ID,
t.assembly_item_id,
t.component_quantity,
t.top_item_id,
t.parent_item,
t.component_item_id,
t.lead_time,
t.extended_quantity,
t.organization_id,
t.wip_supply_type,
t.planning_make_buy_code,
t.component_code
FROM ( SELECT bet.component_item_id,
bet.plan_level,
bet.GROUP_ID,
bet.assembly_item_id,
bet.component_quantity,
bet.top_item_id,
CONNECT_BY_ROOT bet.component_item_id parent_item,
NVL (brlt.quantity, 0) as lead_time,
bet.extended_quantity,
bet.component_sequence_id,
--add below 3 columns 2013/11/14
bet.organization_id,
bet.wip_supply_type,
msib.planning_make_buy_code,
bet.component_code
FROM -- kl_bom_explosion_temp bet,
apps.kl_bom_explosion_temp_grp_v bet, --add by: zaoru,20230503
mtl_system_items_b msib,
apps.kl_bom_routing_lead_time_v brlt
WHERE bet.component_item_id = msib.inventory_item_id
AND bet.organization_id = msib.organization_id
and bet.assembly_item_id = brlt.assembly_item_id(+)
and bet.organization_id = brlt.organization_id(+)
--AND BET.GROUP_ID = 18508477
and bet.organization_id = p_organization_id
and bet.top_item_id = p_top_item_id -- 1224243
and brlt.low_bound(+) <= p_quantity -- 24
and brlt.upper_bound(+) > p_quantity -- 24
--AND msib.planning_make_buy_code = 1
START WITH bet.plan_level = 1
CONNECT BY PRIOR bet.component_item_id =
bet.assembly_item_id
AND PRIOR bet.GROUP_ID = bet.GROUP_ID
--hzq 20160622 增加递归条件
AND PRIOR bet.component_code =
SUBSTR (
bet.component_code,
1,
INSTR (bet.component_code, '-', -1)
- 1)) t) betr
WHERE betr.wip_supply_type <> 6 --2013/11/14 virtual
-- and betr.assembly_item_id=474992
START WITH plan_level = 1
CONNECT BY PRIOR betr.component_item_id = betr.assembly_item_id
AND PRIOR betr.GROUP_ID = betr.GROUP_ID
--hzq 20160622 增加递归条件
AND PRIOR betr.component_code =
SUBSTR (betr.component_code,
1,
INSTR (betr.component_code, '-', -1) - 1);
begin
for r1 in c1 loop
leadtime_rec := leadtime_rec_type(r1.COMPONENT_ITEM_ID,
r1.PLAN_LEVEL,
r1.GROUP_ID,
r1.ASSEMBLY_ITEM_ID,
r1.COMPONENT_QUANTITY,
r1.TOP_ITEM_ID,
r1.LINE_NUM,
r1.TOTAL_QTY,
r1.ORGANIZATION_ID,
r1.WIP_SUPPLY_TYPE,
r1.PLANNING_MAKE_BUY_CODE,
r1.LEAD_TIME
);
-- leadtime_tbl.EXTEND;
--leadtime_tbl(leadtime_tbl.count) := leadtime_rec;
pipe row(leadtime_rec);
end loop;
return;
--return ;
end get_lead_time_ft;
/*
function lead_time_ft(
p_top_item_id in number,
p_organization_id in number,
p_quantity in number
) return sys_refcursor is
res sys_refcursor;
begin
open res for select * from klcux.kl_lead_time_ft ;
return res;
end lead_time_ft;
*/
begin
-- Initialization
--<Statement>;
null;
end kl_lead_time_pkg;
/
调用或调试
--调试
-- Created on 2023/5/3 by WZAORU
declare
-- Local variables here
i integer;
V_TOP_ITEM_ID NUMBER;
v_refcursor kl_lead_time_pkg.leadtime_tbl_type;
ft_row klcux.kl_lead_time_ft%rowtype ;
begin
-- Test statements here
/*
SELECT TB.TOP_ITEM_ID
INTO V_TOP_ITEM_ID
FROM
TABLE(kl_lead_time_pkg.KL_LEAD_TIME_FT( 1224243, 167 , 24) ) TB
WHERE ROWNUM= 1 ;
DBMS_OUTPUT.PUT_LINE('V_TOP_ITEM_ID :'|| V_TOP_ITEM_ID);
*/
v_refcursor := kl_lead_time_pkg.LEAD_TIME_FT( 1224243, 167 , 24);
dbms_output.put_line( v_refcursor%rowcount);
loop
fetch v_refcursor into ft_row ;
exit when v_refcursor%notfound;
dbms_output.put_line(ft_row.TOP_ITEM_ID);
end loop;
end;
多表关联 使用例子：
select wpb.* ,ft.lead_time as lead_time2 from
APPS.KL_WIP_PR_BOMNUM_V wpb ,
TABLE(kl_lead_time_pkg.get_LEAD_TIME_FT( wpb.top_item_id , wpb.organization_id , p_quantity=> 2400) ) ft
where ft.component_item_id = wpb.component_item_id
and ft.organization_id = wpb.organization_id
and ft.top_item_id = wpb.top_item_id
and wpb.top_item_id =1224243 -- 1225241
优质生活从拆开始
