SAP B1 Question Bank -1

What is Docentry?
"Docentry" is unique number , this will generate automatically for all the individual documents. this starts from 1 will increase one by one for each new entry.

What is BaseEntry?
 Its a Docentry of the base document.

What is LineNum?
 Its a Serial number for "Row table" .

What is ObjType?
Its a door number of the each document. When ever we write a address we will write a door number same like you have to mention the Objtype.

Create Calendar for Between Dates

Create Calendar for Between Dates

 CREATE TABLE [dbo].[EA_CALENDAR](  
      [ID] [int] IDENTITY(1,1) NOT NULL,  
      [DATE] [date] NOT NULL,  
      [Idx] [int] NULL,  
      [YEAR] AS (datepart(year,[DATE])) PERSISTED,  
      [SEMESTER] AS (case when datepart(month,[DATE])<(7) then '1' else '2' end) PERSISTED NOT NULL,  
      [TRIMESTER] AS (case when datepart(month,[DATE])<(4) then '1' else case when datepart(month,[DATE])<(7) then '2' else case when datepart(month,[DATE])<(10) then '3' else '4' end end end) PERSISTED NOT NULL,  
      [MONTH] AS (case when len(CONVERT([varchar](2),datepart(month,[DATE])))=(1) then '0'+CONVERT([varchar](2),datepart(month,[DATE])) else CONVERT([varchar](2),datepart(month,[DATE])) end) PERSISTED,  
      [WEEK] AS (case when len(CONVERT([varchar](2),datepart(week,[DATE])))=(1) then '0'+CONVERT([varchar](2),datepart(week,[DATE])) else CONVERT([varchar](2),datepart(week,[DATE])) end),  
      [DAY] AS (case when len(CONVERT([varchar](2),datepart(day,[DATE])))=(1) then '0'+CONVERT([varchar](2),datepart(day,[DATE])) else CONVERT([varchar](2),datepart(day,[DATE])) end) PERSISTED,  
 PRIMARY KEY CLUSTERED   
 (  
      [ID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  

Run the Below Script

 CREATE PROCEDURE [dbo].[EA_AddData]  
 @START_DATE DATETIME,  
 @ENDDATE DATETIME  
 AS  
 WITH CTE_DATES AS  
 (  
 SELECT  
   @START_DATE DateValue UNION ALL SELECT  
   DateValue + 1  
 FROM CTE_DATES  
 WHERE DateValue + 1 <= @ENDDATE)  
 INSERT INTO EA_CALENDAR ([DATE],[idx])  
   SELECT  
    CAST(DateValue AS date),DENSE_RANK() OVER (ORDER BY DATEValue)  
   FROM CTE_DATES  
   OPTION (MAXRECURSION 0)  
 GO  

 EXEC [dbo].[EA_AddData] '',''  

Material not Received Below 7 days

Material not Received Below 7 days 

 CREAte PROCEDURE EA_ACC_ShipDate  
 @shipdate Datetime  
 as  
 SELECT distinct T0.[DocEntry], T0.[Project] AS 'STYLECODE', T0.[ItemCode], T0.[Dscription],T2.[ItmsGrpNam],T0.[Quantity], T0.[OpenQty] AS 'OPEN_QTY',T0.[Quantity]- T0.[OpenQty] as 'GRPOQTY', T0.[ShipDate] FROM POR1 T0 INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T2 ON T1.[ItmsGrpCod] = T2.[ItmsGrpCod] WHERE T0.[OpenQty]>='1'   
 and t0.shipdate<=dateadd(DAY,7,@shipdate) ORDER BY T0.[ShipDate]  

Add User Query in Testing Company [SAP Query Window]

 /* SELECT FROM [dbo].[POR1] T0 */  
 DECLARE @shipdate AS datetime  
 /* WHERE */  
 SET @shipdate = /* T0.ShipDate */ '[%0]'  
 EXEC EA_ACC_ShipDate @shipdate