隨手筆記

declare
po_number varchar2(20):='PO單號'; --key
v_cancel_reason varchar2(100) := 'PO created for price tracking purpose only';
begin

  if po_number is null or v_cancel_reason is null then 
    dbms_output.put_line ('null');
  
  else 
      --po header
      FOR R1 IN (select *
                   from po_headers_all
                  where segment1 =po_number)
      loop
       
       --po_header
       insert into po_headers_all_bk
       select *
         from po_headers_all
        where po_header_id =r1.po_header_id;
       
       update po_headers_all
          set closed_date = sysdate,
              cancel_flag = 'Y',
              closed_code = 'CLOSED'
        where po_header_id =r1.po_header_id
          and org_id = r1.org_id;--r1.po_header_id
       
       --po lines
       insert into po_lines_all_bk
       select *
         from po_lines_all
        where po_header_id =r1.po_header_id;
        
       
       update po_lines_all
          set quantity = 0,
              cancel_flag= 'Y',
              cancelled_by = -1,
              cancel_date =sysdate,
              cancel_reason = v_cancel_reason,
              closed_code ='CLOSED',
              closed_date  = sysdate,
              closed_reason ='Update Closure Status Rolled Up',
              closed_by =-1
        where po_header_id =r1.po_header_id
          and org_id = r1.org_id;
        
       --po line location
         for r2 in (select *
                     from po_lines_all
                    where po_header_id =r1.po_header_id
                      and org_id = r1.org_id)
                    
         loop
           insert into po_line_locations_all_bk
           select *
             from po_line_locations_all
            where po_line_id =r2.po_line_id;
            
           UPDATE po_line_locations_all
              SET quantity_cancelled = quantity,
                  cancel_flag        = 'Y',
                  cancelled_by       = -1,
                  cancel_date        =sysdate,
                  cancel_reason      = v_cancel_reason,
                  closed_code        = 'CLOSED',
                  closed_reason      ='Update Closure Status Rolled Up',
                  closed_date        =sysdate,
                  closed_by          =-1
            where po_line_id =r2.po_line_id;
       
         
         insert into po_distributions_all_bk
         select *
           from po_distributions_all
          where po_line_id =r2.po_line_id ;
    
         update po_distributions_all
            set quantity_cancelled =quantity_ordered
          where po_line_id =r2.po_line_id;
          
        end loop;
  
      end loop;
      commit;
  end if;
end;