Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.
create or replace view rpt_magazine_ad_prod_summary as select i.transdate as calendardate, i.effectivedate as runday, i.transnum as transnumber, i.amount as amt, c.acct_name as ADVERTISER, c.accountnumber as acctnum, i.glacct as glnumber, i.glacctdesc as glname, a.productid, ac.name as adjcause, ar.name as adjreason, j.username as salesrep, j.salesteam as salesteam, o.ordernumber as ordernum, nvl(a.product, p.product) as productname, a.productalias, a.category as productcategory, decode(nvl(s.AdSubTypeId, p.adsubtypeid), 1, 'National', 2, 'National', 4, 'National', 'Retail') as adsubtype, a.placement as placementname, s.position, a.adcategory, decode(t.goalid, 0, 'Account Level Adj', t.name) as goalcategory, a.edition, c.acct_type as customertype_client, decode(i.transtype, 'Invoice', 'INVOICEAMT', 'Adjustment Invoice', 'INVOICEAMT', 'ADJUSTMENT') as entrytype, i.linetype from ocr_invoice i join pmt.pmt_customer c ON c.accountid = i.advertisorid AND c.acct_type IN ('Commercial', 'Small Business', 'Private Party') JOIN pmt.ocr_date d ON i.t_dateid = d.id join pmt.adj_causes ac on i.cdcauseid = ac.id join pmt.adj_reasons ar on i.cdreasonid = ar.id join newjobuser j on i.jobid = j.jobid join goal_category t on t.goalid = i.goalid left join pmt.pmt_order o on o.adorderid = i.adorderid left join pmt.pmt_adschedule s on i.adrunschedid = s.id left join pmt.pmt_prpschedule p on i.prprunschedid = p.id LEFT JOIN pmt.adproducts a on a.productid = decode(i.adrunschedid, 0, nvl(p.productid, 0), nvl(s.productid,0)) and a.placementid = decode(i.adrunschedid, 0, nvl(p.placementid, 0), nvl(s.placementid, 0)) and a.editionid = decode(i.adrunschedid, 0, nvl(p.editionid, 0), nvl(s.editionid, 0)) WHERE i.glacctclass = 'Revenue' AND ac.appliestocommission = 1 and nvl(j.salesteam, ' ') not in ('Finance', 'IT','Commercial Printing','Marketing','No Team') and nvl(s.placement, ' ') <> 'SoloMail Postage' and nvl(j.userlname, ' ' ) NOT IN ('Adbase','AdBase-E','AdStar') and a.productid IN (54,63,55,60,69,105,106,127) and (( i.transdate between Trunc(Add_Months(SYSDATE-27,-12),'Y') and Trunc(Last_Day(Add_Months(SYSDATE-27,-12))+1) ) OR ( i.transdate between Trunc(SYSDATE,'Y') and Trunc(Last_Day(SYSDATE-27)+1) ))
|Roles with read only access to 10 packages under one schema and the same for Packages Bodies.||12||30|
|Need an example for Oracle Database and DDL Triggers||6||37|
|Password Hashing vs Encryption for long term viability||3||71|
|Unix How to Bulk Remove Multiple Directories from FileSystem||10||66|
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
14 Experts available now in Live!