TXT

sql

By Laura Daniels,2014-10-11 15:05
14 views 0
sql

select v.customer_number,--

     v.customer_name,--

     v.customer_type,--

     v.working_type || 'Àà' as working_type,

     v.rongliang,

     v.xingzhi,

     v.xingshi,

     v.contact_person,--

     v.phone_number,--

     v.address,--

     get_expense_content(v.line_id) as expense_content,

     to_char(to_date(v.begin_date, 'dd-mon-yy'),

    'yy"Äê"mm"ÔÂ"dd"ÈÕ"')as v_begin_date,

     to_char(to_date(v.end_date, 'dd-mon-yy'),

    'yy"Äê"mm"ÔÂ"dd"ÈÕ"') as v_end_date,

     sum(decode(v.req_item_id, 80431, v.req_qty, null)) as ??Æ?,

     sum(decode(v.req_item_id, 82699, v.req_qty, 80220, v.req_min_qty, null)) as ÌØÖÆ,

     sum(decode(v.req_item_id, 80402, v.req_qty, null)) as ÆÕ??,

     sum(decode(v.req_item_id, 80451, v.req_qty, null)) as Ê??þ?ÈËÜ,

     sum(decode(v.req_item_id, 80452, v.req_qty, null)) as Ê??þ?ÈÖ?,

     sum(decode(v.req_item_id, 80456, v.req_qty, null)) as Çå??,

     sum(decode(v.req_item_id, 80455, v.req_qty, null)) as ??µä,

     sum(decode(v.req_item_id, 80935, v.req_qty, null)) as Öé????,

     sum(decode(v.req_item_id, 81199, v.req_qty, null)) as ?ÞÌØÖÆ,

     sum(decode(v.req_item_id, 81201, v.req_qty, null)) as Áã?ÈÇå??,

     sum(decode(v.req_item_id, 82579, v.req_qty, null)) as Îå?ÙºÁÉý??Éú,

     sum(decode(v.req_item_id, 82579, v.req_qty, null)) as ÆäËûÆ?ÖÖ,

     sum(decode(v.req_item_id, 0, v.req_qty, null)) as ËùÓÐÆ?ÖÖ,

     v.unit5_name,--

     v.employee_name,--

     v.apply_number,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80431,v_date)as ÏúÊÛ??Æ?,

     nvl(xe_nj_pkg.GET_SALE_QTY(v.customer_id,82699,v_date),0)

     +nvl(xe_nj_pkg.GET_SALE_QTY(v.customer_id,80220,v_date),0) as ÏúÊÛÌØÖÆ,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80402,v_date)as ÏúÊÛÆÕ??,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80451,v_date)as ÏúÊÛÊ??þ?ÈËÜ,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80452,v_date)as ÏúÊÛÊ??þ?ÈÖ?,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80456,v_date)as ÏúÊÛÇå??,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80455,v_date)as ÏúÊÛ??µä,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,80935,v_date)as ÏúÊÛÖé????,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,81199,v_date)as ÏúÊÛ?ÞÌØÖÆ,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,81201,v_date)as ÏúÊÛÁã?ÈÇå??,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,82579,v_date)as ÏúÊÛÎå?ÙºÁÉý??Éú,

     xe_nj_pkg.GET_SALE_QTY(v.customer_id,88888,v_date)as ÏúÊÛÆäËûÆ?ÖÖ,

     xe_nj_pkg.GET_SALE_QTY_cx(v.customer_id,v_date) as ?ÙÏúÔù?Æ,

     xe_nj_pkg.GET_SALE_QTY_zj(v.customer_id,'?Ò?ÇÔù?Æ',v_date) ?Ò?ÇÔù?Æ,

    xe_nj_pkg.GET_SALE_QTY_zj(v.customer_id,'Èȵã-?Ì??Ôù?Æ',v_date) ?Ì??Ôù?Æ,

     v.note

     from xe_expense_apply_v4 v,XE_ORG_SEL_SESSION s

     where s.session_id=:parameter.session_id

     and (CARRY_ID =s.org_id

     or CARRY_ID in(

     select c.customer_id

     from xe_customer c

     where c.customer_type = 1

     and c.unit_id =s.org_id))

     and v.begin_date<=:CONDITION.BEGIN_DATE

     and v.end_date>=:CONDITION.END_DATE

     and nvl(customer_id,0)

    =decode(:CONDITION.customer_id,null,nvl(customer_id,0),:CONDITION.customer_id)

     and nvl(employee_id,0)

    =decode(:CONDITION.employee_id,null,nvl(employee_id,0),:CONDITION.employee_id)

     and

    nvl(v.cust_type,0)=nvl(:condition.CUST_TYPE,nvl(v.cust_type,0))

     --nd

    nvl(expense_type,0)=decode(:CONDITION.expense_type,null,nvl(expense_type,0),:CONDITION.expense_type)

     and

    nvl(item_id,0)=decode(:CONDITION.item_id,null,nvl(item_id,0),:COND

ITION.item_id)

     -- and

    nvl(sales_type,0)=nvl(:CONDITION.SALE_TYPE,nvl(sales_type,0))

     --and

    nvl(is_exp,0)=nvl(:CONDITION.IS_EXP,nvl(is_exp,0))

     --and

    nvl(carry_id,0)=decode(:CONDITION.carry_id,null,nvl(carry_id,0),:C

    ONDITION.carry_id)

     --and nvl(:CONDITION.begin_date,BEGIN_DATE) between

    BEGIN_DATE and END_DATE

     --and nvl(apply_number,0) like nvl(:CONDITION.apply_number,nvl(apply_number,0))

     --and

    nvl(status,0)=nvl(:CONDITION.STATUS,nvl(status,0))

     --and v.apply_number='0042817'

     group by v.customer_number,

     v.customer_name,

     v.customer_type,

     working_type,

     v.rongliang,

     v.xingzhi,

     v.xingshi,

     v.contact_person,

     v.phone_number,

     v.address,

     v.line_id,

     v.begin_date,

     v.end_date,

     v.unit5_name,

     v.employee_name,

     v.apply_number,

     v.customer_id,

     v.note

Ëø??µÄ

    select v.customer_number,

     v.customer_name,

     v.cust_type,

     v.cust_level,

     v.contact_person,

     v.phone,

     v.address,

     v.bill_no,

     v.stock_date,

     v.ÏúÊÛ??Æ?,

     v.ÏúÊÛÌØÖÆ,

     v.ÏúÊÛÆÕ??,

     v.ÏúÊÛÊ??þ?ÈËÜ,

     v.ÏúÊÛÊ??þ?ÈÖ?,

     v.ÏúÊÛÇå??,

     v.ÏúÊÛ??µä,

     v.ÏúÊÛÇåË?ËÜ,

     v.ÏúÊÛÇåË?Ö?,

     v.ÏúÊÛÂÌË?,

     v.ÏúÊÛ?ÞÖé,

     v.ÏúÊÛ?Þ??,

     v.ÏúÊÛÖé????,

     v.?Å?ÈÌØÖÆ??Éú?Þ,

     v.Áã?ÈÇå??,

     v.ÏúÊÛÊ??È??ÉúÎå?ÙºÁÉýÖ?,

     v.?ÙÏú??Æ?,

     v.?ÙÏúÌØÖÆ,

     v.?ÙÏúÆÕ??,

     v.?ÙÏúÊ??þ?ÈËÜ,

     v.?ÙÏúÊ??þ?ÈÖ?,

     v.?ÙÏúÇå??,

     v.?ÙÏú??µä,

     v.?ÙÏúÇåË?ËÜ,

     v.?ÙÏúÇåË?Ö?,

     v.?ÙÏúÂÌË?,

     v.?ÙÏú?ÞÖé,

     v.?ÙÏú?Þ??,

     v.?ÙÏúÖé????,

     v.?ÙÏúÁã?ÈÇå??,

     v.?ÙÏúÊ??È??ÉúÎå?ÙºÁÉýÖ?,

     v.ÏÖ?ð?µÀû,

     v.?Î?Î??5Ôª,

     v.?Î?Î??10Ôª,

     v.?Î?Î??20Ôª,

     v.?Î?Î??8Ôª,

     v.?Î?Î??18Ôª,

     v.?Î?Î??38Ôª,

     v.?ÙÏú?Å?ÈÌØÖÆ??Éú?Þ,

     v.promotion_name,

     v.unit5_name,

     v.employee_name,

     v.note_h,

     xe_nj_pkg.get_warehouse_name(warehouse_id,v.org_id) as warehouse_name

     from xe_load_expense_gzwq_v2 v,XE_ORG_SEL_SESSION s

     where v.org_id in (select u.unit_id from xe_unit_v2 u start with u.unit_id =104494 connect by prior u.level_id=u.parent_id)

     and v.stock_date between

    to_char(:CONDITION.BEGIN_DATE,'yyyy-mm-dd') and

    to_char(:CONDITION.END_DATE,'yyyy-mm-dd')

     and ((:CONDITION.UNIT_TYPE=0 and v.org_id=s.org_id) or (:CONDITION.UNIT_TYPE=1 and v.unit_id=s.org_id and v.org_id=nvl(:CONDITION.UNIT_ID5,v.org_id)))

     and s.session_id=:parameter.session_id

     and

    nvl(v.employee_id,0)=nvl(:CONDITION.EMPLOYEE_ID,nvl(v.employee_id,0))

     and

    nvl(v.customer_id,0)=nvl(:CONDITION.CUSTOMER_ID,nvl(v.customer_id,0))

     and

    nvl(v.cust_type,0)=nvl(:CONDITION.CUST_TYPE,nvl(v.cust_type,0))

     and

    nvl(warehouse_id,0)=nvl(:CONDITION.WAREHOUSE_ID,nvl(warehouse_id,0))

     and

    nvl(is_exp,0)=nvl(:CONDITION.IS_EXP,nvl(is_exp,0))

     --and

    nvl(sales_type,0)=nvl(:CONDITION.SALE_TYPE,nvl(sales_type,0))

     --and

    nvl(item_id,0)=nvl(:CONDITION.ITEM_ID,nvl(item_id,0))

     --and

    nvl(approval_level,0)=nvl(:CONDITION.APPROVAL_LEVEL,nvl(approval_level,0))

     --and nvl(item_no,'0') between

    nvl(:CONDITION.BEGIN_ITEM_NO,nvl(item_no,'0')) and nvl(:CONDITION.END_ITEM_NO,nvl(item_no,'0'))

?ÙÏúµÄ

Report this document

For any questions or suggestions please email
cust-service@docsford.com