1.基于EBS 10.5.9 不支持XML Publish, 强制将XML标签输出, 重复调用fnd_file.put_line(fnd_file.output,''); 打印标签。
create or replace procedure bg_cnc_xml(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_org_id IN NUMBER,
p_mds IN VARCHAR2,
p_prod_type IN VARCHAR2,
p_week IN NUMBER,
p_days_num in number,
p_calendar_type IN VARCHAR2) AS
CURSOR cur_bsad(p_days_num number, p_week number) IS
select rownum Num,
t.Item_Desc,
t.item_number,
t.customer address,
t.end_customer,
t.item_type project,
t.item_main_type,
t.item_sub_type,
t.This_WK_QTY,
t.Next_WK_QTY,
t.day_qty,
t.item_id
from (SELECT ltrim(b.short_desc) Item_Desc,
b.item_number,
b.inventory_item_id Item_id,
b.customer,
sum(b.schedule_quantity) This_WK_QTY,
b.end_customer,
b.item_type,
b.item_sub_type,
b.item_main_type,
b.Next_WK_QTY,
round( sum(b.schedule_quantity) / P_days_num, 0) Day_Qty -- P_day_num
FROM BG_MDS_DETAL_TEMP B
group by
ltrim(b.short_desc) ,
b.item_number,
b.inventory_item_id ,
b.customer,
b.end_customer,
b.item_type,
b.item_sub_type,
b.item_main_type,
b.Next_WK_QTY
order by 6 asc, 8 asc, 2 asc) t;
/* select rownum Num,
t.Item_Desc,
t.item_number,
t.customer address,
t.end_customer,
t.item_type project,
t.item_main_type,
t.item_sub_type,
t.This_WK_QTY,
t.Next_WK_QTY,
t.day_qty,
t.item_id
from (SELECT ltrim(b.short_desc) Item_Desc,
b.item_number,
b.inventory_item_id Item_id,
b.customer,
b.schedule_quantity This_WK_QTY,
b.end_customer,
b.item_type,
b.item_sub_type,
b.item_main_type,
b.Next_WK_QTY,
round(b.schedule_quantity / P_days_num, 0) Day_Qty -- P_day_num
FROM BG_MDS_DETAL_TEMP B
order by 6 asc, 8 asc, 2 asc) t;*/
Cursor cur_bsum(P_type varchar2) is
select *
from (select nvl(t.end_customer, 'Report') type_1,
decode(t.end_customer,
null,
'Report Total',
t.end_customer || ' Product Total:') Type_2,
sum(t.schedule_quantity) QTY,
sum(t.next_wk_qty) Next_Qty
from (select sum(b.schedule_quantity) schedule_quantity,
b.end_customer,
b.next_wk_qty
from bg_mds_detal_temp b
group by b.end_customer, b.next_wk_qty) t
group by rollup(t.end_customer)) t1
where 1 = 1
and t1.type_1 = P_Type;
---------------------------------------
v_year CHAR(4) := '2009';
v_start_week DATE := TO_DATE(v_year || '0401', 'YYYYMMDD');
v_cur_week_start_day date;
days_number number;
bsad_rec cur_bsad%rowtype;
bsum_rec cur_bsum%rowtype;
ctrl_cust varchar2(100);
p_type varchar2(100);
act_qty number;
so_issue_qty number;
rework_qty number;
wip_qty number;
mct number;
a varchar2(50) := '"' || 'Act' || '"';
b varchar2(50) := '"' || 'Plan' || '"';
c varchar2(100);
n_index number;
rec_index number;
j number;
x number;
m number ;
item_id number ;
-------------------
procedure print_sbr_total is
begin
open cur_bsum(ctrl_cust);
fetch cur_bsum
into bsum_rec;
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsum_rec.Type_2 || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsum_rec.QTY || '');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
bsum_rec.Next_QTY || '');
n_index := n_index + 1;
rec_index := 1 - n_index;
fnd_file.put_line(fnd_file.output,
' ' ||
'Sub Plan' || '');
/* fnd_file.put_line(fnd_file.output,
' ' ||
n_index || '');*/
for i in 1 .. 7 loop
-- c := '"' || bsum_rec.Type_2 || '"';
j := i + 4;
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
end loop;
fnd_file.put_line(fnd_file.output, ' ');
----print act subtotal -------------------------
n_index := n_index + 1;
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
'Sub Act' || '');
/* fnd_file.put_line(fnd_file.output,
' ' ||
n_index || '');*/
rec_index :=1 - n_index;
for i in 1 .. 8 loop
j := i + 4;
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
end loop;
--Print Act Achieve %
fnd_file.put_line(fnd_file.output,
' 0');
--Print Dispersion( 1 ) ----
fnd_file.put_line(fnd_file.output,
' 0');
--Print Dispersion( 2 ) ----
fnd_file.put_line(fnd_file.output,
' 0');
---Print lack of qty
fnd_file.put_line(fnd_file.output,
' 0');
--Print daily output qty--
fnd_file.put_line(fnd_file.output,
' 0');
fnd_file.put_line(fnd_file.output, ' ');
---print sub total --------------------
close cur_bsum;
end;
-------------------
/* procedure print_total is
begin
open cur_bsum(ctrl_cust);
fetch cur_bsum
into bsum_rec;
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
ctrl_cust || ' Total:' || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsum_rec.QTY || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsum_rec.Next_QTY || '');
fnd_file.put_line(fnd_file.output, ' ');
------------------
for i in 0 .. 29 loop
begin
--dbms_output.put_line('SUM(');
fnd_file.put_line(fnd_file.output,
' 0');
end;
end loop;
------
fnd_file.put_line(fnd_file.output, '');
close cur_bsum;
end;*/
---------------
procedure print_sbr_line is
begin
-- Print Plan Line
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' || bsad_rec.Num ||
'');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.Item_Desc || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.item_number || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.address || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.end_customer || '');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.this_wk_qty || '');
--lack of last wk
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.next_wk_qty || '');
----print plan line-------------
n_index := n_index + 1;
fnd_file.put_line(fnd_file.output,
' ' ||
'Plan' || '');
for i in 1 .. 7 loop
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.day_qty || '');
end loop;
fnd_file.put_line(fnd_file.output, ' ');
/* -----Print Act line------**/
/* -----Print Act line------**/
n_index := n_index + 1;
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
bsad_rec.end_customer || '');
fnd_file.put_line(fnd_file.output,
' Act');
-- Print this wk every day act qty--
for i in 0 .. 6 loop
begin
select nvl(sum(nvl(wmt.primary_quantity, 0)),0)
into act_qty
from wip_move_transactions_v wmt,
apps.bg_mrp_running_weeks bmrw,
wip_discrete_jobs job
where wmt.organization_id = p_org_id
and job.organization_id=p_org_id
and job.primary_item_id= bsad_rec.item_id
and wmt.wip_entity_id=job.wip_entity_id
and wmt.primary_item_id = bsad_rec.item_id
and bmrw.n_sequence = p_week --STP0909039 job
and wmt.to_operation_seq_num > 10
and wmt.fm_operation_seq_num = 10
and wmt.reason_id = 10 ---'81 WIP - Normal move'
and wmt.to_intraoperation_step_type = 1
and wmt.fm_intraoperation_step_type = 1
and nvl(job.attribute13, 'Null') = bsad_rec.address
and wmt.transaction_date between (next_day(bmrw.week, 'SUN') - 8 + i + (8 / 24)) and
(next_day(bmrw.week, 'SUN') - 7 + i + (7.9998 / 24));
exception
when no_data_found then
act_qty := 0;
end;
fnd_file.put_line(fnd_file.output,
' ' ||
act_qty || '');
end loop;
--Print output total
fnd_file.put_line(fnd_file.output,
' 0');
--Print Act Achieve %
fnd_file.put_line(fnd_file.output,
' 0');
--Print Dispersion( 1 )----
begin
select nvl(sum(nvl(wmt.primary_quantity, 0)),0)
into wip_qty
from wip_move_transactions_v wmt,
apps.bg_mrp_running_weeks bmrw,
wip_discrete_jobs job
where wmt.organization_id = p_org_id
and job.organization_id=p_org_id
and job.primary_item_id= bsad_rec.item_id
and wmt.wip_entity_id=job.wip_entity_id
and wmt.primary_item_id = bsad_rec.item_id
and bmrw.n_sequence = p_week --STP0909039 job
and wmt.to_operation_seq_num > 10
and wmt.fm_operation_seq_num = 10
and wmt.reason_id = 10 ---'81 WIP - Normal move'
and wmt.to_intraoperation_step_type = 1
and wmt.fm_intraoperation_step_type = 1
and wmt.transaction_date between (bmrw.week - 7 + (8 / 24)) and ( bmrw.week + (7.9998 / 24));
exception
when no_data_found then
wip_qty := 0;
end ;
begin
select round(sum(a.schedule_quantity) / 7, 0) * days_number
into act_qty
from bg_mds_detal_temp a
where a.inventory_item_id = bsad_rec.item_id;
exception
when no_data_found then
act_qty := 0;
end;
mct := wip_qty-act_qty;
fnd_file.put_line(fnd_file.output,
' 0');
--Print Dispersion( 2 )----
fnd_file.put_line(fnd_file.output,
' 0');
---Print lack of qty
fnd_file.put_line(fnd_file.output,
' 0');
--Print daily output qty--
fnd_file.put_line(fnd_file.output,
' 0');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
end;
---------------------
procedure print_sbr_header is
begin
fnd_file.put_line(fnd_file.output, '<?xml version="1.0"?>');
fnd_file.put_line(fnd_file.output,
'<?mso-application progid="Excel.Sheet"?>');
fnd_file.put_line(fnd_file.output,
' fnd_file.put_line(fnd_file.output,
' xmlns:o="urn:schemas-microsoft-com:office:office"');
fnd_file.put_line(fnd_file.output,
' xmlns:x="urn:schemas-microsoft-com:office:excel"');
fnd_file.put_line(fnd_file.output,
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"');
fnd_file.put_line(fnd_file.output,
' xmlns:html="'">http://www.w3.org/TR/REC-html40">');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, '
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
fnd_file.put_line(fnd_file.output, ' ');
--next wk sch--
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
------print report plan total -----------------------
fnd_file.put_line(fnd_file.output,
' ' ||
'Plan' || '');
for i in 1 .. 7 loop
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
end loop;
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
fnd_file.put_line(fnd_file.output, '
------print report act total -----------------------
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
'Act' || '');
m:=n-1;
for i in 1 .. 8 loop
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
end loop;
--Print Act Achieve %
fnd_file.put_line(fnd_file.output,
' 0');
--Print Dispersion( 1 ) Group by Item----
fnd_file.put_line(fnd_file.output,
' 0');
--Print Dispersion( 2 )- Group by Item & Address---
fnd_file.put_line(fnd_file.output,
' 0');
---Print lack of qty
fnd_file.put_line(fnd_file.output,
' 0');
--Print daily output qty--
fnd_file.put_line(fnd_file.output,
' 0');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
--------------------------------------------
fnd_file.put_line(fnd_file.output, ' ');
--act completed rate --
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' ' ||
'Act Achieve %' || '');
for i in 1 .. 8 loop
fnd_file.put_line(fnd_file.output,
' ');
fnd_file.put_line(fnd_file.output,
'0');
end loop;
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output,
' * * * End of Report * * *');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, '');
end;
-----------------------------------
BEGIN
commit;
CASE p_calendar_type
WHEN 'STD' THEN
v_start_week := ROUND(SYSDATE, 'YEAR');
WHEN 'FAS' THEN
v_start_week := TO_DATE(TO_CHAR(SYSDATE, 'YYYY') - 1 || '0401',
'YYYYMMDD');
ELSE
v_start_week := v_start_week;
END CASE;
FOR i IN 0 .. 53 LOOP
INSERT INTO apps.bg_mrp_running_weeks VALUES (i, v_start_week);
v_start_week := NEXT_DAY(v_start_week, 'SAT');
-- v_start_week:=v_start_week+7;
END LOOP;
select bmrw.week
into v_cur_week_start_day
from apps.BG_MRP_RUNNING_WEEKS bmrw
WHERE bmrw.n_sequence = P_week;
if trunc(sysdate) - (v_cur_week_start_day - 6) >= 6 then
days_number := 7;
else
days_number := trunc(sysdate) - (v_cur_week_start_day - 6);
end if;
------------------------------
begin
insert into BG_MDS_DETAL_TEMP
(SELECT *
FROM (
-- CNC --
SELECT msd.schedule_designator,
bom_item.*,
case
when (msdate.schedule_date > bmrw.week) then
bmrw.week
else
bmrw.week
end schedule_date,
case
when (msdate.schedule_date > bmrw.week) then
0
else
msdate.schedule_quantity
end schedule_quantity,
nvl(msdate.ATTRIBUTE1, 'Null') Address,
--TO_CHAR (msdate.schedule_date, 'WW') week,
case
when (msdate.schedule_date > bmrw.week) then
to_number(TO_CHAR(msdate.schedule_date, 'WW')) - 1
else
to_number(TO_CHAR(msdate.schedule_date, 'WW'))
end week,
bmrw.n_sequence fweek,
NEXT_DAY(bmrw.week, 'SUN') - 7 + (8 / 24) trans_date_fr,
NEXT_DAY(bmrw.week, 'SUN') + (7.9998 / 24) trans_date_to,
/* (
SELECT SUM (wmtv.transaction_quantity)
FROM apps.wip_move_transactions_v wmtv,
apps.bg_mrp_running_weeks bmrw
WHERE 1 = 1
AND wmtv.fm_operation_seq_num = 10
AND wmtv.to_operation_seq_num = 30
AND wmtv.organization_id = bom_item.organization_id
AND wmtv.primary_item_id = bom_item.inventory_item_id
AND bmrw.n_sequence = &p_week
AND wmtv.transaction_date BETWEEN NEXT_DAY (bmrw.week, 'SUN') - 7 + (8 / 24)
AND NEXT_DAY (bmrw.week, 'SUN') + (7.9998 / 24))*/
0 trans_qty,
(select m.schedule_quantity
from mrp_schedule_dates m, bg_mrp_running_weeks b
where 1 = 1
and m.schedule_designator = msd.schedule_designator
and m.schedule_level = 2
and b.n_sequence = p_week + 1
and nvl(m.ATTRIBUTE1, 'Null') =
nvl(msdate.ATTRIBUTE1, 'Null')
and m.inventory_item_id = bom_item.inventory_item_id
and to_char(m.schedule_date, 'ww') =
to_char(b.week + 1, 'ww')) next_wk_qty,
null ATTRIBUTE1,
null ATTRIBUTE2,
null ATTRIBUTE3
FROM apps.mrp_schedule_designators msd,
apps.mrp_schedule_items msi,
apps.mrp_schedule_dates msdate,
apps.bg_mrp_running_weeks bmrw,
(SELECT DISTINCT msi.organization_id,
msi.inventory_item_id,
msi.segment1 item_number,
msi.description,
nvl(msi.ATTRIBUTE2, 'Null') End_Cust,
SUBSTR(msi.description,
INSTR(msi.description, ',', -1) + 1) short_desc,
-- bic.supply_subinventory,
-- bic.supply_subinventory,
'' supply_subinventory,
'CNC' item_main_type,
msi.attribute5 item_sub_type, -- washing &plating
'' item_type
FROM apps.bom_bill_of_materials bbom,
apps.bom_inventory_components bic,
apps.mtl_system_items msi
WHERE 1 = 1
AND bbom.bill_sequence_id = bic.bill_sequence_id
AND bbom.organization_id = msi.organization_id
AND bbom.assembly_item_id = msi.inventory_item_id
AND NVL(bic.disable_date, SYSDATE) >= SYSDATE
AND bbom.organization_id = p_org_id --215
AND bbom.assembly_item_id IN
(SELECT inventory_item_id
FROM apps.mrp_schedule_items
WHERE 1 = 1
AND organization_id = p_org_id --215
AND schedule_designator = p_mds) --'CNC09W24'
) bom_item
WHERE 1 = 1
AND msd.organization_id = msi.organization_id
AND msd.schedule_designator = msi.schedule_designator
AND msi.organization_id = msdate.organization_id
AND msi.schedule_designator = msdate.schedule_designator
AND msi.inventory_item_id = msdate.inventory_item_id
AND msdate.organization_id = bom_item.organization_id
AND msdate.schedule_level = 2
AND msdate.inventory_item_id = bom_item.inventory_item_id
/* AND TO_CHAR(msdate.schedule_date, 'WW') between
TO_CHAR(bmrw.week, 'WW')*/
AND msdate.schedule_date between bmrw.week and bmrw.week+7
AND msd.organization_id = p_org_id --215
AND msd.schedule_designator = p_mds --'CNC09W24'
AND bmrw.n_sequence = p_week -- 30 --CNC_MDSW32
) rs);
end;
n_index := 10;
open cur_bsad(p_days_num, p_week);
fetch cur_bsad
into bsad_rec;
ctrl_cust := '@@@';
print_sbr_header;
if bsad_rec.end_customer <> ctrl_cust then
ctrl_cust := bsad_rec.end_customer;
end if;
while not cur_bsad%notfound loop
print_sbr_line;
fetch cur_bsad
into bsad_rec;
if bsad_rec.end_customer <> ctrl_cust then
print_sbr_total;
end if;
ctrl_cust := bsad_rec.end_customer;
item_id:=bsad_rec.item_id;--
end loop;
print_sbr_total;
x := -n_index;
print_sbr_footer(x);
end;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9182041/viewspace-630940/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9182041/viewspace-630940/
324
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
