êP춈ó±íé_°lµÄ†–î}

sunjinhai

ÔÚßÐЈó±í֮ǰ£¬ÎÒ•þßÐÐbefore Reportß@‚€Report trigger,
ÑeÃæµÄ³ÌÐòÊÇÒÔÏ£¬´ó‚b‚ƒŽÍæ¿´¿´ÓЛ]ÓÐåeÕ`¡£
function BeforePForm return boolean is
begin
  SRW.USER_EXIT('FND SRWINIT');
--  dbms_application_info.set_client_info('262');
  select gsb.name into :p_company
    from
gl_sets_of_books gsb,financials_system_parameters fsp
  where  gsb.set_of_books_id=fsp.set_of_books_id;
declare
   x_inventory_item_id     MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE;
   x_Group_Id              Number;
   x_organization_id       number;
CURSOR C_find_bom IS
select distinct msi.segment1 Part_number,msi.organization_id
from bom_bill_of_materials bbom,
      mtl_system_items msi
where msi.organization_id = bbom.organization_id
   and msi.inventory_item_id = bbom.assembly_item_id
   and msi.segment1 like '4111%'
   and msi.item_type like 'Anctive'
   and msi.organization_id =433;
BEGIN
  delete from MKL_FP_BOM_LISTS;
  commit;
  delete from mkl_fp_shortage_bom_temp;
  commit;
  delete from Surplus_shortage_detail;
FOR C2 IN C_find_bom LOOP
   
   Select MKL_FP_BOM_LISTS_S.Nextval Into x_Group_Id From Dual;
   SELECT inventory_item_id,organization_id  
     INTO x_inventory_item_id,x_organization_id
     FROM  mtl_system_items
     WHERE segment1=C2.part_number
       AND organization_id=c2.organization_id;
   Insert Into mkl_fp_BOM_LISTS (group_id,organization_id, Assembly_Item_Id)
   Values (x_Group_Id,x_organization_id,x_inventory_Item_Id);
   mkl_shortage_bom_exploder(x_Group_Id,x_organization_id,x_inventory_Item_Id, 0, sysdate, 1, 1, 60, Null);
   commit;
   END LOOP;
  insert into SURPLUS_SHORTAGE_DETAIL
((select distinct msi1.segment1,msi1.description, msi2.segment1,
       msi2.description, msi1.organization_id
from mkl_fp_shortage_BOM_TEMP a,
     mkl_fp_shortage_BOM_TEMP b,
     mtl_system_items msi1,
     mtl_system_items msi2
where a.organization_id = b.organization_id
  and a.top_parent_item_id <>b.top_parent_item_id
  and a.organization_id = msi1.organization_id
  and a.top_parent_item_id = msi1.inventory_item_id
  and a.item_number = b.item_number
  and b.component_s_exists_flag <>1
  and b.organization_id = msi2.organization_id
  and b.top_parent_item_id = msi2.inventory_item_id
  and a.substitute_flag <>1
  and substr(a.item_number,1,4) in
    (select f.item_prefix
     from fp.mkl_fp_parts_list f
     where f.category =(_Category)
       and f.disable_date is null))   
intersect
(select distinct msi1.segment1,msi1.description, msi2.segment1,
       msi2.description, msi1.organization_id
from mkl_fp_shortage_BOM_TEMP a,
     mkl_fp_shortage_BOM_TEMP b,
     mtl_system_items msi1,
     mtl_system_items msi2
where a.organization_id = b.organization_id
  and a.top_parent_item_id <>b.top_parent_item_id
  and a.organization_id = msi1.organization_id
  and a.top_parent_item_id = msi1.inventory_item_id
  and a.item_number = b.item_number
  and b.component_s_exists_flag <>1
  and a.substitute_flag <>1
  and b.organization_id = msi2.organization_id
  and b.top_parent_item_id = msi2.inventory_item_id
  and substr(a.item_number,1,4) in
   (select f.item_prefix
     from fp.mkl_fp_parts_list f
     where f.category in (select decode((:P_Category2),null,(:P_Category)) from dual)
       and f.disable_date is null))
intersect
(select distinct msi1.segment1,msi1.description, msi2.segment1,
       msi2.description, msi1.organization_id
from mkl_fp_shortage_BOM_TEMP a,
     mkl_fp_shortage_BOM_TEMP b,
     mtl_system_items msi1,
     mtl_system_items msi2
where a.organization_id = b.organization_id
  and a.top_parent_item_id <>b.top_parent_item_id
  and a.organization_id = msi1.organization_id
  and a.top_parent_item_id = msi1.inventory_item_id
  and a.item_number = b.item_number
  and b.component_s_exists_flag <>1
  and a.substitute_flag <>1
  and b.organization_id = msi2.organization_id
  and b.top_parent_item_id = msi2.inventory_item_id
  and substr(a.item_number,1,4) in
   (select f.item_prefix
     from fp.mkl_fp_parts_list f
     where f.category in (select decode((:P_Category3),null,(:P_Category)) from dual)
       and f.disable_date is null))
intersect
(select distinct msi1.segment1,msi1.description, msi2.segment1,
       msi2.description, msi1.organization_id
from mkl_fp_shortage_BOM_TEMP a,
     mkl_fp_shortage_BOM_TEMP b,
     mtl_system_items msi1,
     mtl_system_items msi2
where a.organization_id = b.organization_id
  and a.top_parent_item_id <>b.top_parent_item_id
  and a.organization_id = msi1.organization_id
  and a.top_parent_item_id = msi1.inventory_item_id
  and a.item_number = b.item_number
  and a.substitute_flag <>1
  and b.component_s_exists_flag <>1
  and b.organization_id = msi2.organization_id
  and b.top_parent_item_id = msi2.inventory_item_id
  and substr(a.item_number,1,4) in
   (select f.item_prefix
     from fp.mkl_fp_parts_list f
     where f.category in (select decode((:P_Category4),null,(:P_Category)) from dual)
       and f.disable_date is null)));
end;
  return (TRUE);
end;