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;
/

程序包体：

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

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