/****************************************************************************************************************/
/* THIS CODE IS INCLUDED IN BAEP_DAILY_SHELL.SAS.
/****************************************************************************************************************/
/* This code inherits the following statements from BAEP_Daily_Shell.sas: */
/* *libname BAEPData "/sasdata/nonfinance/shared/data/30day/troop support/BAEP/Scheduled_Jobs/Data"; */
/* %let exptOutPath = %str(/sasdata/nonfinance/shared/data/30day/troop support/BAEP/Scheduled_Jobs/Export); */
/* %let outPath=%str(~/sas_pc_files_data/troop support/BAEP/Brianna Lake/Daily Runs/Output); */
/****************************************************************************************************************/
data _NULL_;
call symputx("reportdate",put(intnx('day',today(),-1),mmddyyd10.));
/*MUST CHANGE NUMBER OF DAYS TO SUBTRACT FOR PRIOR MONTH'S EOM */
/*Formatted for humans.*/
run;
%let outfile=%str(XXX Report_&reportdate..xlsx);
/* %put &outfile.; */
%let currdate=%sysfunc(today()); /* No formatting applied yet. */
%let startdate=%sysfunc(intnx(month,&currdate,-1,B)); /* No formatting applied yet. */
%let enddate=%sysfunc(intnx(month,&currdate,-1,E)); /* No formatting applied yet. */
data _NULL_;
startdate_=&startdate.;
enddate_=&enddate.;
call symput('startdate_edw',CATS(year(startdate_),put(month(startdate_),z2.),put(day(startdate_),z2.))); /* Formatted for EDW HANA. */
call symput('enddate_edw',CATS(year(enddate_),put(month(enddate_),z2.),put(day(enddate_),z2.))); /* Formatted for EDW HANA. */
call symput('startdate_hum',put(startdate_,mmddyy10.));
/* Formatted for humans. /* Formatted for humans. */
call symput('enddate_hum',put(enddate_,mmddyy10.));
call symputx("reportdate",put(intnx('day',today(),-1),mmddyyd10.)); /*Formatted for humans.*/
run;
/****************************************************************************************************************/
/****************************************************************************************************************/
proc sql;
connect to saphana as pvcon (&PVCONSTR.);
create table test as select * from connection to pvcon
(
select distinct
substr(o30._BIC_BORGDNUM,1,6) as DoDAAC,
b.M_BIC_BPRCGRCD5 as PGC,
to_date(nullif(o30._BIC_BORDER_DT,'00000000'),'YYYYMMDD') as "Order Create Date",
o30._BIC_BBSTNK as "Customer PO Number",
o30._BIC_BORGDNUM as "Original Doc Num",
o30._BIC_BSUFX_CD as SFX,
b.M_BIC_B_NIIN_DESC_SH as Nomenclature,
b.M_BIC_B_NSN as NSN,
sum(o30.CONF_QTY) as Quantity,
o30._BIC_BPRI_CD as "Pri Code",
o30.BASE_UOM as "U/I",
o30._BIC_BSUPPADR as "Supp Address",
b.M_EANUPC as UPC,
o30._BIC_BDMDPLANT as RIC,
o30._BIC_BSUPP_ST as "Status",
coalesce(to_date(nullif(o30.CH_ON,'00000000'),'YYYYMMDD'),to_date(nullif(o30.CREATEDON,'00000000'),'YYYYMMDD')) as "Status Date",
sum(o30.NET_VALUE) as Price
from "_SYS_BIC"."EDW.SQL/CV_OM_BOFHO30" o30
left join "_SYS_BIC"."EDW.SQL/CV_CS_MATERIAL" b
on o30.MATERIAL= b.M_MATERIAL
left join "_SYS_BIC"."EDW.SQL/CV_CS_ITEM_DETAIL" id
on o30.MATERIAL = id.MATERIAL
left join "_SYS_BIC"."EDW.SQL/CV_CS_PROFIT_CTR" pc
on b.M_PROFIT_CTR = pc.PC_PROFIT_CTR
where pc.PC_SUPPLY_CHAIN_DESC ='C&T'
and o30._BIC_BSUPP_ST not in (/*'SS',*/'')
and substr(o30._BIC_BORGDNUM,1,6) in ('Z47912')
and o30._BIC_BEARCDTE='00000000'
/* and o30._BIC_BSHIP_QTY='0' */
group by substr(o30._BIC_BORGDNUM,1,6), b.M_BIC_BPRCGRCD5, o30._BIC_BORGDNUM, o30._BIC_BSUFX_CD, b.M_BIC_B_NIIN_DESC_SH,
b.M_BIC_B_NSN, o30._BIC_BPRI_CD, o30.BASE_UOM, o30._BIC_BSUPPADR, b.M_EANUPC, o30._BIC_BDMDPLANT, o30._BIC_BSUPP_ST,
coalesce(to_date(nullif(o30.CH_ON,'00000000'),'YYYYMMDD'),to_date(nullif(o30.CREATEDON,'00000000'),'YYYYMMDD')),
to_date(nullif(o30._BIC_BORDER_DT,'00000000'),'YYYYMMDD'),
o30._BIC_BBSTNK
);
disconnect from pvcon;
quit;
PROC EXPORT DATA=test (where=(Status not in ('B4','BQ','BR','BS','BF','C1','C2','C3','C4','C5','C6','C7','C8','CA','CB','CC','CD','CE','CF','CG','CH','CJ','CK','CL','CM','CN','CP','CQ','CR',
'CS','CT','CU','CV','CW','CX','CY','CZ','D1','D2','D3','D4','D5','D6','D8','CA','DB','DE','DF','DJ','DN','DP','DQ','DR','DY','PC','PG','PH','PJ')))
OUTFILE="&exptOutPath./&outfile."
DBMS=xlsx REPLACE;
SHEET="Open";
RUN;
PROC EXPORT DATA=test (where=(Status in ('B4','BQ','BR','BS','BF','C1','C2','C3','C4','C5','C6','C7','C8','CA','CB','CC','CD','CE','CF','CG','CH','CJ','CK','CL','CM','CN','CP','CQ','CR',
'CS','CT','CU','CV','CW','CX','CY','CZ','D1','D2','D3','D4','D5','D6','D8','CA','DB','DE','DF','DJ','DN','DP','DQ','DR','DY','PC','PG','PH','PJ')))
OUTFILE="&exptOutPath./&outfile."
DBMS=xlsx REPLACE;
SHEET="Cancelled";
RUN;
PROC EXPORT DATA=test (where=(Status in ('SS')))
OUTFILE="&exptOutPath./&outfile."
DBMS=xlsx REPLACE;
SHEET="SS";
RUN;
filename mymail email
to=(
"NAME1 <EMAIL ADDRESS>"
)
CC=(
"NAME2 <EMAIL ADDRESS>"
)
from=('Trp Spt RR&A Data Requests <TrpSptRR-ADataRequests@org.com>')
subject="XXX Report &reportdate."
attach=( "&exptOutPath/&outfile" CONTENT_TYPE="application/xlsx")
;
data _null_;
file mymail;
put 'Dear All,';
put ' ';
put "Please see the attached XXX report with data as of &reportdate..";
put ' ';
put 'If you have any questions or concerns, please feel free to contact me.';
put 'I would be happy to assist any way that I can.';
put ' ';
put 'Thank you,';
put 'XXX';
put ' ';
put 'This email was sent from the SAS Grid Server.';
put "<&_SASPROGRAMFILE.>";
run;
