On the subject you gave the string to match as:
"reconcilia"
Which matched the following attached files:
/d/e/rd/RD_PaymentReconciliation.sql
/d/e/rd/RD20000713Reconciliation.zip
/d/e/rd/ReconciliationReport.sql
/d/e/rd/RD_ReconciliationReport.sql
As usual, please upload any changes you make.
Thank you.
if exists (select * from sysobjects where id =
object_id(N'[dbo].[RD_PaymentReconciliation]') and OBJECTPROPERTY(id, N'IsProcedure')
= 1)
drop procedure [dbo].[RD_PaymentReconciliation]
GO
/************************************************************************
* Name : RD_PaymentReconciliation
* Description : Reconcile payments
*
* Command :
* Drop : drop procedure RD_PaymentReconciliation
* Change Log : 29/12/1999 ROB - Created
* : 24/01/2000 ROB - Implemented Public Amounts
* : 17/04/2000 ROB - Put Commosion % in comment
* : 20/04/2000 ROB - Abort if not all products
sold
************************************************************************/
CREATE PROCEDURE RD_PaymentReconciliation
(
@EventTypeKey SMALLINT,
@EventKey INT,
@CommissionPercent FLOAT = 0,
@CreateTransaction CHAR(1) = 'Y',
@DocumentExternalReference VARCHAR(20) = NULL,
@DocumentDate SMALLDATETIME = NULL
)
AS
CREATE TABLE #TainerCartons
(TainerKey int not null,
CartonCount float not null)
CREATE TABLE #ProductAmounts
(TainerKey int not null,
LineNumber smallint not null,
EventTypeKey smallint not null,
EventKey int not null,
EventAmountSequence tinyint not null,
AmountTypeKey char(3) not null,
CurrencyKey char(3) null,
ExchangeRate float null,
AmountLevel char(1) null,
ActualAmount float null,
TaxAmount float null,
ActualAmountPublic float null,
TaxAmountPublic float null)
DECLARE @EventAspectKey char(4)
DECLARE @TotalCartons float
DECLARE @TotalSales float
DECLARE @TotalCommission float
DECLARE @FinalPayment float
DECLARE @TotalSalesPublic float
DECLARE @TotalCommissionPublic float
DECLARE @FinalPaymentPublic float
DECLARE @EventTypeCommission char(3)
DECLARE @EventTypeSettlement char(3)
DECLARE @CRIndCommission smallint
DECLARE @CRIndSettlement smallint
DECLARE @EventAmountSequence tinyint
DECLARE @BaseCurrencyKey char(3)
DECLARE @PaymentKey int
-- not used yet
DECLARE @EventFilterCriteriaKey int
DECLARE @EventSelection tinyint
DECLARE @OrgTainerKey char(6)
DECLARE @FromDocumentDate smalldatetime
DECLARE @UptoDocumentDate smalldatetime
DECLARE @FromEntryDate smalldatetime
DECLARE @UptoEntryDate smalldatetime
DECLARE @CapturedBy varchar(32)
BEGIN
SET NOCOUNT ON
/* Parameter Validation */
SELECT DISTINCT
@EventAspectKey = EVTA.EventAspectKey
FROM Event EVNT
INNER JOIN EventTypeAspects EVTA
ON EVNT.EventTypeKey = EVTA.EventTypeKey
WHERE EVNT.EventTypeKey = @EventTypeKey
AND EVNT.EventKey = @EventKey
AND EVTA.EventAspectKey = 'IN'
IF @@ROWCOUNT = 0 BEGIN
RAISERROR 50000 'No such Intake Event found'
RETURN -1
END
IF NOT @CommissionPercent BETWEEN 0 AND 100 BEGIN
RAISERROR 50000 'Invalid commission percentage supplied'
RETURN -1
END
IF @CreateTransaction NOT IN ('N','Y') BEGIN
RAISERROR 50000 'Create Transaction must be [Y]es or [N]o'
RETURN -1
END
/* Get Event Amount Type for Calculated Commission [CCOM] */
SELECT @EventTypeCommission = SUBSTRING(ZZCP.CurrentValue,1,3),
@CRIndCommission = EVAT.CRIndicator
FROM ConfigParameterType ZZCP
INNER JOIN EventAmountType EVAT
ON SUBSTRING(ZZCP.CurrentValue,1,3) =
EVAT.AmountTypeKey
WHERE ConfigParameterTypeKey = 'CCOM'
IF @EventTypeCommission IS NULL BEGIN
RAISERROR 50000 'Amount Type for Calculated Commission [CCOM] undefined'
RETURN -1
END
/* Get Event Amount Type for Calculated Settlement [CSET] */
SELECT @EventTypeSettlement = SUBSTRING(ZZCP.CurrentValue,1,3),
@CRIndSettlement = EVAT.CRIndicator
FROM ConfigParameterType ZZCP
INNER JOIN EventAmountType EVAT
ON SUBSTRING(ZZCP.CurrentValue,1,3) =
EVAT.AmountTypeKey
WHERE ConfigParameterTypeKey = 'CSET'
IF @EventTypeCommission IS NULL BEGIN
RAISERROR 50000 'Amount Type for Calculated Settlement [CSET] undefined'
RETURN -1
END
SELECT @BaseCurrencyKey = ORGN.CurrencyKey
FROM ConfigParameterType ZZPT
INNER JOIN OrgTainer ORGN
ON SUBSTRING(ZZPT.CurrentValue,1,6) =
ORGN.OrgTainerKey
WHERE ZZPT.ConfigParameterTypeKey = 'USER'
IF @BaseCurrencyKey IS NULL BEGIN
RAISERROR 50000 'No indication of system user is available'
RETURN -1
END
/* Set Parameter initial values */
SELECT @EventSelection = 0,
@EventAmountSequence = 1,
@PaymentKey = 0
/* Make initial list of Tainers for this Event */
SELECT *
INTO #TainerList
FROM Tainer
WHERE EventTypeKey = @EventTypeKey
AND EventKey = @EventKey
IF (SELECT COUNT(*) FROM #TainerList) = 0 BEGIN
RAISERROR 50000 'No remaining Tainers for this Intake Event'
RETURN -1
END
/* Make initial list of Products for this Event */
SELECT PROD.*
INTO #ProductList
FROM #TainerList TAIN
INNER JOIN Product PROD
ON TAIN.TainerKey = PROD.TainerKey
IF (SELECT COUNT(*) FROM #ProductList) = 0 BEGIN
RAISERROR 50000 'No remaining Products for this Intake Event'
RETURN -1
END
SELECT @TotalCartons = SUM(InitialQuantity)
FROM #ProductList
INSERT #TainerCartons
SELECT TainerKey,SUM(InitialQuantity)
FROM #ProductList
GROUP BY TainerKey
/* Get a list of Event Level Amounts against these Tainers */
INSERT #ProductAmounts
(TainerKey,LineNumber,EventTypeKey,EventKey,EventAmountSequence,
AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
TaxAmount,ActualAmountPublic,TaxAmountPublic)
SELECT PROD.TainerKey,PROD.LineNumber,EVAM.EventTypeKey,EVAM.EventKey,
EVAM.EventAmountSequence,EVAM.AmountTypeKey,EVAM.CurrencyKey,
EVAM.ExchangeRate,'E',
EVAM.ActualAmount*(PROD.InitialQuantity/@TotalCartons),
EVAM.TaxAmount*(PROD.InitialQuantity/@TotalCartons),
EVAM.ActualAmountPublic*(PROD.InitialQuantity/@TotalCartons),
EVAM.TaxAmountPublic*(PROD.InitialQuantity/@TotalCartons)
FROM EventTypeAspects EVTA
INNER JOIN Event EVNT
ON EVTA.EventTypeKey = EVNT.EventTypeKey
INNER JOIN EventTainers EVTN
ON EVNT.EventTypeKey = EVTN.EventTypeKey
AND EVNT.EventKey = EVTN.EventKey
INNER JOIN #ProductList PROD
ON PROD.TainerKey = EVTN.TainerKey
INNER JOIN EventAmounts EVAM
ON EVNT.EventTypeKey = EVAM.EventTypeKey
AND EVNT.EventKey = EVAM.EventKey
WHERE EVTA.EventAspectKey = 'AE'
/* Get a list of Tainer Level Amounts against these Tainers */
INSERT #ProductAmounts
(TainerKey,LineNumber,EventTypeKey,EventKey,EventAmountSequence,
AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
TaxAmount,ActualAmountPublic,TaxAmountPublic)
SELECT PROD.TainerKey,PROD.LineNumber,EVAT.EventTypeKey,EVAt.EventKey,
EVAT.EventAmountSequence,EVAT.AmountTypeKey,EVAT.CurrencyKey,
EVAT.ExchangeRate,'T',
EVAT.ActualAmount*(PROD.InitialQuantity/TMTC.CartonCount),
EVAT.TaxAmount*(PROD.InitialQuantity/TMTC.CartonCount),
EVAT.ActualAmountPublic*(PROD.InitialQuantity/TMTC.CartonCount),
EVAT.TaxAmountPublic*(PROD.InitialQuantity/TMTC.CartonCount)
FROM EventTypeAspects EVTA
INNER JOIN Event EVNT
ON EVTA.EventTypeKey = EVNT.EventTypeKey
INNER JOIN EventTainerAmounts EVAT
ON EVNT.EventTypeKey = EVAT.EventTypeKey
AND EVNT.EventKey = EVAT.EventKey
INNER JOIN #ProductList PROD
ON PROD.TainerKey = EVAT.TainerKey
INNER JOIN #TainerCartons TMTC
ON PROD.TainerKey = TMTC.TainerKey
WHERE EVTA.EventAspectKey = 'AE'
/* Get a list of Product Level Amounts against these Tainers */
INSERT #ProductAmounts
(TainerKey,LineNumber,EventTypeKey,EventKey,EventAmountSequence,
AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
TaxAmount,ActualAmountPublic,TaxAmountPublic)
SELECT PROD.TainerKey,PROD.LineNumber,EVAP.EventTypeKey,EVAP.EventKey,
EVAP.EventAmountSequence,EVAP.AmountTypeKey,EVAP.CurrencyKey,
EVAP.ExchangeRate,'P',
EVAP.AmountPerUnit*PROD.InitialQuantity,
EVAP.TaxAmountPerUnit*PROD.InitialQuantity,
EVAP.AmountPerUnitPublic*PROD.InitialQuantity,
EVAP.TaxAmountPerUnitPublic*PROD.InitialQuantity
FROM EventTypeAspects EVTA
INNER JOIN Event EVNT
ON EVTA.EventTypeKey = EVNT.EventTypeKey
INNER JOIN EventProductAmounts EVAP
ON EVNT.EventTypeKey = EVAP.EventTypeKey
AND EVNT.EventKey = EVAP.EventKey
INNER JOIN #ProductList PROD
ON PROD.TainerKey = EVAP.TainerKey
AND PROD.LineNumber = EVAP.LineNumber
WHERE EVTA.EventAspectKey = 'AE'
/* Check that there is a Sale Event for every Tainer */
SELECT DISTINCT TainerKey
INTO #SoldTainers
FROM #ProductAmounts PRAM
INNER JOIN EventAmountType EVAT
ON PRAM.AmountTypeKey = EVAT.AmountTypeKey
WHERE EVAT.AmountCategory = 'S'
IF (SELECT COUNT(*)
FROM #TainerList TAIN
LEFT OUTER JOIN #SoldTainers SOLD
ON TAIN.TainerKey = SOLD.TainerKey
WHERE SOLD.TainerKey IS NULL) <> 0 BEGIN
RAISERROR 50000 'Not all Pallets for this Intake Event have been sold'
RETURN -1
END
/* Calculate transaction signs */
UPDATE #ProductAmounts
SET ActualAmount = (PRAM.ActualAmount * EVAT.CRIndicator)
* ExchangeRate,
TaxAmount = (PRAM.TaxAmount *
EVAT.CrIndicator) * ExchangeRate,
ActualAmountPublic = (PRAM.ActualAmountPublic * EVAT.CRIndicator)
* ExchangeRate,
TaxAmountPublic = (PRAM.TaxAmountPublic *
EVAT.CrIndicator) * ExchangeRate
FROM #ProductAmounts PRAM
INNER JOIN EventAmountType EVAT
ON PRAM.AmountTypeKey = EVAT.AmountTypeKey
/* Calculate Sales and Commissions */
SELECT @TotalSales = ISNULL(SUM(PRAM.ActualAmount),0),
@TotalCommission =
0-ISNULL(SUM(PRAM.ActualAmount)*(@CommissionPercent/100.00),0),
@TotalSalesPublic =
ISNULL(SUM(PRAM.ActualAmountPublic),0),
@TotalCommissionPublic =
0-ISNULL(SUM(PRAM.ActualAmountPublic)*(@CommissionPercent/100.00),0)
FROM #ProductAmounts PRAM
INNER JOIN EventAmountType EVAT
ON PRAM.AmountTypeKey = EVAT.AmountTypeKey
WHERE EVAT.AmountCategory = 'S'
SELECT @TotalCommission =
@TotalCommission - ISNULL(SUM(ActualAmount),0),
@TotalCommissionPublic =
@TotalCommissionPublic - ISNULL(SUM(ActualAmountPublic),0)
FROM #ProductAmounts
WHERE AmountTypeKey = @EventTypeCommission
SELECT @FinalPayment =
0-ISNULL(SUM(PRAM.ActualAmount),0)-@TotalCommission,
@FinalPaymentPublic =
0-ISNULL(SUM(PRAM.ActualAmountPublic),0)-@TotalCommissionPublic
FROM #ProductAmounts PRAM
-- SELECT @TotalSales, @EventTypeSettlement, @FinalPayment,
@EventTypeCommission, @TotalCommission
/* Select Result Set */
IF ISNULL(@CreateTransaction,'Y') = 'Y' BEGIN
IF @TotalCommission <> 0
OR @TotalCommissionPublic <> 0
OR @FinalPayment <> 0
OR @FinalPaymentPublic <> 0 BEGIN
BEGIN TRANSACTION
INSERT Event
(EventTypeKey,DocumentExternalReference,DocumentDate,Narrative,
CapturedBy,EntryDate,EventState)
VALUES
(90,@DocumentExternalReference,@DocumentDate,'Calculated Commission @ '+
STR(@CommissionPercent,5,2)+'%','Auto Generated',getdate(),0)
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RAISERROR 50000 'Could not create
transaction'
RETURN -1
END
SELECT @PaymentKey = @@IDENTITY
INSERT EventTainers
(EventTypeKey,EventKey,TainerKey)
SELECT DISTINCT 90,@PaymentKey,TainerKey
FROM #TainerList
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RAISERROR 50000 'Could not create
transaction tainer list'
RETURN -1
END
IF @TotalCommission <> 0
OR @TotalCommissionPublic <> 0 BEGIN
INSERT EventAmounts
(EventTypeKey,EventKey,EventAmountSequence,AmountTypeKey,CurrencyKey,
ExchangeRate,ActualAmount,TaxAmount,ActualAmountPublic,TaxAmountPublic,
Narrative,Status,AmountLevel)
VALUES
(90,@PaymentKey,@EventAmountSequence,@EventTypeCommission,@BaseCurrencyKey,1,
@TotalCommission*@CRIndCommission,0,
@TotalCommissionPublic*@CRIndCommission,0,'Calculated Commission @ '+
STR(@CommissionPercent,5,2)+'%',0,'E')
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RAISERROR 50000 'Could not
create Commission entry'
RETURN -1
END
SELECT @EventAmountSequence = 2
END
IF @FinalPayment <> 0
OR @FinalPaymentPublic <> 0 BEGIN
INSERT EventAmounts
(EventTypeKey,EventKey,EventAmountSequence,AmountTypeKey,CurrencyKey,
ExchangeRate,ActualAmount,TaxAmount,ActualAmountPublic,TaxAmountPublic,
Narrative,Status,AmountLevel)
VALUES
(90,@PaymentKey,@EventAmountSequence,@EventTypeSettlement,@BaseCurrencyKey,1,
@FinalPayment*@CRIndSettlement,0,
@FinalPaymentPublic*@CRIndSettlement,0,'Calculated Settlement',0,'E')
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RAISERROR 50000 'Could not
create Settlement entry'
RETURN -1
END
END
COMMIT TRANSACTION
END
END ELSE BEGIN
/* Insert Commission rows */
IF @TotalCommission <> 0 BEGIN
INSERT #ProductAmounts
(TainerKey,LineNumber,EventTypeKey,EventKey,EventAmountSequence,
AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
TaxAmount,ActualAmountPublic,TaxAmountPublic)
SELECT
PROD.TainerKey,PROD.LineNumber,90,0,1,@EventTypeCommission,'ZAR',1,'E',
@TotalCommission*(PROD.InitialQuantity/@TotalCartons),0,
@TotalCommissionPublic*(PROD.InitialQuantity/@TotalCartons),0
FROM #ProductList PROD
END
/* Insert Final Payment Rows */
IF @FinalPayment <> 0 BEGIN
INSERT #ProductAmounts
(TainerKey,LineNumber,EventTypeKey,EventKey,EventAmountSequence,
AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
TaxAmount,ActualAmountPublic,TaxAmountPublic)
SELECT
PROD.TainerKey,PROD.LineNumber,90,0,1,@EventTypeSettlement,'ZAR',1,'E',
@FinalPayment*(PROD.InitialQuantity/@TotalCartons),0,
@FinalPaymentPublic*(PROD.InitialQuantity/@TotalCartons),0
FROM #ProductList PROD
END
SELECT
TainerKey,LineNumber,EventTypeKey,EventKey,EventAmountSequence,
AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
TaxAmount,ActualAmountPublic,TaxAmountPublic
FROM #ProductAmounts
END
END
GO
GRANT EXECUTE ON RD_PaymentReconciliation TO Reporting,UserInterface
GO
RD20000713Reconciliation.zip
if exists (select * from sysobjects where id =
object_id(N'[dbo].[RD_ReconciliationReport]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[RD_ReconciliationReport]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
/************************************************************************
* Name : RD_ReconciliationReport
* Description : Get all reconciling amount entries for the events
* returned by RD_GetFilteredAmounts
* Command : RD_ReconciliationReport 1,'O','C','S','P'
* Drop : drop procedure RD_ReconciliationReport
* Change Log : 26/06/2000 ROB - Created (from RD_AmountReport)
* : 13/07/2000 ROB - Get Parent Tainer at time of Event
************************************************************************/
CREATE PROCEDURE RD_ReconciliationReport
(
@EventFilterCriteriaKey INT,
@AddressType CHAR(1) = 'P', -- [P]roducer, [O]wner,
[C]ustodian, [E]vent
@AmountCategory1 CHAR(1) = '?', -- [P]ayment, [C]ost, [S]ales
@AmountCategory2 CHAR(1) = '?', -- [P]ayment, [C]ost, [S]ales
@AmountCategory3 CHAR(1) = '?', -- [P]ayment, [C]ost, [S]ales
@EventTypeKey SMALLINT = NULL,
@EventKey INT = NULL,
@OrgTainerType CHAR(1) = NULL,-- [P]roducer, [C]ustodian,
[O]wner
@OrgTainerKey CHAR(6) = NULL,
@TainerKey INT = NULL
)
AS
CREATE TABLE #ReportEvent
(EventTypeKey smallint not null,
EventKey int not null,
OrgTainerKey char(6) null,
ParentEventTypeKey smallint null,
ParentEventKey int null,
DocumentExternalReference varchar(20) null,
DocumentDate smalldatetime not null,
Narrative varchar(50) null,
CapturedBy varchar(32) null,
EntryDate smalldatetime null,
EventState tinyint null
)
CREATE TABLE #Products
(TainerKey int NULL,
LineNumber smallint NULL,
ParentTainerKey int NULL
)
CREATE TABLE #ReconAmounts
(EventTypeKey smallint NULL,
EventKey int NULL,
TainerKey int NULL,
LineNumber smallint NULL,
QuantityOfCartons int
NULL,
AmountCategory char(1) NULL,
AmountTypeKey char(3)
NULL,
CurrencyKey char(3)
NULL,
ForexAmountPerCarton decimal(12,2) NULL,
LocalAmountPerCarton decimal(12,2) NULL,
ForexTaxAmountPerCarton decimal(12,2) NULL,
LocalTaxAmountPerCarton decimal(12,2) NULL
)
CREATE TABLE #TainerList
(TainerKey int)
CREATE TABLE #SubTainerCount
(TainerKey int,
SubTainerCount float
)
CREATE TABLE #EventSubTainerCount
(EventTypeKey smallint not null,
EventKey int not null,
SubTainerCount float
)
CREATE TABLE #PaymentWork
(EventTypeKey smallint null,
EventKey int null,
ControlEventTypeKey smallint null,
ControlEventKey int null,
TainerKey int null,
LineNumber smallint null,
EventAmountSequence tinyint null,
AmountLevel char(1) null,
NoOfSubtainers int null,
AmountTypeKey char(3) null,
CurrencyKey char(3) null,
ExchangeRate float null,
Amount float null,
TaxAmount float null,
AmountCategory char(1) null,
AmountTypeDescription varchar(40) null,
CommissionPercentage float null
)
CREATE TABLE #LastControlEvent
(TainerKey int,
ControlDocumentDate smalldatetime,
ControlExternalReference varchar(20) null)
CREATE TABLE #PaymentReport
(IntakeExternalReference varchar(20) null,
IntakeDocumentDate smalldatetime null,
ControlExternalReference varchar(20) null,
ControlDocumentDate smalldatetime null,
DocumentExternalReference varchar(20) null,
DocumentDate smalldatetime null,
ProducerOrgTainerKey char(6) null,
ProducerDescription varchar(40) null,
PostalAddressLine1 varchar(40) null,
PostalAddressLine2 varchar(40) null,
PostalAddressLine3 varchar(40) null,
PostalAddressLine4 varchar(40) null,
TargetMarketKey char(4) null,
TainerTypeKey char(3) null,
TainerKey int null,
TainerExternalReference varchar(40) null,
TainerDescription varchar(40) null,
PalletMark varchar(12) null,
ParentTainerKey int null,
LocTainerKey char(6) null,
OwnerOrgTainerKey char(6) null,
OwnerDescription varchar(40) null,
CustodianOrgTainerKey char(6) null,
CustodianDescription varchar(40) null,
CountryKey char(3) null,
TainerState tinyint null,
InspectionStateKey tinyint null,
InspectionStateDescription varchar(40) null,
MixedPalletIndicator char(1) null,
LineNumber smallint null,
CommodityKey char(2) null,
CommodityDescription varchar(40) null,
BrandKey char(6) null,
BrandDescription varchar(40) null,
GradeKey smallint null,
GradeDescription varchar(40) null,
VarietyKey char(6) null,
VarietyDescription varchar(40) null,
InventoryKey char(2) null,
InventoryDescription varchar(40) null,
SubTainerKey char(3) null,
CountTableKey smallint null,
CountValue varchar(10) null,
InitialQuantity int null,
CurrentQuantity int null,
PickingCode varchar(12) null,
PickingDate smalldatetime null,
PackingCode varchar(12) null,
PackingDate smalldatetime null,
Orchard varchar(20) null,
EventOrgTainerKey char(6) null,
EventDescription varchar(40) null,
EventAmountSequence tinyint null,
AmountLevel char(1) null,
AmountTypeKey char(3) null,
CurrencyKey char(3) null,
ExchangeRate float null,
ActualAmount float null,
TaxAmount float null,
AmountCategory char(1) null,
AmountTypeDescription varchar(40) null,
CurrencyDescription varchar(40) null,
Nettweight float null,
CommissionPercentage float null
)
DECLARE @CallStatus int
DECLARE @TotalSubTainers float
DECLARE @OwnerOrgTainerKey char(6),
@CustodianOrgTainerKey char(6),
@ProducerOrgTainerKey char(6)
DECLARE @EventTypeCommission char(3)
BEGIN
SET NOCOUNT ON
/* Parameter Validation */
IF @OrgTainerType = 'C' SELECT @CustodianOrgTainerKey = @OrgTainerKey
IF @OrgTainerType = 'O' SELECT @OwnerOrgTainerKey = @OrgTainerKey
IF @OrgTainerType = 'P' SELECT @ProducerOrgTainerKey = @OrgTainerKey
/* Get Event Amount Type for Calculated Commission [CCOM] */
SELECT @EventTypeCommission = SUBSTRING(ZZCP.CurrentValue,1,3)
FROM ConfigParameterType ZZCP
INNER JOIN EventAmountType EVAT
ON SUBSTRING(ZZCP.CurrentValue,1,3) =
EVAT.AmountTypeKey
WHERE ConfigParameterTypeKey = 'CCOM'
IF @EventTypeCommission IS NULL BEGIN
RAISERROR 50000 'Amount Type for Calculated Commission [CCOM] undefined'
RETURN -1
END
/* Get Data from Filter */
INSERT #ReportEvent
(EventTypeKey,EventKey,OrgTainerKey,ParentEventTypeKey,ParentEventKey,
DocumentExternalReference,DocumentDate,Narrative,CapturedBy,EntryDate,EventState)
EXECUTE @CallStatus =
RD_GetFilteredEvents
@EventFilterCriteriaKey,@EventTypeKey,@EventKey,@TainerKey
IF @CallStatus <> 0 RETURN -1
-- SELECT * FROM #ReportEvent
/* Create Product List from Filtered Events (Intake & Control Events only) */
INSERT #Products (TainerKey,LineNumber,ParentTainerKey)
SELECT DISTINCT
PROD.TainerKey,PROD.LineNumber,NULL
FROM #ReportEvent REVN
INNER JOIN Tainer TAIN
ON
REVN.EventTypeKey = TAIN.EventTypeKey
AND REVN.EventKey
= TAIN.EventKey
INNER JOIN Product PROD
ON
TAIN.TainerKey = PROD.TainerKey
UNION
SELECT DISTINCT
PROD.TainerKey,PROD.LineNumber,EVTN.TainerKeyReferenced
FROM #ReportEvent REVN
INNER JOIN ControlEvent
EVCT
ON
REVN.EventTypeKey = EVCT.EventTypeKey
AND REVN.EventKey
= EVCT.EventKey
INNER JOIN EventTainers EVTN
ON
REVN.EventTypeKey = EVTN.EventTypeKey
AND REVN.EventKey
= EVTN.EventKey
INNER JOIN Product PROD
ON
EVTN.TainerKey = PROD.TainerKey
-- SELECT * FROM #Products Order by TainerKey,LineNumber
/* Get all amounts for these Products */
INSERT #ReconAmounts
(EventTypeKey,EventKey,TainerKey,LineNumber,QuantityOfCartons,
AmountCategory,AmountTypeKey,CurrencyKey,ForexAmountPerCarton,
LocalAmountPerCarton,ForexTaxAmountPerCarton,LocalTaxAmountPerCarton)
EXECUTE GG_EventAmounts
-- SELECT '#ReconAmounts',* FROM #ReconAmounts
/* Accumulate Amounts */
INSERT #PaymentWork
(EventTypeKey,EventKey,TainerKey,LineNumber,EventAmountSequence,
AmountLevel,NoOfSubTainers,AmountTypeKey,CurrencyKey,ExchangeRate,Amount,TaxAmount,
AmountCategory,AmountTypeDescription)
SELECT RECA.EventTypeKey,RECA.EventKey,RECA.TainerKey,RECA.LineNumber,0,
NULL,RECA.QuantityOfCartons,RECA.AmountTypeKey,RECA.CurrencyKey,
CASE RECA.ForexAmountPerCarton WHEN 0 THEN 0
ELSE
RECA.LocalAmountPerCarton/RECA.ForexAmountPerCarton END,
RECA.QuantityOfCartons*RECA.ForexAmountPerCarton,
RECA.QuantityOfCartons*RECA.ForexTaxAmountPerCarton,
RECA.AmountCategory,EVAT.Description
FROM #ReconAmounts RECA
INNER JOIN EventAmountType EVAT
ON RECA.AmountTypeKey = EVAT.AmountTypeKey
WHERE RECA.AmountCategory IN
(@AmountCategory1,@AmountCategory2,@AmountCategory3)
/* Get latest Control Event Data for AmountCategory Sale */
INSERT #LastControlEvent
(TainerKey,ControlDocumentDate)
SELECT PAYW.TainerKey, MAX(EVNT.DocumentDate)
FROM #PaymentWork PAYW
INNER JOIN EventTainers EVTN
ON PAYW.TainerKey = EVTN.TainerKey
INNER JOIN ControlEvent EVCT
ON EVCT.EventTypeKey = EVTN.EventTypeKey
AND EVCT.EventKey = EVTN.EventKey
INNER JOIN Event EVNT
ON EVNT.EventTypeKey = EVTN.EventTypeKey
AND EVNT.EventKey = EVTN.EventKey
WHERE PAYW.AmountCategory = 'S'
GROUP BY PAYW.TainerKey
UPDATE #LastControlEvent
SET ControlExternalReference = EVNT.DocumentExternalreference
FROM #LastControlEvent LCEV
INNER JOIN EventTainers EVTN
ON LCEV.TainerKey = EVTN.TainerKey
INNER JOIN ControlEvent EVCT
ON EVCT.EventTypeKey = EVTN.EventTypeKey
AND EVCT.EventKey = EVTN.EventKey
INNER JOIN Event EVNT
ON EVNT.EventTypeKey = EVTN.EventTypeKey
AND EVNT.EventKey = EVTN.EventKey
AND EVNT.DocumentDate = LCEV.ControlDocumentDate
/* Drop all rows not for specified Tainer Key, if supplied */
/*
IF @TainerKey IS NOT NULL BEGIN
DELETE #PaymentWork
WHERE TainerKey <> @TainerKey
END
*/
/* Get all required attributes */
-- SELECT * FROM #PaymentWork
INSERT #PaymentReport
(DocumentExternalReference,DocumentDate,ProducerOrgTainerKey,TargetMarketKey,
TainerTypeKey,TainerKey,TainerExternalReference,TainerDescription,
PalletMark,ParentTainerKey,LocTainerKey,OwnerOrgTainerKey,CustodianOrgTainerKey,
CountryKey,TainerState,InspectionStateKey,MixedPalletIndicator,LineNumber,
BrandKey,GradeKey,VarietyKey,InventoryKey,SubTainerKey,CountTableKey,CountValue,
InitialQuantity,CurrentQuantity,PickingCode,PickingDate,PackingCode,PackingDate,Orchard,
EventOrgTainerKey,EventAmountSequence,AmountLevel,AmountTypeKey,CurrencyKey,ExchangeRate,
ActualAmount,TaxAmount,AmountCategory,AmountTypeDescription,CommissionPercentage)
SELECT
EVNT.DocumentExternalReference,EVNT.DocumentDate,PROD.ProducerOrgTainerKey,
TAIN.TargetMarketKey,TAIN.TainerTypeKey,TAIN.TainerKey,TAIN.TainerExternalReference,
TAIN.Description,TAIN.PalletMark,NULL,TAIN.LocTainerKey,
TAIN.OwnerOrgTainerKey,TAIN.CustodianOrgTainerKey,TAIN.CountryKey,
TAIN.TainerState,PROD.InspectionStateKey,TAIN.MixedPalletIndicator,PROD.LineNumber,
PROD.BrandKey,PROD.GradeKey,PROD.VarietyKey,PROD.InventoryKey,PROD.SubTainerKey,
PROD.CountTableKey,CountValue,PROD.InitialQuantity,PROD.CurrentQuantity,
PROD.PickingCode,PROD.PickingDate,PROD.PackingCode,PROD.PackingDate,PROD.Orchard,EVNT.OrgTainerKey,
PAYW.EventAmountSequence,PAYW.AmountLevel,PAYW.AmountTypeKey,PAYW.CurrencyKey,PAYW.ExchangeRate,
PAYW.Amount,PAYW.TaxAmount,PAYW.AmountCategory,PAYW.AmountTypeDescription,PAYW.CommissionPercentage
FROM #PaymentWork PAYW
INNER JOIN Event EVNT
ON PAYW.EventTypeKey = EVNT.EventTypeKey
AND PAYW.EventKey = EVNT.EventKey
INNER JOIN Tainer TAIN
ON PAYW.TainerKey = TAIN.TainerKey
INNER JOIN Product PROD
ON PAYW.TainerKey = PROD.TainerKey
AND PAYW.LineNumber = PROD.LineNumber
WHERE ISNULL(PROD.ProducerOrgTainerKey,SPACE(6)) BETWEEN
ISNULL(@ProducerOrgTainerKey,SPACE(6)) AND
ISNULL(@ProducerOrgTainerKey,'ZZZZZZ')
AND ISNULL(TAIN.OwnerOrgTainerKey,SPACE(6)) BETWEEN
ISNULL(@OwnerOrgTainerKey,SPACE(6)) AND
ISNULL(@OwnerOrgTainerKey,'ZZZZZZ')
AND ISNULL(TAIN.CustodianOrgTainerKey,SPACE(6)) BETWEEN
ISNULL(@CustodianOrgTainerKey,SPACE(6)) AND
ISNULL(@CustodianOrgTainerKey,'ZZZZZZ')
-- SELECT * FROM #PaymentReport
/* Update additional details */
UPDATE #PaymentReport
SET ProducerDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.ProducerOrgTainerKey
WHERE REPT.ProducerOrgTainerKey IS NOT NULL
UPDATE #PaymentReport
SET OwnerDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.OwnerOrgTainerKey
WHERE REPT.OwnerOrgTainerKey IS NOT NULL
UPDATE #PaymentReport
SET CustodianDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.CustodianOrgTainerKey
WHERE REPT.CustodianOrgTainerKey IS NOT NULL
UPDATE #PaymentReport
SET EventDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.EventOrgTainerKey
WHERE REPT.EventOrgTainerKey IS NOT NULL
IF @AddressType = 'P' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.ProducerOrgTainerKey
END
IF @AddressType = 'O' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.OwnerOrgTainerKey
END
IF @AddressType = 'C' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.CustodianOrgTainerKey
END
IF @AddressType = 'E' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.EventOrgTainerKey
END
UPDATE #PaymentReport
SET VarietyDescription = VRTY.Description,
CommodityKey = VRTY.CommodityKey,
CommodityDescription = CMDT.Description
FROM Variety VRTY
INNER JOIN #PaymentReport REPT
ON VRTY.VarietyKey = REPT.VarietyKey
INNER JOIN Commodity CMDT
ON VRTY.CommodityKey = CMDT.CommodityKey
UPDATE #PaymentReport
SET BrandDescription = BRND.Description
FROM Brand BRND
INNER JOIN #PaymentReport REPT
ON BRND.BrandKey = REPT.BrandKey
WHERE REPT.BrandKey IS NOT NULL
UPDATE #PaymentReport
SET GradeDescription = GRAD.Description
FROM Grade GRAD
INNER JOIN #PaymentReport REPT
ON GRAD.GradeKey = REPT.GradeKey
WHERE REPT.GradeKey IS NOT NULL
UPDATE #PaymentReport
SET InventoryDescription = INVT.Description
FROM Inventory INVT
INNER JOIN #PaymentReport REPT
ON INVT.InventoryKey = REPT.InventoryKey
WHERE REPT.InventoryKey IS NOT NULL
UPDATE #PaymentReport
SET IntakeExternalReference = EVNT.DocumentExternalReference,
IntakeDocumentDate = EVNT.DocumentDate
FROM Tainer TAIN
INNER JOIN #PaymentReport REPT
ON TAIN.TainerKey = REPT.TainerKey
INNER JOIN Event EVNT
ON TAIN.EventTypeKey = EVNT.EventTypeKey
AND TAIN.EventKey = EVNT.EventKey
UPDATE #PaymentReport
SET CurrencyDescription = CURR.Description
FROM Currency CURR
INNER JOIN #PaymentReport REPT
ON CURR.CurrencyKey = REPT.CurrencyKey
UPDATE #PaymentReport
SET Nettweight = SUBT.Nettweight
FROM SubTainer SUBT
INNER JOIN #PaymentReport REPT
ON SUBT.SubTainerKey = REPT.SubTainerKey
UPDATE #PaymentReport
SET ControlExternalReference = LCEV.ControlExternalReference,
ControlDocumentDate = LCEV.ControlDocumentDate
FROM #LastControlEvent LCEV
INNER JOIN #PaymentReport REPT
ON LCEV.TainerKey = REPT.TainerKey
WHERE REPT.AmountCategory = 'S'
UPDATE #PaymentReport
SET InspectionStateDescription = INSS.Description
FROM InspectionState INSS
INNER JOIN #PaymentReport REPT
ON INSS.InspectionStateKey =
REPT.InspectionStateKey
UPDATE #PaymentReport
SET ParentTainerKey = PROD.ParentTainerKey
FROM #Products PROD
INNER JOIN #PaymentReport REPT
ON PROD.TainerKey = REPT.TainerKey
AND PROD.LineNumber = REPT.LineNumber
WHERE PROD.TainerKey <> PROD.ParentTainerKey
/* Get Result Set */
/* Eric Saturday May 06, 2000 changed field order and order by */
SELECT
TainerExternalReference,AmountTypeKey,CurrencyKey,
ExchangeRate=CONVERT(DECIMAL(12,4),ExchangeRate),AmountLevel,ActualAmount,
VarietyKey,CurrentQuantity,MixedPalletIndicator,LineNumber,NettWeight,
PackingDate=CONVERT(VARCHAR(10),PackingDate,111),BrandKey,GradeKey,CountValue,
IntakeExternalReference,IntakeDocumentDate=CONVERT(VARCHAR(10),IntakeDocumentDate,111),
ControlExternalReference,ControlDocumentDate=CONVERT(VARCHAR(10),ControlDocumentDate,111),
DocumentExternalReference,DocumentDate=CONVERT(VARCHAR(10),DocumentDate,111),ProducerOrgTainerKey,
ProducerDescription,PostalAddressLine1,PostalAddressLine2,PostalAddressLine3,
PostalAddressLine4,TargetMarketKey,TainerTypeKey,TainerDescription,
PalletMark,ParentTainerKey,LocTainerKey,OwnerOrgTainerKey,OwnerDescription,CustodianOrgTainerKey,
CustodianDescription,CountryKey,TainerState,InspectionStateKey,InspectionStatedescription,
CommodityKey,CommodityDescription,BrandDescription,
GradeDescription,VarietyDescription,InventoryKey,InventoryDescription,
SubTainerKey,CountTableKey,InitialQuantity,PickingCode,PickingDate=CONVERT(VARCHAR(10),PickingDate,111),PackingCode,
Orchard,EventOrgTainerKey,EventDescription,
TaxAmount,AmountCategory,AmountTypeDescription,
AmountCategoryDescription=CASE AmountCategory
WHEN 'C' THEN 'Cost'
WHEN 'P' THEN 'Payment'
WHEN 'S' THEN 'Sale'
ELSE '????'
END,
CurrencyDescription, CommissionPercentage,EventAmountSequence
FROM #PaymentReport
REVERSE ORDER BY
TainerexternalReference,AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
VarietyKey,CurrentQuantity,MixedPalletIndicator,LineNumber,NettWeight,
PackingDate,BrandKey,GradeKey,CountValue,
IntakeExternalReference,IntakeDocumentDate,ProducerOrgTainerKey,DocumentExternalReference
END
GO
GRANT EXECUTE ON RD_ReconciliationReport TO Userinterface, Reporting
GO
if exists (select * from sysobjects where id =
object_id(N'[dbo].[RD_ReconciliationReport]') and OBJECTPROPERTY(id, N'IsProcedure') =
1)
drop procedure [dbo].[RD_ReconciliationReport]
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
/************************************************************************
* Name : RD_ReconciliationReport
* Description : Get all reconciling amount entries for the events
* returned by RD_GetFilteredAmounts
* Command : RD_ReconciliationReport 1,'O','C','S','P'
* Drop : drop procedure RD_ReconciliationReport
* Change Log : 26/06/2000 ROB - Created (from RD_AmountReport)
* : 18/07/2000 ROB - Add ParentTainerExternalReference
************************************************************************/
CREATE PROCEDURE RD_ReconciliationReport
(
@EventFilterCriteriaKey INT,
@AddressType CHAR(1) = 'P', -- [P]roducer, [O]wner,
[C]ustodian, [E]vent
@AmountCategory1 CHAR(1) = '?', -- [P]ayment, [C]ost, [S]ales
@AmountCategory2 CHAR(1) = '?', -- [P]ayment, [C]ost, [S]ales
@AmountCategory3 CHAR(1) = '?', -- [P]ayment, [C]ost, [S]ales
@EventTypeKey SMALLINT = NULL,
@EventKey INT = NULL,
@OrgTainerType CHAR(1) = NULL,-- [P]roducer, [C]ustodian,
[O]wner
@OrgTainerKey CHAR(6) = NULL,
@TainerKey INT = NULL
)
AS
CREATE TABLE #ReportEvent
(EventTypeKey smallint not null,
EventKey int not null,
OrgTainerKey char(6) null,
ParentEventTypeKey smallint null,
ParentEventKey int null,
DocumentExternalReference varchar(20) null,
DocumentDate smalldatetime not null,
Narrative varchar(50) null,
CapturedBy varchar(32) null,
EntryDate smalldatetime null,
EventState tinyint null
)
CREATE TABLE #Products
(TainerKey int NULL,
LineNumber smallint NULL,
ParentTainerKey int NULL
)
CREATE TABLE #ReconAmounts
(EventTypeKey smallint NULL,
EventKey int NULL,
TainerKey int NULL,
LineNumber smallint NULL,
QuantityOfCartons int
NULL,
AmountCategory char(1) NULL,
AmountTypeKey char(3)
NULL,
CurrencyKey char(3)
NULL,
ForexAmountPerCarton decimal(12,2) NULL,
LocalAmountPerCarton decimal(12,2) NULL,
ForexTaxAmountPerCarton decimal(12,2) NULL,
LocalTaxAmountPerCarton decimal(12,2) NULL
)
CREATE TABLE #TainerList
(TainerKey int)
CREATE TABLE #SubTainerCount
(TainerKey int,
SubTainerCount float
)
CREATE TABLE #EventSubTainerCount
(EventTypeKey smallint not null,
EventKey int not null,
SubTainerCount float
)
CREATE TABLE #PaymentWork
(EventTypeKey smallint null,
EventKey int null,
ControlEventTypeKey smallint null,
ControlEventKey int null,
TainerKey int null,
LineNumber smallint null,
EventAmountSequence tinyint null,
AmountLevel char(1) null,
NoOfSubtainers int null,
AmountTypeKey char(3) null,
CurrencyKey char(3) null,
ExchangeRate float null,
Amount float null,
TaxAmount float null,
AmountCategory char(1) null,
AmountTypeDescription varchar(40) null,
CommissionPercentage float null
)
CREATE TABLE #LastControlEvent
(TainerKey int,
ControlDocumentDate smalldatetime,
ControlExternalReference varchar(20) null)
CREATE TABLE #PaymentReport
(IntakeExternalReference varchar(20) null,
IntakeDocumentDate smalldatetime null,
ControlExternalReference varchar(20) null,
ControlDocumentDate smalldatetime null,
DocumentExternalReference varchar(20) null,
DocumentDate smalldatetime null,
ProducerOrgTainerKey char(6) null,
ProducerDescription varchar(40) null,
PostalAddressLine1 varchar(40) null,
PostalAddressLine2 varchar(40) null,
PostalAddressLine3 varchar(40) null,
PostalAddressLine4 varchar(40) null,
TargetMarketKey char(4) null,
TainerTypeKey char(3) null,
TainerKey int null,
TainerExternalReference varchar(40) null,
TainerDescription varchar(40) null,
PalletMark varchar(12) null,
ParentTainerKey int null,
ParentTainerExternalReference varchar(40) null,
LocTainerKey char(6) null,
OwnerOrgTainerKey char(6) null,
OwnerDescription varchar(40) null,
CustodianOrgTainerKey char(6) null,
CustodianDescription varchar(40) null,
CountryKey char(3) null,
TainerState tinyint null,
InspectionStateKey tinyint null,
InspectionStateDescription varchar(40) null,
MixedPalletIndicator char(1) null,
LineNumber smallint null,
CommodityKey char(2) null,
CommodityDescription varchar(40) null,
BrandKey char(6) null,
BrandDescription varchar(40) null,
GradeKey smallint null,
GradeDescription varchar(40) null,
VarietyKey char(6) null,
VarietyDescription varchar(40) null,
InventoryKey char(2) null,
InventoryDescription varchar(40) null,
SubTainerKey char(3) null,
CountTableKey smallint null,
CountValue varchar(10) null,
InitialQuantity int null,
CurrentQuantity int null,
PickingCode varchar(12) null,
PickingDate smalldatetime null,
PackingCode varchar(12) null,
PackingDate smalldatetime null,
Orchard varchar(20) null,
EventOrgTainerKey char(6) null,
EventDescription varchar(40) null,
EventAmountSequence tinyint null,
AmountLevel char(1) null,
AmountTypeKey char(3) null,
CurrencyKey char(3) null,
ExchangeRate float null,
ActualAmount float null,
TaxAmount float null,
AmountCategory char(1) null,
AmountTypeDescription varchar(40) null,
CurrencyDescription varchar(40) null,
Nettweight float null,
CommissionPercentage float null
)
DECLARE @CallStatus int
DECLARE @TotalSubTainers float
DECLARE @OwnerOrgTainerKey char(6),
@CustodianOrgTainerKey char(6),
@ProducerOrgTainerKey char(6)
DECLARE @EventTypeCommission char(3)
BEGIN
SET NOCOUNT ON
/* Parameter Validation */
IF @OrgTainerType = 'C' SELECT @CustodianOrgTainerKey = @OrgTainerKey
IF @OrgTainerType = 'O' SELECT @OwnerOrgTainerKey = @OrgTainerKey
IF @OrgTainerType = 'P' SELECT @ProducerOrgTainerKey = @OrgTainerKey
/* Get Event Amount Type for Calculated Commission [CCOM] */
SELECT @EventTypeCommission = SUBSTRING(ZZCP.CurrentValue,1,3)
FROM ConfigParameterType ZZCP
INNER JOIN EventAmountType EVAT
ON SUBSTRING(ZZCP.CurrentValue,1,3) =
EVAT.AmountTypeKey
WHERE ConfigParameterTypeKey = 'CCOM'
IF @EventTypeCommission IS NULL BEGIN
RAISERROR 50000 'Amount Type for Calculated Commission [CCOM] undefined'
RETURN -1
END
/* Get Data from Filter */
INSERT #ReportEvent
(EventTypeKey,EventKey,OrgTainerKey,ParentEventTypeKey,ParentEventKey,
DocumentExternalReference,DocumentDate,Narrative,CapturedBy,EntryDate,EventState)
EXECUTE @CallStatus =
RD_GetFilteredEvents
@EventFilterCriteriaKey,@EventTypeKey,@EventKey,@TainerKey
IF @CallStatus <> 0 RETURN -1
-- SELECT * FROM #ReportEvent
/* Create Product List from Filtered Events (Intake & Control Events only) */
INSERT #Products (TainerKey,LineNumber,ParentTainerKey)
SELECT DISTINCT
PROD.TainerKey,PROD.LineNumber,NULL
FROM #ReportEvent REVN
INNER JOIN Tainer TAIN
ON
REVN.EventTypeKey = TAIN.EventTypeKey
AND REVN.EventKey
= TAIN.EventKey
INNER JOIN Product PROD
ON
TAIN.TainerKey = PROD.TainerKey
UNION
SELECT DISTINCT
PROD.TainerKey,PROD.LineNumber,EVTN.TainerKeyReferenced
FROM #ReportEvent REVN
INNER JOIN ControlEvent
EVCT
ON
REVN.EventTypeKey = EVCT.EventTypeKey
AND REVN.EventKey
= EVCT.EventKey
INNER JOIN EventTainers EVTN
ON
REVN.EventTypeKey = EVTN.EventTypeKey
AND REVN.EventKey
= EVTN.EventKey
INNER JOIN Product PROD
ON
EVTN.TainerKey = PROD.TainerKey
-- SELECT * FROM #Products Order by TainerKey,LineNumber
/* Get all amounts for these Products */
INSERT #ReconAmounts
(EventTypeKey,EventKey,TainerKey,LineNumber,QuantityOfCartons,
AmountCategory,AmountTypeKey,CurrencyKey,ForexAmountPerCarton,
LocalAmountPerCarton,ForexTaxAmountPerCarton,LocalTaxAmountPerCarton)
EXECUTE GG_EventAmounts
-- SELECT '#ReconAmounts',* FROM #ReconAmounts
/* Accumulate Amounts */
INSERT #PaymentWork
(EventTypeKey,EventKey,TainerKey,LineNumber,EventAmountSequence,
AmountLevel,NoOfSubTainers,AmountTypeKey,CurrencyKey,ExchangeRate,Amount,TaxAmount,
AmountCategory,AmountTypeDescription)
SELECT RECA.EventTypeKey,RECA.EventKey,RECA.TainerKey,RECA.LineNumber,0,
NULL,RECA.QuantityOfCartons,RECA.AmountTypeKey,RECA.CurrencyKey,
CASE RECA.ForexAmountPerCarton WHEN 0 THEN 0
ELSE
RECA.LocalAmountPerCarton/RECA.ForexAmountPerCarton END,
RECA.QuantityOfCartons*RECA.ForexAmountPerCarton,
RECA.QuantityOfCartons*RECA.ForexTaxAmountPerCarton,
RECA.AmountCategory,EVAT.Description
FROM #ReconAmounts RECA
INNER JOIN EventAmountType EVAT
ON RECA.AmountTypeKey = EVAT.AmountTypeKey
WHERE RECA.AmountCategory IN
(@AmountCategory1,@AmountCategory2,@AmountCategory3)
/* Accumulate Amounts per Product */
/*
INSERT #PaymentWork
(EventTypeKey,EventKey,TainerKey,LineNumber,EventAmountSequence,
AmountLevel,NoOfSubTainers,AmountTypeKey,CurrencyKey,ExchangeRate,Amount,TaxAmount,
AmountCategory,AmountTypeDescription)
SELECT
REPT.EventTypeKey,REPT.EventKey,PROD.TainerKey,PROD.LineNumber,EVPA.EventAmountSequence,
'P',PROD.InitialQuantity,EVPA.AmountTypeKey,EVPA.CurrencyKey,EVPA.ExchangeRate,
EVAT.CRIndicator*(PROD.InitialQuantity*EVPA.AmountPerUnitPublic),
EVAT.CRIndicator*(PROD.InitialQuantity*EVPA.TaxAmountPerUnitPublic),
EVAT.AmountCategory,EVAT.Description
FROM #ReportEvent REPT
INNER JOIN EventProductAmounts EVPA
ON REPT.EventTypeKey = EVPA.EventTypeKey
AND REPT.EventKey = EVPA.EventKey
INNER JOIN Product PROD
ON EVPA.TainerKey = PROD.TainerKey
AND EVPA.LineNumber = PROD.LineNumber
INNER JOIN EventAmountType EVAT
ON EVPA.AmountTypeKey = EVAT.AmountTypeKey
WHERE EVAT.AmountCategory IN (@AmountCategory1,@AmountCategory2,@AmountCategory3)
*/
/* Calculate the number of SubTainers in each Tainer */
/*
INSERT #TainerList
SELECT DISTINCT TainerKey
FROM #ReportEvent REPT
INNER JOIN EventTainerAmounts EVTA
ON REPT.EventTypeKey = EVTA.EventTypeKey
AND REPT.EventKey = EVTA.EventKey
INSERT #SubTainerCount
(TainerKey,SubTainerCount)
SELECT PROD.TainerKey,SUM(PROD.InitialQuantity)
FROM #TainerList TAIN
INNER JOIN Product PROD
ON TAIN.TainerKey = PROD.TainerKey
GROUP BY PROD.TainerKey
*/
/* Accumulate Costs per Tainer */
/*
INSERT #PaymentWork
(EventTypeKey,EventKey,TainerKey,LineNumber,EventAmountSequence,
AmountLevel,NoOfSubTainers,AmountTypeKey,CurrencyKey,ExchangeRate,Amount,TaxAmount,
AmountCategory,AmountTypeDescription)
SELECT REPT.EventTypeKey,REPT.EventKey,PROD.TainerKey,PROD.LineNumber,
EVTA.EventAmountSequence,'T',
PROD.InitialQuantity,EVTA.AmountTypeKey,EVTA.CurrencyKey,EVTA.ExchangeRate,
EVAT.CRIndicator*(EVTA.ActualAmountPublic*(PROD.InitialQuantity/SUBT.SubTainerCount)),
EVAT.CRIndicator*(EVTA.TaxAmountPublic*(PROD.InitialQuantity/SUBT.SubTainerCount)),
EVAT.AmountCategory,EVAT.Description
FROM #ReportEvent REPT
INNER JOIN EventTainerAmounts EVTA
ON REPT.EventTypeKey = EVTA.EventTypeKey
AND REPT.EventKey = EVTA.EventKey
INNER JOIN Product PROD
ON EVTA.TainerKey = PROD.TainerKey
INNER JOIN #SubTainerCount SUBT
ON EVTA.TainerKey = SUBT.TainerKey
INNER JOIN EventAmountType EVAT
ON EVTA.AmountTypeKey = EVAT.AmountTypeKey
WHERE EVAT.AmountCategory IN
(@AmountCategory1,@AmountCategory2,@AmountCategory3)
*/
/* Calculate the number of SubTainers affected by each Event */
/*
INSERT #EventSubTainerCount
(EventTypeKey,EventKey,SubTainerCount)
SELECT REPT.EventTypeKey,REPT.EventKey,SUM(PROD.InitialQuantity)
FROM #ReportEvent REPT
INNER JOIN EventTainers EVTN
ON REPT.EventTypeKey = EVTN.EventTypeKey
AND REPT.EventKey = EVTN.EventKey
INNER JOIN Product PROD
ON EVTN.TainerKey = PROD.TainerKey
GROUP BY REPT.EventTypeKey,REPT.EventKey
*/
/* Accumulate Costs per Event */
/*
INSERT #PaymentWork
(EventTypeKey,EventKey,TainerKey,LineNumber,EventAmountSequence,
AmountLevel,NoOfSubTainers,AmountTypeKey,CurrencyKey,ExchangeRate,Amount,TaxAmount,
AmountCategory,AmountTypeDescription,CommissionPercentage)
SELECT REPT.EventTypeKey,REPT.EventKey,PROD.TainerKey,PROD.LineNumber,
EVAM.EventAmountSequence,'E',PROD.InitialQuantity,EVAM.AmountTypeKey,
EVAM.CurrencyKey,EVAM.ExchangeRate,
EVAT.CRIndicator*
EVAM.ActualAmountPublic*(PROD.InitialQuantity/SUBT.SubTainerCount),
EVAT.CRIndicator*
EVAM.TaxAmountPublic*(PROD.InitialQuantity/SUBT.SubTainerCount),
EVAT.AmountCategory,EVAT.Description,
CASE EVAM.AmountTypeKey WHEN @EventTypeCommission THEN
SUBSTRING(EVAM.Narrative,CHARINDEX('@',EVAM.Narrative)+2,5) ELSE NULL
END
FROM #ReportEvent REPT
INNER JOIN EventTainers EVTN
ON REPT.EventTypeKey = EVTN.EventTypeKey
AND REPT.EventKey = EVTN.EventKey
INNER JOIN #EventSubTainerCount SUBT
ON REPT.EventTypeKey = SUBT.EventTypeKey
AND REPT.EventKey = SUBT.EventKey
INNER JOIN Product PROD
ON EVTN.TainerKey = PROD.TainerKey
INNER JOIN EventAmounts EVAM
ON REPT.EventTypeKey = EVAM.EventTypeKey
AND REPT.EventKey = EVAM.EventKey
INNER JOIN EventAmountType EVAT
ON EVAM.AmountTypeKey = EVAT.AmountTypeKey
WHERE EVAT.AmountCategory IN
(@AmountCategory1,@AmountCategory2,@AmountCategory3)
*/
/* Get latest Control Event Data for AmountCategory Sale */
INSERT #LastControlEvent
(TainerKey,ControlDocumentDate)
SELECT PAYW.TainerKey, MAX(EVNT.DocumentDate)
FROM #PaymentWork PAYW
INNER JOIN EventTainers EVTN
ON PAYW.TainerKey = EVTN.TainerKey
INNER JOIN ControlEvent EVCT
ON EVCT.EventTypeKey = EVTN.EventTypeKey
AND EVCT.EventKey = EVTN.EventKey
INNER JOIN Event EVNT
ON EVNT.EventTypeKey = EVTN.EventTypeKey
AND EVNT.EventKey = EVTN.EventKey
WHERE PAYW.AmountCategory = 'S'
GROUP BY PAYW.TainerKey
UPDATE #LastControlEvent
SET ControlExternalReference = EVNT.DocumentExternalreference
FROM #LastControlEvent LCEV
INNER JOIN EventTainers EVTN
ON LCEV.TainerKey = EVTN.TainerKey
INNER JOIN ControlEvent EVCT
ON EVCT.EventTypeKey = EVTN.EventTypeKey
AND EVCT.EventKey = EVTN.EventKey
INNER JOIN Event EVNT
ON EVNT.EventTypeKey = EVTN.EventTypeKey
AND EVNT.EventKey = EVTN.EventKey
AND EVNT.DocumentDate = LCEV.ControlDocumentDate
/* Drop all rows not for specified Tainer Key, if supplied */
/*
IF @TainerKey IS NOT NULL BEGIN
DELETE #PaymentWork
WHERE TainerKey <> @TainerKey
END
*/
/* Get all required attributes */
-- SELECT * FROM #PaymentWork
INSERT #PaymentReport
(DocumentExternalReference,DocumentDate,ProducerOrgTainerKey,TargetMarketKey,
TainerTypeKey,TainerKey,TainerExternalReference,TainerDescription,
PalletMark,ParentTainerKey,LocTainerKey,OwnerOrgTainerKey,CustodianOrgTainerKey,
CountryKey,TainerState,InspectionStateKey,MixedPalletIndicator,LineNumber,
BrandKey,GradeKey,VarietyKey,InventoryKey,SubTainerKey,CountTableKey,CountValue,
InitialQuantity,CurrentQuantity,PickingCode,PickingDate,PackingCode,PackingDate,Orchard,
EventOrgTainerKey,EventAmountSequence,AmountLevel,AmountTypeKey,CurrencyKey,ExchangeRate,
ActualAmount,TaxAmount,AmountCategory,AmountTypeDescription,CommissionPercentage)
SELECT
EVNT.DocumentExternalReference,EVNT.DocumentDate,PROD.ProducerOrgTainerKey,
TAIN.TargetMarketKey,TAIN.TainerTypeKey,TAIN.TainerKey,TAIN.TainerExternalReference,
TAIN.Description,TAIN.PalletMark,NULL,TAIN.LocTainerKey,
TAIN.OwnerOrgTainerKey,TAIN.CustodianOrgTainerKey,TAIN.CountryKey,
TAIN.TainerState,PROD.InspectionStateKey,TAIN.MixedPalletIndicator,PROD.LineNumber,
PROD.BrandKey,PROD.GradeKey,PROD.VarietyKey,PROD.InventoryKey,PROD.SubTainerKey,
PROD.CountTableKey,CountValue,PROD.InitialQuantity,PROD.CurrentQuantity,
PROD.PickingCode,PROD.PickingDate,PROD.PackingCode,PROD.PackingDate,PROD.Orchard,EVNT.OrgTainerKey,
PAYW.EventAmountSequence,PAYW.AmountLevel,PAYW.AmountTypeKey,PAYW.CurrencyKey,PAYW.ExchangeRate,
PAYW.Amount,PAYW.TaxAmount,PAYW.AmountCategory,PAYW.AmountTypeDescription,PAYW.CommissionPercentage
FROM #PaymentWork PAYW
INNER JOIN Event EVNT
ON PAYW.EventTypeKey = EVNT.EventTypeKey
AND PAYW.EventKey = EVNT.EventKey
INNER JOIN Tainer TAIN
ON PAYW.TainerKey = TAIN.TainerKey
INNER JOIN Product PROD
ON PAYW.TainerKey = PROD.TainerKey
AND PAYW.LineNumber = PROD.LineNumber
WHERE ISNULL(PROD.ProducerOrgTainerKey,SPACE(6)) BETWEEN
ISNULL(@ProducerOrgTainerKey,SPACE(6)) AND
ISNULL(@ProducerOrgTainerKey,'ZZZZZZ')
AND ISNULL(TAIN.OwnerOrgTainerKey,SPACE(6)) BETWEEN
ISNULL(@OwnerOrgTainerKey,SPACE(6)) AND
ISNULL(@OwnerOrgTainerKey,'ZZZZZZ')
AND ISNULL(TAIN.CustodianOrgTainerKey,SPACE(6)) BETWEEN
ISNULL(@CustodianOrgTainerKey,SPACE(6)) AND
ISNULL(@CustodianOrgTainerKey,'ZZZZZZ')
-- SELECT * FROM #PaymentReport
/* Update additional details */
UPDATE #PaymentReport
SET ProducerDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.ProducerOrgTainerKey
WHERE REPT.ProducerOrgTainerKey IS NOT NULL
UPDATE #PaymentReport
SET OwnerDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.OwnerOrgTainerKey
WHERE REPT.OwnerOrgTainerKey IS NOT NULL
UPDATE #PaymentReport
SET CustodianDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.CustodianOrgTainerKey
WHERE REPT.CustodianOrgTainerKey IS NOT NULL
UPDATE #PaymentReport
SET EventDescription = ORGN.Description
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.EventOrgTainerKey
WHERE REPT.EventOrgTainerKey IS NOT NULL
IF @AddressType = 'P' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.ProducerOrgTainerKey
END
IF @AddressType = 'O' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.OwnerOrgTainerKey
END
IF @AddressType = 'C' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.CustodianOrgTainerKey
END
IF @AddressType = 'E' BEGIN
UPDATE #PaymentReport
SET PostalAddressLine1 = ORGN.PostalAddressLine1,
PostalAddressLine2 = ORGN.PostalAddressLine2,
PostalAddressLine3 = ORGN.PostalAddressLine3,
PostalAddressLine4 = ORGN.PostalAddressLine4
FROM OrgTainer ORGN
INNER JOIN #PaymentReport REPT
ON ORGN.OrgTainerKey = REPT.EventOrgTainerKey
END
UPDATE #PaymentReport
SET VarietyDescription = VRTY.Description,
CommodityKey = VRTY.CommodityKey,
CommodityDescription = CMDT.Description
FROM Variety VRTY
INNER JOIN #PaymentReport REPT
ON VRTY.VarietyKey = REPT.VarietyKey
INNER JOIN Commodity CMDT
ON VRTY.CommodityKey = CMDT.CommodityKey
UPDATE #PaymentReport
SET BrandDescription = BRND.Description
FROM Brand BRND
INNER JOIN #PaymentReport REPT
ON BRND.BrandKey = REPT.BrandKey
WHERE REPT.BrandKey IS NOT NULL
UPDATE #PaymentReport
SET GradeDescription = GRAD.Description
FROM Grade GRAD
INNER JOIN #PaymentReport REPT
ON GRAD.GradeKey = REPT.GradeKey
WHERE REPT.GradeKey IS NOT NULL
UPDATE #PaymentReport
SET InventoryDescription = INVT.Description
FROM Inventory INVT
INNER JOIN #PaymentReport REPT
ON INVT.InventoryKey = REPT.InventoryKey
WHERE REPT.InventoryKey IS NOT NULL
UPDATE #PaymentReport
SET IntakeExternalReference = EVNT.DocumentExternalReference,
IntakeDocumentDate = EVNT.DocumentDate
FROM Tainer TAIN
INNER JOIN #PaymentReport REPT
ON TAIN.TainerKey = REPT.TainerKey
INNER JOIN Event EVNT
ON TAIN.EventTypeKey = EVNT.EventTypeKey
AND TAIN.EventKey = EVNT.EventKey
UPDATE #PaymentReport
SET CurrencyDescription = CURR.Description
FROM Currency CURR
INNER JOIN #PaymentReport REPT
ON CURR.CurrencyKey = REPT.CurrencyKey
UPDATE #PaymentReport
SET Nettweight = SUBT.Nettweight
FROM SubTainer SUBT
INNER JOIN #PaymentReport REPT
ON SUBT.SubTainerKey = REPT.SubTainerKey
UPDATE #PaymentReport
SET ControlExternalReference = LCEV.ControlExternalReference,
ControlDocumentDate = LCEV.ControlDocumentDate
FROM #LastControlEvent LCEV
INNER JOIN #PaymentReport REPT
ON LCEV.TainerKey = REPT.TainerKey
WHERE REPT.AmountCategory = 'S'
UPDATE #PaymentReport
SET InspectionStateDescription = INSS.Description
FROM InspectionState INSS
INNER JOIN #PaymentReport REPT
ON INSS.InspectionStateKey =
REPT.InspectionStateKey
UPDATE #PaymentReport
SET ParentTainerKey = PROD.ParentTainerKey,
ParentTainerExternalReference = TAIN.TainerExternalReference
FROM #Products PROD
INNER JOIN #PaymentReport REPT
ON PROD.TainerKey = REPT.TainerKey
AND PROD.LineNumber = REPT.LineNumber
INNER JOIN Tainer TAIN
ON PROD.ParentTainerKey = TAIN.TainerKey
WHERE PROD.TainerKey <> PROD.ParentTainerKey
/* Get Result Set */
/* Eric Saturday May 06, 2000 changed field order and order by */
SELECT
TainerExternalReference,AmountTypeKey,CurrencyKey,
ExchangeRate=CONVERT(DECIMAL(12,4),ExchangeRate),AmountLevel,ActualAmount,
VarietyKey,CurrentQuantity,MixedPalletIndicator,LineNumber,NettWeight,
PackingDate=CONVERT(VARCHAR(10),PackingDate,111),BrandKey,GradeKey,CountValue,
IntakeExternalReference,IntakeDocumentDate=CONVERT(VARCHAR(10),IntakeDocumentDate,111),
ControlExternalReference,ControlDocumentDate=CONVERT(VARCHAR(10),ControlDocumentDate,111),
DocumentExternalReference,DocumentDate=CONVERT(VARCHAR(10),DocumentDate,111),ProducerOrgTainerKey,
ProducerDescription,PostalAddressLine1,PostalAddressLine2,PostalAddressLine3,
PostalAddressLine4,TargetMarketKey,TainerTypeKey,TainerDescription,
PalletMark,ParentTainerKey,ParentTainerExternalReference,LocTainerKey,
OwnerOrgTainerKey,OwnerDescription,CustodianOrgTainerKey,
CustodianDescription,CountryKey,TainerState,InspectionStateKey,InspectionStatedescription,
CommodityKey,CommodityDescription,BrandDescription,
GradeDescription,VarietyDescription,InventoryKey,InventoryDescription,
SubTainerKey,CountTableKey,InitialQuantity,PickingCode,PickingDate=CONVERT(VARCHAR(10),PickingDate,111),PackingCode,
Orchard,EventOrgTainerKey,EventDescription,
TaxAmount,AmountCategory,AmountTypeDescription,
AmountCategoryDescription=CASE AmountCategory
WHEN 'C' THEN 'Cost'
WHEN 'P' THEN 'Payment'
WHEN 'S' THEN 'Sale'
ELSE '????'
END,
CurrencyDescription, CommissionPercentage,EventAmountSequence
FROM #PaymentReport
REVERSE ORDER BY
TainerexternalReference,AmountTypeKey,CurrencyKey,ExchangeRate,AmountLevel,ActualAmount,
VarietyKey,CurrentQuantity,MixedPalletIndicator,LineNumber,NettWeight,
PackingDate,BrandKey,GradeKey,CountValue,
IntakeExternalReference,IntakeDocumentDate,ProducerOrgTainerKey,DocumentExternalReference
END
GO
GRANT EXECUTE ON RD_ReconciliationReport TO Userinterface, Reporting
GO
