none
convertir to SQL RRS feed

  • Question

  • select o.ord_code "Contract Number",o.ord_desc "Contract Description",(select c.com_desc from d7i.r5companies c where c.com_code=o.ord_supplier) "Vendor Description",

    (select max(u.usr_mrc) from d7i.r5users u,d7i.r5requisitions r,d7i.r5orderlines ol where

    u.usr_code=r.req_origin and r.req_code=ol.orl_req and o.ord_code=ol.orl_order group by o.ord_code) "Dept",

    (select p.prv_dvalue from d7i.r5propertyvalues p where p.prv_property='CONT-EFF' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB') "Effective Date",

    (select p.prv_dvalue from d7i.r5propertyvalues p where p.prv_property='CONT-EXP' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB') "Expire Date",

    decode(sign((select p.prv_dvalue from d7i.r5propertyvalues p where p.prv_property='CONT-EXP' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB')-

    sysdate),1,'YES','NO') "Live",

    (select p.prv_nvalue from d7i.r5propertyvalues p where p.prv_property='HSECAT' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB') "HSE Category",

    (select p.prv_value from d7i.r5propertyvalues p where p.prv_property='SCOWNER' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB') "Contract Owner",

    (select p.prv_value from d7i.r5propertyvalues p where p.prv_property='SC2OWNER' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB') "Secondary Owner"

    from d7i.r5orders o where o.ord_class='SC' and o.ord_rstatus='A' and --( (to_char(o.ord_approv,'YYYY')>'2017') or (to_char(o.ord_approv,'YYYY')<'2018' and

    ( trunc(sysdate)<=(select trunc(p.prv_dvalue) from d7i.r5propertyvalues p where p.prv_property='CONT-EXP' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB')--))

    or ((select to_char(p.prv_dvalue,'YYYY') from d7i.r5propertyvalues p where p.prv_property='CONT-EXP' and p.prv_rentity='PORD' and p.prv_class='SC' and p.prv_code=o.ord_code||'#GB') = '2018')

    )

    dimanche 30 septembre 2018 10:40