1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194
| CREATE OR REPLACE
PACKAGE "STAFF_PROF" AS
TYPE LOAD_REC_BUD IS RECORD ( n_reqid NUMBER,
v_reqname VARCHAR2(200),
v_reqtype VARCHAR2(40),
v_expense_type VARCHAR2(60),
v_category VARCHAR2(60),
v_expense_code VARCHAR2(60),
v_labor_type VARCHAR2(60),
v_period_name VARCHAR2(30),
v_amout NUMBER);
TYPE LOAD_REC_BUD_FULL IS RECORD ( n_reqid NUMBER, --Proposale Or Project Number
v_reqname VARCHAR2(200), --Proposale or Project Name
v_reqtype VARCHAR2(40), --Proposale or Project
v_budid NUMBER, --Budget ID
v_businesspurpose VARCHAR2(60),
v_product VARCHAR2(100),
v_subsidiary VARCHAR2(60),
v_plan VARCHAR2(60),
v_platform VARCHAR2(100),
v_origin VARCHAR2(200),
v_security VARCHAR2(200),
v_module VARCHAR2(200),
v_oldtmscode VARCHAR2(200),
v_expense_type VARCHAR2(60),
v_category VARCHAR2(60),
v_expense_code VARCHAR2(60),
v_labor_type VARCHAR2(60),
v_period_name VARCHAR2(30),
v_working_days NUMBER,
v_amount NUMBER);
TYPE load_set_bud_full IS TABLE OF LOAD_REC_BUD_FULL;
FUNCTION BUD_REQ_POS
( P_SCENARIO_ID IN NUMBER,
P_START_PERIOD IN NUMBER DEFAULT NULL,
P_FINISH_PERIOD IN NUMBER DEFAULT NULL,
P_PERIOD_TYPE IN VARCHAR2 DEFAULT 'M')
RETURN load_set_bud_full PIPELINED;
END;
CREATE OR REPLACE
PACKAGE BODY "STAFF_PROF" AS
FUNCTION BUD_REQ_POS
( P_SCENARIO_ID IN NUMBER,
P_START_PERIOD IN NUMBER DEFAULT NULL,
P_FINISH_PERIOD IN NUMBER DEFAULT NULL,
P_PERIOD_TYPE IN VARCHAR2 DEFAULT 'M')
RETURN load_set_bud_full PIPELINED IS
TYPE req_list_values IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_req_list_values req_list_values;
l_prj_bud_id NUMBER; -- Project Budget ID
l_loads_set_bud_full LOAD_REC_BUD_FULL;
TYPE num_list_values IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_list_values num_list_values;
n_is_project NUMBER;
l_rec_cursor LOAD_REC;
l_rec_bud_full LOAD_REC_BUD_FULL;
CURSOR c_loads_bud_values(C_BUDGET_ID NUMBER) IS
SELECT work_item_id , work_item_name ,work_item, expense_type_code , category_meaning
, budget_line_udata2 EXPENSE_CODE, labor_type_code LABOR_TYPE
, to_char(to_date(to_char(period_month),'YYYYMM'),'MM/YYYY') PERIOD
, sum(forecast_value) AMOUNT
FROM bud_lines_v
WHERE 1=1
and budget_id=C_BUDGET_ID
and period_id>=P_START_PERIOD
and period_id<=P_FINISH_PERIOD
group by work_item_id,work_item_name,work_item,budget_line_udata2,expense_type_code
,category_meaning,labor_type_code,period_month
order by work_item_name,to_char(to_date(period_month),'Q YYYY');
l_count NUMBER;
l_load_bud_values c_loads_bud_values%ROWTYPE;
n_reqid NUMBER;
v_reqname VARCHAR2(200);
v_reqtype VARCHAR2(40);
v_expense_type VARCHAR2(60);
v_businesspurpose VARCHAR2(60);
v_product VARCHAR2(100);
v_subsidiary VARCHAR2(60);
v_plan VARCHAR2(60);
v_platform VARCHAR2(100);
v_origin VARCHAR2(200);
v_security VARCHAR2(200);
v_module VARCHAR2(200);
v_oldtmscode VARCHAR2(200);
BEGIN
select distinct(ksc.request_id) as request_id
bulk collect into l_req_list_values
from t_scen_cont ksc, k_req_ts krt ,k_reqs kr
where ksc.s_comp_id=P_SCENARIO_ID
and kr.req_id=ksc.request_id
and krt.req_t_id in kr.req_t_id;
IF l_req_list_values.FIRST IS NOT NULL THEN
FOR i IN l_req_list_values.FIRST..l_req_list_values.LAST LOOP
select count(prj_project_id)
into l_count
from t_proj
where request_id=l_req_list_values(i);
if l_count>0 then --is a project
--get the project_request_id and Budget_ID
select prj_budget_id
into l_prj_bud_id
from t_proj
where request_id=l_req_list_values(i);
v_reqtype:='Project';
else --is a proposal
select prop_budget_id
into l_prj_bud_id
from t_pro
where req_id=l_req_list_values(i);
v_reqtype:='Proposal';
end if;
select visible_parameter1 ,visible_parameter2
,visible_parameter13 ,visible_parameter9
,visible_parameter4 ,visible_parameter10
,visible_parameter11 ,visible_parameter6
,visible_parameter8
into v_businesspurpose,v_product,v_subsidiary,v_plan,v_platform,v_origin
,v_security,v_module,v_oldtmscode
from req_de krd
where krd.batch_number=1
and request_id=l_req_list_values(i);
OPEN c_loads_bud_values(l_prj_bud_id);
LOOP
==>error ici FETCH c_loads_bud_values INTO l_load_bud_values;
l_loads_set_bud_full.n_reqid := l_req_list_values(i);
l_loads_set_bud_full.v_reqname := l_load_bud_values.work_item_name;
l_loads_set_bud_full.v_reqtype := v_reqtype;
l_loads_set_bud_full.v_budid := l_prj_bud_id;
l_loads_set_bud_full.v_businesspurpose := v_businesspurpose;
l_loads_set_bud_full.v_product := v_product;
l_loads_set_bud_full.v_subsidiary := v_subsidiary;
l_loads_set_bud_full.v_plan := v_plan;
l_loads_set_bud_full.v_platform := v_platform;
l_loads_set_bud_full.v_origin := v_origin;
l_loads_set_bud_full.v_security := v_security;
l_loads_set_bud_full.v_module := v_module;
l_loads_set_bud_full.v_oldtmscode := v_oldtmscode;
l_loads_set_bud_full.v_expense_type := l_load_bud_values.expense_type_code;
l_loads_set_bud_full.v_category := l_load_bud_values.category_meaning;
l_loads_set_bud_full.v_expense_code := l_load_bud_values.EXPENSE_CODE;
l_loads_set_bud_full.v_labor_type := l_load_bud_values.LABOR_TYPE;
l_loads_set_bud_full.v_period_name := l_load_bud_values.PERIOD;
l_loads_set_bud_full.v_working_days := 0;
l_loads_set_bud_full.v_amount := l_load_bud_values.AMOUNT;
if l_load_bud_values.AMOUNT>0 then
PIPE ROW(l_loads_set_bud_full);
end if;
EXIT WHEN c_loads_bud_values%NOTFOUND;
END LOOP;
CLOSE c_loads_bud_values;
END LOOP;
END IF;
RETURN;
END;
END; |
Partager