MD050-出库单设计

1. create or replace package body qpo_po_out_md050_pkg is
2.   PROCEDURE output(v_string VARCHAR2) IS
     BEGIN
       dbms_output.put_line(v_string);
       fnd_file.put_line(fnd_file.output,
          v_string);
     END output;
     
   PROCEDURE main(    errbuf        OUT         VARCHAR2,
                        retcode        OUT         NUMBER,
                        djmin          IN          NUMBER,
                        djmax          IN          NUMBER,
                        swlx           IN          VARCHAR2,
                        clrqmin        IN          DATE,
                        clrqmax        IN          DATE,
                        lydw           IN          VARCHAR2,
                        zdr            IN          VARCHAR2
                        ) IS
3. CURSOR               cur_header    IS
4. SELECT  qioh.doc_number,
           qioh.require_dept,
           qioh.doc_desc,
           qioh.tran_type_name,
           qioh.project_number||qioh.project_name  project_name,
           to_char(qioh.creation_date,'yyyymmdd') creat_date,
           qioh.created_emp_name,
           hou.NAME              org_name,
           qioh.header_id
   FROM    qinv_issue_order_header_v qioh,
           HR_ORGANIZATION_UNITS_V   hou
   WHERE   qioh.organization_id=hou.organization_id(+)
   AND     qioh.doc_number >=                    nvl(djmin,qioh.doc_number) 
   AND     qioh.doc_number <=                    nvl(djmax,qioh.doc_number)
   AND     qioh.tran_type_name=                  nvl(swlx,qioh.tran_type_name)
   AND     qioh.creation_date>=                  nvl(clrqmin,qioh.creation_date)
   AND     qioh.creation_date<=                  nvl(clrqmax,qioh.creation_date)
   AND     qioh.require_dept=                    nvl(lydw,qioh.require_dept)
   AND     qioh.created_emp_name=                nvl(zdr,qioh.created_emp_name)
   ORDER BY                                           qioh.doc_number;
5. CURSOR     cur_line(cur_header_id IN NUMBER)    IS
6. SELECT   qiol.line_id
   FROM     qinv_issue_order_line_v    qiol
   WHERE    qiol.header_id=cur_header_id;
7. CURSOR   cur_detail(cur_header_id IN NUMBER,cur_line_id IN NUMBER) IS
8. SELECT qiod.item_id,
          qiod.item_desc,
          qiod.tran_uom,
          qiod.subinv_code,
          qiod.locator_code,
          qiod.issued_quantity      
   FROM   qinv_issue_order_detail_v     qiod
   WHERE  qiod.header_id=cur_header_id
   AND    qiod.line_id=cur_line_id;
9. line_no NUMBER :=0;
   time_print VARCHAR2(50);
10. BEGIN
11. SELECT       to_char(SYSDATE,'yyyy')||'年'||to_char(SYSDATE,'mm')||'月'||to_char(SYSDATE,'dd')||'日' INTO time_print
    FROM         dual;
12. output('<?xml version="1.0" encoding="utf-8"?>');
13. output('<main>');
14. FOR h IN        cur_header        LOOP
15.     output('<list>');
       
        output('<header>');
       
        output('<djbh>'||h.doc_number||'</djbh>');
        output('<lydw>'||h.require_dept||'</lydw>');
        output('<dysj>'||time_print||'</dysj>');
        output('<djsm>'||h.doc_desc||'</djsm>');
        output('<swlx>'||h.tran_type_name||'</swlx>');
        output('<clrq>'||h.creat_date||'</clrq>');
        output('<kczz>'||h.org_name||'</kczz>');
        output('<gcxm>'||h.project_name||'</gcxm>');
        output('<clr>'||h.created_emp_name||'</clr>');
        output('<bz></bz>');
       
        output('</header>');
       
        FOR i IN            cur_line(h.header_id)                 LOOP
           
            output('<line>');
                  
                   line_no:=0;
                   FOR j IN  cur_detail(h.header_id,i.line_id)               LOOP
                      
                       line_no:=line_no+1;
                  
                       output('<detail>');
                      
                              output('<hh>'||line_no||'</hh>');
                              output('<wzbm>'||j.item_id||'</wzbm>');
                              output('<wzmc>'||j.item_desc||'</wzmc>');
                              output('<fczk>'||j.subinv_code||'</fczk>');
                              output('<fchw>'||j.locator_code||'</fchw>');
                              output('<jldw>'||j.tran_uom||'</jldw>');
                              output('<sl>'||j.issued_quantity||'</sl>');
                              output('<dwcb></dwcb>');
                              output('<je></je>');
                              output('<fbfx></fbfx>');
                              output('<bz></bz>');
                      
                       output('</detail>');
                  
                   END LOOP;
                  
           
            output('</line>');
        output('<jehj></jehj>');
       
        END LOOP;
    output('</list>');
16. END LOOP;
17. output('</main>');
18. EXCEPTION
        WHEN no_data_found THEN
          retcode := 1;
19. END main;
20. end qpo_po_out_md050_pkg;