Launch Batch Application JD Edwards

JD Edwards - Pass Value to UBE/Application in URL

Topic related to this:-
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=371210426635792&id=748331.1&_afrWindowMode=0&_adf.ctrl-state=ydq9vvmc_4




How to create and get access of JD Edwards application
http://oooooo.sssssss.com/jde/E1Menu.maf?OID=P0011_W0011A&User=rajeev&Password=xxxxxx&Environment=p&FormDSTmpl=|2|&FormDSData=|99999|&selectJPD910=*ALL

http://oracle.SSSSSSSS.com/jde/E1Menu.maf?OID=P0011_W0011_ZJDE0001A&FormDSTmpl=|1|&FormDSData=|G|

Create Excel Office addin using C# – Part 1 (Ribbon Designer)

https://sandeepfabiani.wordpress.com/2013/08/24/create-excel-office-addin-using-csharp-ribbon-designer/

Inventory Stock Report - JD Edwards UBE

Listing of ER for Report: Report : Expected Sales/GP by  Branch Plant * Item (R55COST02A)

***********************************************************************

     GLOBALS: Variables (Global)

***********************************************************************

     rpt_LedgType_LEDG21

     rpt_MCU_NO_Space

     rpt_MCU_AN8

     rpt_SalesReportingCode1

     rpt_SalesReportingCode2

     rpt_SalesReportingCode3

     rpt_SalesReportingCode4

     rpt_AlternateAddressKey_ALKY

     rpt_ForOwner_AN8

     rpt_NameGiven_GNNM

     rpt_NameSurname_SRNM

     rpt_HubMgr_CMC1

     rpt_ForHub_AN8

=======================================================================

     SECTION: Page Header [PAGE HEADER] (S3)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0001 RV VersionName = SL VersionName

=======================================================================

     SECTION: OnHand qty  by item/BP/SRP1/Company [GROUP SECTION] (S4)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Initialize Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0001 Set Selection Append Flag(<Yes>)

0002 Set User Selection(BC Cost Method (F4105)(LEDG), <Equal To>, "02", <And>)

0003 Set User Selection(BC Quantity on Hand - Primary units (F41021)(PQOH), <Not Equal To>, <Zero>, <And>)

0004 Set User Selection(BC Quantity on Hand - Primary units (F41021)(PQOH), <Not Equal To>, "0", <And>)

0005 Set User Selection(BC Quantity on Hand - Primary units (F41021)(PQOH), <Not Equal To>, <Blank>, <None>)

-----------------------------------------------------------------------

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0004 RV Extended Cost02 = [BC Quantity on Hand - Primary units (F41021)(PQOH)]*[BC Amount - Unit Cost (F4105)(UNCS)]

0005 RV CompanyFooter = BC Company (F0006)(CO)

0006 RV BusinessUnitFooter = BC Business Unit (F41021)(MCU)

0007 RV DescriptionCompressedBUFooter = BC Description - Compressed (F0006)(DC)

0008 RV Description_ITEM = substr([BC Description (F4101)(DSC1)],0,30)

0009 RV Location_ITEM = substr([BC Location (F41021)(LOCN)],0,20)

0010 VA rpt_LedgType_LEDG21 = "21"

0011 RV Unit Cost21 = 0.00

0012 F4105.Fetch Single

0013 RV Extended Cost21 = [BC Quantity on Hand - Primary units (F41021)(PQOH)]*[RV Unit Cost21]

0014 If RV Extended Cost21 is greater than <Zero> Or RV Extended Cost21 is greater than <Blank>

0015    RV GrossProfit = (([RV Extended Cost21]-[RV Extended Cost02])/[RV Extended Cost21])*100

0016 Else

0017    RV GrossProfit = 0.00

0018 End If

0020 ! Get UDC Description (Obsolete)

0021 ! Get UDC Description (Obsolete)

0022 ! Get UDC Description (Obsolete)

0023 ! Get UDC Description (Obsolete)

0025 RV SRP1 = BC Sales Catalog Section (F4101)(SRP1)

0026 RV SRP2 = BC Sub Section (F4101)(SRP2)

0027 RV SRP3 = BC Sales Category Code 3 (F4101)(SRP3)

0028 RV SRP4 = BC Sales Category Code 4 (F4101)(SRP4)

0029 RV SRP5 = BC Sales Category Code 5 (F4101)(SRP5)

0031 VA rpt_AlternateAddressKey_ALKY = rtrim(ltrim([BC Business Unit (F41021)(MCU)],' '),' ')

0034 VA rpt_NameGiven_GNNM = ""

0035 VA rpt_NameSurname_SRNM = ""

0036 VA rpt_ForHub_AN8 = ""

0037 VA rpt_HubMgr_CMC1 = ""

0038 F0101.Fetch Single

0040 F41001.Fetch Single

0041 F0111.Fetch Single

0043 RV Ownername = concat(concat(rtrim([VA rpt_NameGiven_GNNM],' '),"  "),[VA rpt_NameSurname_SRNM])

0044 VA rpt_NameGiven_GNNM = ""

0045 VA rpt_NameSurname_SRNM = ""

0047 F0101.Fetch Single

0049 F550354.Fetch Single

0050 F0111.Fetch Single

0051 RV HubMgrName = concat(concat(rtrim([VA rpt_NameGiven_GNNM],' '),"  "),[VA rpt_NameSurname_SRNM])

=======================================================================

     SECTION: On Business Unit [GROUP SECTION] (S5)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0002 VA rpt_MCU_NO_Space = ltrim([PC Business Unit (F0006)(MCU)],' ')

0003 F0101.Fetch Single

0004 F0111.Fetch Single

0005 RV FirstName = rtrim([RV FirstName],'  ')

0006 RV FirstName = concat(concat([RV FirstName],"  "),[RV LastName])

0008 If RV Total of Extended Cost21_OnBu is greater than <Zero> Or RV Total of Extended Cost21_OnBu is greater than <Blank>

0009    RV GrossProfit_OnBU = (([RV Total of Extended Cost21_OnBu]-[RV Total of Extended Cost02_OnBU])/[RV Total of Extended Cost21_OnBu])*100

0010 Else

0011    RV GrossProfit_OnBU = 0.00

0012 End If

=======================================================================

     SECTION: On Company [GROUP SECTION] (S8)

=======================================================================

     OBJECT:  SECTION

     EVENT:  Do Section

-----------------------------------------------------------------------

     OPT: Using Defaults

0001 If RV Total of Extended Cost21_OnComp is greater than <Zero> Or RV Total of Extended Cost21_OnComp is greater than <Blank>

0002    RV GrossProfit_OnComp = (([RV Total of Extended Cost21_OnComp]-[RV Total of Extended Cost02_OnComp])/[RV Total of Extended Cost21_OnComp])*100

0003 Else

0004    RV GrossProfit_OnComp = 0.00

0005 End If

Saturday October 14, 2017  11:22

Convert Row Into Column Dynamic completely PIVOT SQL Server

Need to create 12 month rolling report , user will enter year and Month , need to show report Month (YYYY-MM) on Top from F0911 , which is a detail table in JD Edwards , Amount is in row.
also Year and Month is in Row.


alter proc SP_ActualByAdminRollingAmt
--declare
@frmPN varchar(7) ='2017-08'

as



declare @frmdate datetime =@frmPN+'-01'
declare @TOdate datetime =DATEADD(M,-12, @frmPN+'-01')



--drop table #temp11
--drop table #temp12
--drop table #temp13



select
glaid,
glmcu,
glObj ,glsub,
glsblt,GLSBL,
glan8 ,
glfy,
glpn,
glaa
into #temp11
from mfdb.proddta.f0911 with(nolock)
where glfy in(17,16) and gllt='AA'  and left(globj,2) in('60','61')
 and gldgj>=[dbo].[JDT_GTJ](@TOdate) and gldgj<=[dbo].[JDT_GTJ](@frmdate)
--JDT_GTJ - function to convert calendar date to Julian date
and glco=1 and glaa<>0

--print [dbo].[JDT_GTJ](@frmdate)
--print [dbo].[JDT_GTJ](@TOdate)


select
glmcu,
mcdl01,
glObj Obj,
GmDL01 Obj_Desc,
ltrim(glsblt+ltrim(GLSBL)) Subledger,
DRDL01 Subledger_Desc,
glan8 VendorNo,
abalph VendorName,
glfy,
glpn,
sum(glaa)/100 glaa,
 case when glfy=17 then '2017-' + right('0'+cast(glpn as varchar(2)),2) else '2016-' + right('0'+cast(glpn as varchar(2)),2)  end  Period  into #temp12
 from  #temp11
left outer join proddta.f0101 on aban8=glan8
left outer join proddta.f0901 on gmaid=glaid
left outer join proddta.f0006 on mcmcu=glmcu
LEFT OUTER JOIN PRODCTL.F0005 ON DRSY='55' AND DRRT='SL'  AND LTRIM(DRKY)=LTRIM(GLSBL)
where left(glObj,2) in('60','61')
group by glObj,GmDL01,glsblt+ltrim(GLSBL) ,DRDL01,glan8,abalph,glfy,
glpn,glmcu,mcdl01



----main logic PIVOT

select distinct Period into #temp13 from  #temp12
DECLARE @cols AS NVARCHAR(MAX);

-- need to do following as amount filed which are in Row---

--set @cols='[2016-08],[2016-09],[2016-10],[2016-11],[2016-12], [2017-01],[2017-02],[2017-03],-----[2017-04],[2017-05],[2017-06],[2017-07],[2017-08],[2017-09],[2017-10],[2017-11],[2017-12]'



select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(Period)
                      FROM #temp13
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)')
                        , 1, 1, '');


DECLARE @query AS NVARCHAR(MAX);
set @query=
'SELECT * FROM(SELECT ltrim(glmcu) BU,mcdl01 BU_Desc,Obj, Obj_Desc, Subledger, Subledger_Desc, VendorNo, VendorName ,glaa,[Period]
  FROM #temp12 )t PIVOT (SUM([glaa])
  FOR [Period] IN ( ' + @cols + ' )' +
' ) AS pvt
order by 1,3,5'
execute(@query);