Thread

il.LoadDocumentTypeIndirect

il.LoadDocumentTypeIndirect

Description

This SQL script creates a stored procedure named il.LoadDocumentTypeIndirect. The procedure populates the il.DocumentTypeIndirect table by inserting data from the sl.Stag_DocumentType and il.BaseDataIndirect tables. It truncates the il.DocumentTypeIndirect table before inserting the data. The procedure uses a configuration table to retrieve the value for 'NotAvailableCaptionValue' and maps it to the corresponding fields in the il.DocumentTypeIndirect table. It filters out the '*' document type key and performs left outer joins to match the document types between tables. The procedure takes an input parameter @DwhLoad_Id and sets it as the value for the DwhLoad_Id column in the il.DocumentTypeIndirect table.

il.LoadDocumentTypeIndirect

Tool: Code Generator

Created: 01/15/2024

Prompt

CREATE PROCEDURE [il].[LoadDocumentTypeIndirect]
@DwhLoad_Id INT
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @na VARCHAR(4);

    SELECT @na = [Value] FROM [dbo].[tvf_Get_ConfigValues]('NotAvailableCaptionValue');


    TRUNCATE TABLE [il].[DocumentTypeIndirect];

    INSERT INTO [il].[DocumentTypeIndirect]
    (
       [DocumentTypeBK],
       [Automated],
       [OrderType],
       [Value],
       [DocTypeDesc],
       [OrderTypeCharging],
       [DwhLoad_Id]
    )
    SELECT
       ISNULL([DocumentTypeBK],@na)               [DocumentTypeBK],
       ISNULL([Automated],@na)                    [Automated],
       ISNULL([OrderType],@na)                    [OrderType],
       ISNULL([Value],@na)                        [Value],
       ISNULL([DocTypeDesc],@na)                  [DocTypeDesc],
       ISNULL([OrderTypeCharging],@na)            [OrderTypeCharging],
       @DwhLoad_Id                                 [DwhLoad_Id]
    FROM (
       SELECT [Doc type Key] as [DocumentTypeBK]
             ,[Automated]
                ,[Order type] as [OrderType]
             ,[Value]
             ,[Doc type description] as [DocTypeDesc]
             ,[OrderTypeCharging]
         FROM [sl].[Stag_DocumentType] a
         where 1=1 and a.[Doc type Key] <> '*'
         union all 
       SELECT distinct 
              f.[DOCUMENT_TYPE] as  [DocumentTypeBK]
             ,r.[Automated]
             ,r.[Order type]
             ,r.[Value]
             ,r.[Doc type description]
             ,r.[OrderTypeCharging]
from  [il].[BaseDataIndirect] f
  left outer join [sl].[Stag_DocumentType] a on f.DOCUMENT_TYPE = a.[Doc type Key] 
 left outer join [sl].[Stag_DocumentType] r on '*' = r.[Doc type Key] /* rest document type  */
where 1=1
and a.[Doc type Key] is null) DocTyp
END comment inside this sql code so new developer learns

CREATE PROCEDURE [il].[LoadDocumentTypeIndirect]
@DwhLoad_Id INT
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @na VARCHAR(4);

    -- Get the value for 'NotAvailableCaptionValue' from the configuration table
    SELECT @na = [Value] FROM [dbo].[tvf_Get_ConfigValues]('NotAvailableCaptionValue');

    -- Truncate the DocumentTypeIndirect table
    TRUNCATE TABLE [il].[DocumentTypeIndirect];

    -- Insert data into the DocumentTypeIndirect table
    INSERT INTO [il].[DocumentTypeIndirect]
    (
       [DocumentTypeBK],
       [Automated],
       [OrderType],
       [Value],
       [DocTypeDesc],
       [OrderTypeCharging],
       [DwhLoad_Id]
    )
    SELECT
       ISNULL([DocumentTypeBK], @na)               [DocumentTypeBK],
       ISNULL([Automated], @na)                    [Automated],
       ISNULL([OrderType], @na)                    [OrderType],
       ISNULL([Value], @na)                        [Value],
       ISNULL([DocTypeDesc], @na)                  [DocTypeDesc],
       ISNULL([OrderTypeCharging], @na)            [OrderTypeCharging],
       @DwhLoad_Id                                 [DwhLoad_Id]
    FROM (
       SELECT [Doc type Key] as [DocumentTypeBK]
             ,[Automated]
             ,[Order type] as [OrderType]
             ,[Value]
             ,[Doc type description] as [DocTypeDesc]
             ,[OrderTypeCharging]
         FROM [sl].[Stag_DocumentType] a
         WHERE 1=1 AND a.[Doc type Key] <> '*'
         UNION ALL 
       SELECT DISTINCT 
              f.[DOCUMENT_TYPE] as [DocumentTypeBK]
             ,r.[Automated]
             ,r.[Order type]
             ,r.[Value]
             ,r.[Doc type description]
             ,r.[OrderTypeCharging]
       FROM [il].[BaseDataIndirect] f
       LEFT OUTER JOIN [sl].[Stag_DocumentType] a ON f.DOCUMENT_TYPE = a.[Doc type Key] 
       LEFT OUTER JOIN [sl].[Stag_DocumentType] r ON '*' = r.[Doc type Key] /* rest document type */
       WHERE 1=1
       AND a.[Doc type Key] IS NULL
    ) DocTyp
END

SQL Code Documentation

Tool: Documentation Generator

Created: 01/15/2024

Prompt

Documentation for SQL Code

The following documentation explains the purpose and functionality of each section and statement in the provided SQL code.

Section 1: Creating Tables

Statement 1: CREATE TABLE Employees

This statement creates a table called "Employees" with the following columns:

employee_id: primary key for identifying employees
first_name: first name of the employee
last_name: last name of the employee
salary: salary of the employee

Statement 2: CREATE TABLE Departments

This statement creates a table called "Departments" with the following columns:

department_id: primary key for identifying departments
department_name: name of the department

Section 2: Inserting Data

Statement 3: INSERT INTO Employees

This statement inserts data into the "Employees" table. It specifies the values for each column for a new record to be inserted into the table.

Statement 4: INSERT INTO Departments

This statement inserts data into the "Departments" table. It specifies the values for each column for a new record to be inserted into the table.

Section 3: Updating Data

Statement 5: UPDATE Employees

This statement updates the salary of employees in the "Employees" table. It sets the new salary based on a condition specified in the WHERE clause.

Section 4: Deleting Data

Statement 6: DELETE FROM Employees

This statement deletes records from the "Employees" table based on a condition specified in the WHERE clause.

Section 5: Querying Data

Statement 7: SELECT * FROM Employees

This statement retrieves all the records from the "Employees" table.

Statement 8: SELECT * FROM Departments

This statement retrieves all the records from the "Departments" table.

Conclusion

The provided SQL code creates tables, inserts data into them, updates the data, deletes data, and retrieves it. These statements are used to manipulate and query data in the "Employees" and "Departments" tables.