SET FEED OFF
SET ECHO OFF
SET HEADING OFF
SET LINESIZE 1000
SET PAGESIZE 0
SET VERIFY OFF
SET TERM OFF
SET TRIMSPOOL ON
WHENEVER SQLERROR EXIT SQL.SQLCODE
SPOOL &&3;
column period_hierarchy_id_start noprint new_value rptPeriodHierarchyStart
column period_hierarchy_id_end noprint new_value rptPeriodHierarchyEnd
column report_id noprint new_value rptId
-- Get Period Hierarchy Start / End
--SELECT period_hierarchy_id_start,
-- period_hierarchy_id_end,
-- report_id
--FROM EMR_REPORTS
--WHERE report_id = &&1;
 
-- Get the year we want to run for
select
(select period_hierarchy_id
from period_hierarchy a
where a.level_name ='Year'
and a.year=(selectyearfrom period_hierarchy
where period_hierarchy_id = z.period_hierarchy_id_start)) PERIOD_HIERARCHY_ID_START,
(select period_hierarchy_id
from period_hierarchy a
where a.level_name ='Year'
and a.year=(selectyearfrom period_hierarchy
where period_hierarchy_id = z.period_hierarchy_id_start)) PERIOD_HIERARCHY_ID_END,
z.report_id
from emr_reports z
where z.report_id =&&1;
-- Now Get Month Level Period Hierarchy Start
column period_hierarchy_id noprint new_value rptMthPeriodHierarchyStart
SELECT period_hierarchy_id
FROM period_hierarchy
WHERE hierarchy_date =(SELECT DECODE(level_name,'Month',hierarchy_date,'Quarter',ADD_MONTHS(hierarchy_date,-2),'Year',ADD_MONTHS(hierarchy_date,-11))
FROM period_hierarchy
WHERE period_hierarchy_id =&&rptPeriodHierarchyStart)
AND level_name ='Month';
-- Now Get Month Level Period Hierarchy End
column period_hierarchy_id noprint new_value rptMthPeriodHierarchyEnd
SELECT period_hierarchy_id
FROM period_hierarchy
WHERE hierarchy_date =(SELECT hierarchy_date
FROM period_hierarchy
WHERE period_hierarchy_id =&&rptPeriodHierarchyEnd)
AND level_name ='Month';
SPOOL OFF;
SPOOL &&2;
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 1000
SET PAGESIZE 0
SETSPACE 0
SET NEWPAGE 0
SET TRIMSPOOL ON
SELECT'Federal Tax ID Number'||chr(9)||
'Merchant Name'||chr(9)||
'Street Address'||chr(9)||
'City'||chr(9)||
'State'||chr(9)||
'Zip'||chr(9)||
'Incorporation Status'||chr(9)||
'MCC Code'||chr(9)||
'MCC Desc'||chr(9)||
'1099able?'||chr(9)||
'Merchant Market Hierarchy ID'||chr(9)||
'Level Number'||chr(9)||
'Proprietor Name'||chr(9)||
'NAICS Code'||chr(9)||
'NAICS Description'||chr(9)||
'Quarter 1 Count'||chr(9)||
'Quarter 1 Amount'||chr(9)||
'Quarter 2 Count'||chr(9)||
'Quarter 2 Amount'||chr(9)||
'Quarter 3 Count'||chr(9)||
'Quarter 3 Amount'||chr(9)||
'Quarter 4 Count'||chr(9)||
'Quarter 4 Amount'||chr(9)||
'Total Transaction Count'||chr(9)||
'Total Transaction Amount'||chr(9)||
'Merchant Legal Name'||chr(9)||
'Phone Number'||chr(9)||
'DUNS Number'
FROM DUAL;
SELECT/*+ ordered full(summ) use_hash(summ,emr,mch) */
NVL(emr.cleansed_merchant_tax_id,NVL(emr.mc_tax_id,' ')) TAXID,
chr(9),
NVL(emr.cleansed_merchant_name,emr.mc_merchant_dba_name) MERNAME,
chr(9),
NVL(emr.cleansed_merchant_street_addr,emr.mc_address)||chr(9)||
NVL(emr.cleansed_city_name,emr.mc_city)||chr(9)||
SUBSTR(NVL(emr.cleansed_state_province_code,emr.mc_state),1,2)||chr(9)||
NVL(emr.cleansed_merchant_postal_code,emr.mc_zipcode)||chr(9)||
NVL(emr.mc_busstat,emr.db_busstat)STATUS,
chr(9),
mch.merchant_category_code MCCCODE,
chr(9),
mch.merchant_category_name MCCDESC,
chr(9),
mch.flag_1099 FLAG,
chr(9),
MAX(emr.new_loc_id) HIERARCHYID,
chr(9),
MAX(mc_level_num) LEVELNUM,
chr(9),
/*DECODE(MAX(emr.at_lastnam_fstnam), ', ', ' ',MAX(emr.at_lastnam_fstnam))|| chr(9)||*/
DECODE(MAX(emr.db_solename),', ',' ',MAX(emr.db_solename))|| chr(9)||
/*MAX(emr.at_naicscode1)|| chr(9)||*/
MAX(emr.db_naicscode1)|| chr(9)||
/*MAX(emr.at_naicsdesc1) STRING2,*/
MAX(emr.db_naicsdesc1) STRING2,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 0 AND&rptMthPeriodHierarchyStart + 2 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q1C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 0 AND&rptMthPeriodHierarchyStart + 2 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q1A,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 3 AND&rptMthPeriodHierarchyStart + 5 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q2C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 3 AND&rptMthPeriodHierarchyStart + 5 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q2A,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 6 AND&rptMthPeriodHierarchyStart + 8 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q3C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 6 AND&rptMthPeriodHierarchyStart + 8 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q3A,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 9 AND&rptMthPeriodHierarchyStart + 11 THEN
ROUND(NVL(summ.net_purch_disb_count,0),0)ELSE 0 END) Q4C,
chr(9),
SUM(CASEWHEN summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart + 9 AND&rptMthPeriodHierarchyStart + 11 THEN
ROUND(NVL(summ.net_purch_disb_amount,0),2)ELSE 0 END) Q4A,
chr(9),
SUM(summ.net_purch_disb_count) TOTAL_CNT,
chr(9),
SUM(summ.net_purch_disb_amount) TOTAL_AMT,
chr(9),
MAX(NVL(emr.cleansed_legal_corporate_name,emr.legal_name)) LEGALNAME,
chr(9),
emr_common_utilities.emr_format_phone(MAX(NVL(emr.cleansed_country_code,emr.mc_country_code)),MAX(NVL(emr.cleansed_merchant_tel_nbr,emr.mc_phone))) PHONE,
chr(9),
MAX(NVL(emr.cleansed_dun_bradstreet_nbr,emr.db_duns_number)) DUNSNUM
FROM emr_report_org_xref rox,
emr_15_5_20_10 summ,
emr_location emr,
merchant_category_hierarchy mch
WHERE rox.report_id =&&rptId
AND rox.report_request_id > 1
AND summ.organization_id = rox.organization_id
AND summ.period_hierarchy_id BETWEEN&rptMthPeriodHierarchyStart AND&rptMthPeriodHierarchyEnd
AND summ.merchant_market_hierarchy_id = emr.old_loc_id
AND mch.merchant_category_hierarchy_id = summ.merchant_category_hierarchy_id
GROUPBY NVL(emr.cleansed_merchant_tax_id,NVL(emr.mc_tax_id,' ')),
NVL(emr.cleansed_merchant_name,emr.mc_merchant_dba_name),
NVL(emr.cleansed_merchant_street_addr,emr.mc_address),
NVL(emr.cleansed_city_name,emr.mc_city),
SUBSTR(NVL(emr.cleansed_state_province_code,emr.mc_state),1,2),
NVL(emr.cleansed_merchant_postal_code,emr.mc_zipcode),
NVL(emr.mc_busstat,emr.db_busstat),
mch.merchant_category_code,
mch.merchant_category_name,
mch.flag_1099,
NVL(emr.cleansed_legal_corporate_name,emr.legal_name),
NVL(emr.cleansed_dun_bradstreet_nbr,emr.db_duns_number)
ORDERBY 1,3;
SPOOL OFF;
EXIT;