Display Project Details in CFL

Dear All,
   Today, we will discuss about below points
1) Display Active Project
2) Display Project based on Posting Date.

Code Here,

  '*************************************************************************************************************

    'Type               : Friend  Sub

    'Name               : CardDetailsCondition

    'Parameter          :

    'Return Value       :

    'Author             : Lakshmi Narayanan

    'Created Dt         : 26-11-2016

    'Last Modified By   :                       

    'Modified Dt        :

    'Purpose            : This Method is used for Project Details CFL Condition

    '*************************************************************************************************************

    Friend Sub ProjectDetailsCondition(ByVal SBO_Application As SAPbouiCOM.Application, ByVal oForm As SAPbouiCOM.Form, ByVal cflIndex As Object, ByVal strPostingDate As Object)

        Dim ocfl As SAPbouiCOM.ChooseFromList = Nothing

        'Dim oCFLcollection As SAPbouiCOM.ChooseFromListCollection

        Dim ocondition As SAPbouiCOM.Condition = Nothing

        Dim oconditions As SAPbouiCOM.Conditions = Nothing

        'oCFLcollection = oForm.ChooseFromLists

        ocfl = SBO_Application.Forms.ActiveForm.ChooseFromLists.Item(cflIndex)

        Try

            oconditions = ocfl.GetConditions

            If 0 = oconditions.Count Then

                ocondition = oconditions.Add

                ocondition.Alias = "Active"

                ocondition.Operation = SAPbouiCOM.BoConditionOperation.co_EQUAL

                ocondition.CondVal = "Y"

                ocondition.Alias = "ValidTo"

                ocondition.Operation = SAPbouiCOM.BoConditionOperation.co_GRATER_THAN

                ocondition.CondVal = strPostingDate



                ocfl.SetConditions(oconditions)

            End If

        Catch ex As Exception



        Finally

            ocfl = Nothing

            ocondition = Nothing

            oconditions = Nothing



        End Try

       

    End Sub

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  

Validate BOM

Validate BOM Item Code,Project and Allow Procurement  for Sales Order

 IF @transaction_type IN (N'A', N'U') AND (@Object_type = N'17')  
 BEGIN  
 set @entry= (select distinct DOCENTRY from RDR1 where DocEntry=@list_of_cols_val_tab_del )  
 IF EXISTS (SELECT DISTINCT T1.ITEMCODE FROM [DBO].RDR1 T1 WHERE T1.LinePoPrss='Y' )  
 BEGIN  
  IF NOT Exists (SELECT DISTINCT T1.ItemCode FROm [DBO].OITT T0 INNER JOIN [DBO].RDR1 T1 ON T1.itemcode=T0.CODE where T1.DocEntry = @entry)  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Item Code BOM is missing !'  
 END  
 ELSE IF NOT EXISTS( SELECT DISTINCT T1.Project FROm [DBO].OITT T0 INNER JOIN [DBO].RDR1 T1 ON T1.itemcode=T0.CODE AND T1.PROJECT IS not NULL WHERE  T1.DocEntry = @entry)  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Project Code is missing !'  
 END  
 ELSE IF NOT EXISTS( SELECT DISTINCT T1.LinePoPrss FROm [DBO].OITT T0 INNER JOIN [DBO].RDR1 T1 ON T1.itemcode=T0.CODE AND  T1.LinePoPrss='Y'  WHERE  T1.DocEntry = @entry)  
 BEGIN  
 SELECT @Error = 5, @error_message = 'Allow Procurement is not checked !'  
 END  
 END  
 END