Hi all,
I want to insert incremental load in the given script insert and updates only.
this is the data layer ..can any one help me with this script as we are using the subroutine to fetch data from the database?
//------------------------------------------------------------------------------------------------------------------------
// FACT (Specific part of load script used for loading data, like for instance the creation of a dimension/fact table)
//------------------------------------------------------------------------------------------------------------------------
SUB Fact_ContractLines
CALL LogMessage ('Fact: Extracting Fact Lines (Contract, Material and Equipment Fact Data)')
ContractLines:
LOAD *;
SQL SELECT
TRIM(LEADING '0' FROM COALESCE(Sold2SCBP.PARTNER_FK_SAPACCOUNT_ID,Sold2SCHBP.PARTNER_FK_SAPACCOUNT_ID)) AS SoldToCustomer_Id,
TRIM(LEADING '0' FROM COALESCE(Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID,Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID)) AS ContractShipToCustomer_Id,
TRIM(LEADING '0' FROM COALESCE(Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID,Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID,E.Ship_To_Business_Partner_Code)) AS ShipToCustomer_Id,
TRIM(LEADING '0' FROM SC.CONTRACT_NUMBER) AS Contract_Id,
SC.START_DATE as ContractStartDate,
SC.END_DATE as ContractEndDate,
CAST(SC.SALES_AREA AS CHAR(4)) as SalesOrg,
SC.SALES_GROUP as SalesGroup,
SC.SALES_AREA as SalesArea_Id,
SC.PURCHASE_ORDER as CustPONumber_Id,
TRIM(LEADING '0' FROM CL.CONTRACT_LINE_ITEM_NUMBER) AS ContractLine_Id,
CL.LINE_ITEM as ContractLine,
CL.DESCRIPTION as ContractLineDescription,
CL.START_DATE AS ContractLineStartDate,
CL.END_DATE AS ContractLineEndDate,
CL.PROFIT_CENTER AS ProfitCenter_Id,
CL.MATERIAL_ID as AgreementMaterial,
CL.NET_VALUE as ContractLineNetValue,
CL.NET_PRICE as ContractLineNetPrice,
CL.CURRENCY_CODE as ContractCurrency,
CASE
WHEN CL.START_DATE > CURRENT_DATE THEN 'Pending'
WHEN CL.START_DATE<=CURRENT_DATE AND CL.END_DATE>=CURRENT_DATE THEN 'Active'
WHEN CL.END_DATE<CURRENT_DATE THEN 'Expired'
ELSE 'Error'
END as ContractStatus,
TRIM(LEADING '0' FROM E.Equipment_Code) AS Equipment_Id,
E.Technical_Start_Date AS EquipmentStartDate,
E.Equipment_Category_Code as EquipmentCategoryCode,
TRIM(LEADING '0' FROM E.Serial_Number) as SerialNumber,
TRIM(LEADING '0' FROM E.Hardware_Material_Code) as Hardware_Material_Code,
E.Hardware_Serial_Number as Hardware_Serial_Number,
E.Hardware_Version as Hardware_Version,
TRIM(LEADING '0' FROM E.Product_Code) as SystemCode,
M.Description as SystemCodeDescription,
TRIM(LEADING '0' FROM CL.CONTRACT_LINE_ITEM_NUMBER) || '|' || TRIM(LEADING '0' FROM CIP.EQUIPMENT_ID) AS Upgrade_Id,
TRIM(LEADING '0' FROM E.Configurable_Material_Code) AS CommercialMaterial,
CnfM.Description AS CommercialMaterialDescription,
CnfM.End_Of_Life_Material AS Material_EOLDate,
CASE WHEN CnfM.End_Of_Life_Material IS NULL or CnfM.End_Of_Life_Material > CURRENT_DATE THEN 'N' ELSE 'Y' END AS EOLReached,
CnfM.SLCP_Material_Identifier_Code AS SLCPMaterialIdentifier,
CASE
WHEN SLCPMaterialIdentifier IS NULL or SLCPMaterialIdentifier in ('U','N') THEN 'Non-SLCP'
WHEN SLCPMaterialIdentifier in ('B','H','S') THEN 'SLCP'
ELSE NULL
END AS SLCPType,
CASE
WHEN SLCPMaterialIdentifier IS NULL or SLCPMaterialIdentifier in ('U','N') THEN 0
WHEN SLCPMaterialIdentifier in ('B','H','S') THEN 1
ELSE NULL
END AS isSLCP,
CASE
WHEN SLCPMaterialIdentifier IS NULL or SLCPMaterialIdentifier in ('U','N') THEN 1
WHEN SLCPMaterialIdentifier in ('B','H','S') THEN 0
ELSE NULL
END AS isNonSLCP,
E.SLCP_SW_Version AS SLCP_SWVersion,
E.SLCP_Changes_Performed AS SLCP_ChangesPerformed,
E.Remaining_SLCP_Changes AS SLCP_Changes,
SMAInst.SUPPORT_LEVEL_FORMATTED AS NumberOfInstallationsContract,
CASE
WHEN CL.MATERIAL_ID in ($(PCMSPortfolio)) THEN 1
ELSE 0
END AS isPCMS,
CASE
WHEN CL.MATERIAL_ID in ($(HIPortfolio)) THEN 1
ELSE 0
END
AS isHI,
CASE
WHEN M.Is_SMA_Upgrade_Eligible = 'Y' THEN 1 ELSE 0
END AS isSMA_Upgrade_Eligible,
CL.BASE_CONTRACT_ID as Base_Contract_ID,
CL.LINE_CATEGORY as Line_Category
FROM P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT AS SC
INNER JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_LINE AS CL ON SC.CONTRACT_NUMBER = CL.CUSTOMERSERVICECONTRACT_NUMBER
INNER JOIN P0_IM_DL_GCS_V.C_CSA_COVERED_INSTALLED_PRODUCT AS CIP ON CL.CONTRACT_LINE_ITEM_NUMBER = CIP.CUSTOMER_SERVICE_CONTRACT_LINE AND CIP.IS_ACTIVE_FLAG='Y'
LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Sold2SCBP ON CL.CONTRACT_LINE_ITEM_NUMBER = Sold2SCBP.CONTRACT_LINE_ITEM_NUMBER AND Sold2SCBP.FUNCTION_FK_FUNCTION_CODE='AG'
LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Ship2SCBP ON CL.CONTRACT_LINE_ITEM_NUMBER = Ship2SCBP.CONTRACT_LINE_ITEM_NUMBER AND Ship2SCBP.FUNCTION_FK_FUNCTION_CODE='WE'
LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Sold2SCHBP ON CL.CUSTOMERSERVICECONTRACT_NUMBER = Sold2SCHBP.CUSTOMERSERVICECONTRACT_FK_NUMBER AND Sold2SCHBP.FUNCTION_FK_FUNCTION_CODE='AG' AND Sold2SCHBP.CUSTOMERSERVICECONTRACTLINE_FK_LINE_ITEM=0
LEFT JOIN P0_IM_DL_GCS_V.C_CSA_CUSTOMER_SERVICE_CONTRACT_PARTNERS as Ship2SCHBP ON CL.CUSTOMERSERVICECONTRACT_NUMBER = Ship2SCHBP.CUSTOMERSERVICECONTRACT_FK_NUMBER AND Ship2SCHBP.FUNCTION_FK_FUNCTION_CODE='WE' AND Ship2SCHBP.CUSTOMERSERVICECONTRACTLINE_FK_LINE_ITEM=0
-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Sold2BP ON Sold2SCBP.PARTNER_FK_SAPACCOUNT_ID = Sold2BP.SAPACCOUNT_ID
-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Ship2BP ON Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID = Ship2BP.SAPACCOUNT_ID
-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Sold2CBP ON Sold2SCHBP.PARTNER_FK_SAPACCOUNT_ID = Sold2CBP.SAPACCOUNT_ID
-- LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS Ship2CBP ON Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID = Ship2CBP.SAPACCOUNT_ID
-- LEFT JOIN P0_IM_DL_GCS_V.C_PIB_PROXY_FOR_CUSTOMER_SALES_AREA AS Sold2SA ON COALESCE(Sold2SCBP.PARTNER_FK_SAPACCOUNT_ID,Sold2SCHBP.PARTNER_FK_SAPACCOUNT_ID) = Sold2SA.CUSTOMER_FK_BUSINESS_SAP_ACCOUNT_ID AND SC.SALES_AREA=Sold2SA.SALES_AREA_ID
FULL OUTER JOIN P0_IM_DL_GCS_V.D_Equipment AS E ON CIP.EQUIPMENT_ID=E.Equipment_Code
LEFT JOIN P0_IM_DL_GCS_V.D_Material AS M ON E.Product_Code_With_Zero=M.Material_Code_With_Zero
LEFT JOIN P0_IM_DL_GCS_V.C_CSA_COVERED_SOFTWARE_SUPPORT AS SMAInst ON CL.CONTRACT_LINE_ITEM_NUMBER = SMAInst.CONTRACT_ID_FK AND SMAInst.CHARACTERISTIC_NAME='SMA_SW_INSTALLATIONS' AND SMAInst.CORE_CHANGE_DATE=(SEL Max(CORE_CHANGE_DATE) FROM P0_IM_DL_GCS_V.C_CSA_COVERED_SOFTWARE_SUPPORT AS TP WHERE TP.CONTRACT_ID_FK=CL.CONTRACT_LINE_ITEM_NUMBER AND TP.CHARACTERISTIC_NAME='SMA_SW_INSTALLATIONS')
LEFT JOIN P0_IM_DL_GCS_V.C_PROXY_FOR_BUSINESS_PARTNER AS EquiShip2BP ON E.Ship_To_Business_Partner_Code = EquiShip2BP.SAPACCOUNT_ID
LEFT JOIN P0_IM_DL_GCS_V.D_Material AS CnfM ON E.Configurable_Material_Code=CnfM.Material_Code
WHERE (CL.MATERIAL_ID in ($(PCMSPortfolio),$(HIPortfolio)) OR M.Is_SMA_Upgrade_Eligible = 'Y') AND COALESCE(Ship2SCBP.PARTNER_FK_SAPACCOUNT_ID,Ship2SCHBP.PARTNER_FK_SAPACCOUNT_ID,E.Ship_To_Business_Partner_Code) IS NOT NULL;
// SQL SELECT *
// FROM "P0_IM_DL_GCS_V"."C_CSA_CUSTOMER_SERVICE_CONTRACT_LINE"
// WHERE "MATERIAL_ID" IN ($(PCMSPortfolio),$(HIPortfolio));
CALL LogMessage ('Fact: Store Fact Lines (' & $(fTableRows(ContractLines)) & ' record(s))')
STORE ContractLines INTO $(vPath.QVD)\FACT.qvd;
DROP TABLE ContractLines;
END SUB
Regards
Manu
