未审批报销单

纵横四海

WORKFLOW_APPROVED_FLAG 为W表示该报销单被撤回。

1. 
   
2. select a.WORKFLOW_APPROVED_FLAG,
   
3.        a.expense_status_code,
   
4.        a.EXPENSE_CURRENT_APPROVER_ID,
   
5.        a.created_by,
   
6.       
   
7.        g.segment1,
   
8.        a.invoice_num,
   
9.        c.last_name,
   
10.        a.creation_date,
    
11.        replace(a.description, ',', '，') 　description,
    
12.        (select cc.last_name
    
13.           from hr_employees cc, per_assignments_f dd
    
14.          where cc.employee_id = dd.PERSON_ID
    
15.            and cc.employee_id = a.expense_current_approver_id
    
16.            and (sysdate between dd.EFFECTIVE_START_DATE and
    
17.                dd.EFFECTIVE_END_DATE)
    
18.         
    
19.         ) current_approver_name,
    
20.       
    
21.        b.item_description,
    
22.        g.segment1 || '.' || g.segment2 || '.' || g.segment3 || '.' ||
    
23.        g.segment4 || '.' || g.segment5 || '.' || g.segment6 || '.' ||
    
24.        g.segment7 || '.' || g.segment8 segments,
    
25.        b.amount,
    
26.        replace(b.JUSTIFICATION, ',', '，') 　JUSTIFICATION
    
27.   from ap_expense_report_headers_all a,
    
28.        ap_expense_report_lines_all   b,
    
29.        hr_employees                  c,
    
30.        per_assignments_f             d,
    
31.        hr_organization_units         e,
    
32.        gl_code_combinations          g
    
33. where a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
    
34.    and c.employee_id = d.PERSON_ID
    
35.    and a.employee_id = c.employee_id
    
36.    and d.ORGANIZATION_ID = e.organization_id
    
37.    and g.code_combination_id = b.code_combination_id
    
38.    and (sysdate between d.EFFECTIVE_START_DATE and d.EFFECTIVE_END_DATE)
    
39.       --and a.invoice_num = 'W105736'
    
40.       --and a.invoice_num = 'W26370'
    
41.    and a.expense_status_code = 'PENDMGR'
    
42.    and (a.WORKFLOW_APPROVED_FLAG <> 'W' or a.WORKFLOW_APPROVED_FLAG is null)
    
43.    and to_date(to_char(a.creation_date, 'YYYYMMDD'), 'YYYYMMDD') between
    
44.        gp_start_date and gp_end_date
    
45.    and a.org_id = gp_org_id
    
46. order by c.last_name, a.invoice_num;
    

复制代码