Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- --------------------------------------------------------------------------------
- -- Routine DDL
- -- --------------------------------------------------------------------------------
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `GenLCLExportDailyStats` $$
- CREATE PROCEDURE `GenLCLExportDailyStats`( IN pSailDate INT(11) )
- MODIFIES SQL DATA
- MAIN: BEGIN
- -- Server SQL Logging declarations.
- DECLARE mSLS VARCHAR(64) DEFAULT "GenLCLExportDailyStats";
- DECLARE mSLH VARCHAR(256) DEFAULT "";
- DECLARE mSLT VARCHAR(128) DEFAULT UUID();
- DECLARE mDEBUG BOOL DEFAULT FALSE;
- DECLARE mSailDate DATE DEFAULT NULL;
- DECLARE mTotalCBM DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalKGS DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalAPCost DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalBiblesCost DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalInvoice DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalGenExp DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalUnitVY DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalChgs DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mTotalCns DECIMAL(10,2) DEFAULT 0.00;
- DECLARE mBLTerm VARCHAR(2) DEFAULT "";
- DECLARE mPort VARCHAR(3) DEFAULT "";
- DECLARE mDRTerm VARCHAR(2) DEFAULT "";
- DECLARE mDRNum VARCHAR(6) DEFAULT "";
- DECLARE mGLTerm VARCHAR(3) DEFAULT "";
- DECLARE mCntVoy VARCHAR(4) DEFAULT "";
- DECLARE mBLFdes VARCHAR(3) DEFAULT "";
- DECLARE mTRecs INT(11) DEFAULT 0;
- -- SQL Error Handler Variables
- -- These must appear BEFORE cursor declarations.
- DECLARE mRecordNotFound BOOL DEFAULT FALSE;
- DECLARE mCursorEOL BOOL DEFAULT FALSE;
- -- SQL CURSOR declarations begin here.
- DECLARE mCursorA CURSOR FOR
- SELECT
- blterm, port, drterm, drnum, glterm, cntvoy, blfdes,
- (
- IF ( ChgsChgCdeInGLNumb( chg01, "3440" ), amt01, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg02, "3440" ), amt02, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg03, "3440" ), amt03, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg04, "3440" ), amt04, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg05, "3440" ), amt05, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg06, "3440" ), amt06, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg07, "3440" ), amt07, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg08, "3440" ), amt08, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg09, "3440" ), amt09, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg10, "3440" ), amt10, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg11, "3440" ), amt11, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( chg12, "3440" ), amt12, 0.00 )
- ) AS 'AMT',
- IF( engmet = 'M', ( msr01 + msr02 + msr03 + msr04 + msr05 + msr06 ) / 100, ( msr01 + msr02 + msr03 + msr04 + msr05 + msr06 ) / 35.314 ) AS 'CBM',
- IF( engmet = 'M', ( wgt01 + wgt02 + wgt03 + wgt04 + wgt05 + wgt06 ) / 100, ( wgt01 + wgt02 + wgt03 + wgt04 + wgt05 + wgt06 ) / 2.2046 ) AS 'KGS'
- FROM histry
- WHERE saildt = pSailDate
- AND airocn='O';
- DECLARE mCursorB CURSOR FOR
- SELECT DISTINCT port, glterm, cntvoy, blfdes
- FROM histry
- WHERE saildt = pSailDate
- AND airocn='O';
- DECLARE mCursorC CURSOR FOR
- SELECT DISTINCT glterm, PORT, blfdes, cntvoy
- FROM histry
- WHERE saildt = pSailDate
- AND airocn='O';
- -- SQL CURSOR declarations end here.
- -- SQL Error Handlers
- -- These must appear AFTER cursor declarations.
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET mRecordNotFound = TRUE;
- DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET mCursorEOL = TRUE;
- -- ****************************************************************
- -- VALIDATION
- -- Set the SQL Server Log Header.
- SET mSLH = CONCAT( ": pSailDate (", pSailDate, "): " );
- IF ISNULL( pSailDate ) OR pSailDate < 1 THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "VALIDATION", mSLH, "Stopped; pSailDate invalid." ) );
- LEAVE MAIN;
- END IF;
- SET mSailDate = DATE_FORMAT( pSailDate, "%Y%m%d" );
- IF ISNULL( mSailDate ) THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "VALIDATION", mSLH, "Stopped; pSailDate invalid." ) );
- LEAVE MAIN;
- END IF;
- -- VALIDATION
- -- ****************************************************************
- -- Debug Entry
- IF mDEBUG = TRUE THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG", mSLH, "Begins." ) );
- END IF;
- -- Make sure the table is empty for the selected date
- -- because this procedure regens all the data for entire date!
- START TRANSACTION;
- DELETE FROM ExportDailyStats WHERE SailDate = mSailDate;
- COMMIT;
- -- *******************************************************
- -- Loop A
- -- Charges and Costs by BL
- START TRANSACTION;
- OPEN mCursorA;
- -- Debug Entry
- IF mDEBUG = TRUE THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG: Cursor opened, starting loop A." ) );
- END IF;
- LoopA: LOOP
- -- Reset
- SET mTotalCBM = 0.00;
- SET mTotalKGS = 0.00;
- SET mTotalAPCost = 0.00;
- SET mTotalInvoice = 0.00;
- SET mTotalBiblesCost = 0.00;
- SET mTotalChgs = 0.00;
- SET mTotalCns = 0.00;
- SET mBLTerm = "";
- SET mPort = "";
- SET mDRTerm = "";
- SET mDRNum = "";
- SET mGLTerm = "";
- SET mCntVoy = "";
- SET mBLFdes = "";
- -- Get next record from the table.
- SET mCursorEOL = FALSE;
- FETCH mCursorA INTO mBLTerm, mPort, mDRTerm, mDRNum, mGLTerm, mCntVoy, mBLFdes, mTotalChgs, mTotalCBM, mTotalKGS;
- IF mCursorEOL = TRUE THEN
- LEAVE LoopA;
- END IF;
- -- Get the total Correction Notices associated with this BL.
- -- Only get the LATEST entry (may be more than one entry but the latest is the final).
- SELECT
- IF ( ChgsChgCdeInGLNumb( ccd01, "3440" ), cna01, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd02, "3440" ), cna02, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd03, "3440" ), cna03, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd04, "3440" ), cna04, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd05, "3440" ), cna05, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd06, "3440" ), cna06, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd07, "3440" ), cna07, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd08, "3440" ), cna08, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd09, "3440" ), cna09, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd10, "3440" ), cna10, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd11, "3440" ), cna11, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( ccd12, "3440" ), cna12, 0.00 )
- INTO mTotalCns
- FROM cnoths
- WHERE blkey = CONCAT( mBLTerm, mPort, mDRTerm, mDRNum )
- ORDER BY cndate DESC
- LIMIT 1;
- -- Get the Type 4 COSTS associated with this BL.
- SELECT SUM( apcost.amount ) INTO mTotalAPCost
- FROM bldr, apcost
- WHERE bldr.blterm = mBLTerm
- AND bldr.PORT = mPort
- AND bldr.drterm = mDRTerm
- AND bldr.drnum = mDRNum
- AND apcost.type = '4'
- AND apcost.askfld = CONCAT( bldr.dr1, bldr.dr2 )
- AND apcost.gltrml = mGLTerm
- AND ( FclCodFclCodInGLNumb( apcost.cstcde, "3403" ) = TRUE
- OR FclCodFclCodInGLNumb( apcost.cstcde, "3492" ) = TRUE
- OR FclCodFclCodInGLNumb( apcost.cstcde, "4009" ) = TRUE
- OR FclCodFclCodInGLNumb( apcost.cstcde, "4010" ) = TRUE
- OR FclCodFclCodInGLNumb( apcost.cstcde, "4030" ) = TRUE
- OR FclCodFclCodInGLNumb( apcost.cstcde, "4041" ) = TRUE
- OR FclCodFclCodInGLNumb( apcost.cstcde, "4130" ) = TRUE );
- -- Cleanup Results
- IF ISNULL( mTotalCBM ) = TRUE THEN
- SET mTotalCBM = 0.00;
- END IF;
- IF ISNULL( mTotalKGS ) = TRUE THEN
- SET mTotalKGS = 0.00;
- END IF;
- IF ISNULL( mTotalAPCost ) = TRUE THEN
- SET mTotalAPCost = 0.00;
- END IF;
- IF ISNULL( mTotalInvoice ) = TRUE THEN
- SET mTotalInvoice = 0.00;
- END IF;
- IF ISNULL( mTotalBiblesCost ) = TRUE THEN
- SET mTotalBiblesCost = 0.00;
- END IF;
- IF ISNULL( mTotalChgs ) = TRUE THEN
- SET mTotalChgs = 0.00;
- END IF;
- IF ISNULL( mTotalCns ) = TRUE THEN
- SET mTotalCns = 0.00;
- END IF;
- -- Update temporary table.
- INSERT INTO ExportDailyStats SET
- SailDate = mSailDate,
- LoadTerm = mGLTerm,
- UnLoadTerm = mPort,
- DestTerm = mBLFdes,
- TotalCBM = mTotalCBM,
- TotalKGS = mTotalKGS,
- TotalAPCost4 = mTotalAPCost,
- TotalChgs = mTotalChgs,
- TotalCns = mTotalCns,
- TotalBLs = 1,
- RecLastUpdated = NULL
- ON DUPLICATE KEY UPDATE
- TotalCBM = TotalCBM + mTotalCBM,
- TotalKGS = TotalKGS + mTotalKGS,
- TotalAPCost4 = TotalAPCost4 + mTotalAPCost,
- TotalChgs = TotalChgs + mTotalChgs,
- TotalCns = TotalCns + mTotalCns,
- TotalBLs = TotalBLs + 1,
- RecLastUpdated = NULL;
- END LOOP LoopA;
- -- Cleanup
- CLOSE mCursorA;
- COMMIT;
- -- *******************************************************
- -- Loop B
- -- Costs by VOYAGE (Voyages consists of multiple BLs!)
- START TRANSACTION;
- OPEN mCursorB;
- -- Debug Entry
- IF mDEBUG = TRUE THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG: Cursor opened, starting loop B." ) );
- END IF;
- LoopB: LOOP
- -- Reset
- SET mTotalCBM = 0.00;
- SET mTotalKGS = 0.00;
- SET mTotalAPCost = 0.00;
- SET mTotalBiblesCost = 0.00;
- SET mTotalChgs = 0.00;
- SET mTotalCns = 0.00;
- SET mTotalInvoice = 0.00;
- SET mTotalUnitVY = 0.00;
- SET mBLTerm = "";
- SET mPort = "";
- SET mDRTerm = "";
- SET mDRNum = "";
- SET mGLTerm = "";
- SET mCntVoy = "";
- SET mBLFdes = "";
- SET mTRecs = 0;
- -- Get next record from the table.
- SET mCursorEOL = FALSE;
- FETCH mCursorB INTO mPort, mGLTerm, mCntVoy, mBLFdes;
- IF mCursorEOL = TRUE THEN
- LEAVE LoopB;
- END IF;
- -- Get the Type 5 COSTS associated with this Voyage.
- SELECT SUM( amount ) INTO mTotalAPCost
- FROM apcost
- WHERE type = '5'
- AND askfld LIKE CONCAT( mGLTerm, mPort, mCntVoy, "%" )
- AND gltrml = mGLTerm
- AND ( FclCodFclCodInGLNumb( cstcde, "3403" ) = TRUE
- OR FclCodFclCodInGLNumb( cstcde, "3492" ) = TRUE
- OR FclCodFclCodInGLNumb( cstcde, "4009" ) = TRUE
- OR FclCodFclCodInGLNumb( cstcde, "4010" ) = TRUE
- OR FclCodFclCodInGLNumb( cstcde, "4030" ) = TRUE
- OR FclCodFclCodInGLNumb( cstcde, "4041" ) = TRUE
- OR FclCodFclCodInGLNumb( cstcde, "4130" ) = TRUE );
- IF ISNULL( mTotalAPCost ) = TRUE THEN
- SET mTotalAPCost = 0.00;
- END IF;
- SELECT IF( SUM( trlcbm ) != 0.00, SUM( trlcbm ), SUM( trlcft ) / 35.314 )
- INTO mTotalUnitVY
- FROM unitvy
- WHERE voynum = CONCAT( mGLTerm, mPort, mCntVoy );
- IF ISNULL( mTotalUnitVY ) THEN
- SET mTotalUnitVY = 0.00;
- END IF;
- -- Get the Bibles associated with this Voyage.
- SELECT amnt01 + amnt02 + amnt03 + amnt04 + amnt05 + amnt06 + amnt07 + amnt08 + amnt09 + amnt10 + amnt11 + amnt12
- INTO mTotalBiblesCost
- FROM bibles
- WHERE trmnum = mGLTerm
- AND prtnum = mPort
- AND voynum = mCntVoy;
- IF ISNULL( mTotalBiblesCost ) = TRUE THEN
- SET mTotalBiblesCost = 0.00;
- END IF;
- -- Update temporary table - with TotalUnitVY.
- UPDATE ExportDailyStats SET
- TotalAPCost5 = TotalAPCost5 + mTotalAPCost,
- TotalUnitVY = TotalUnitVY + mTotalUnitVY,
- TotalBiblesCost = TotalBiblesCost + mTotalBiblesCost,
- RecLastUpdated = NULL
- WHERE SailDate = mSailDate
- AND LoadTerm = mGLTerm
- AND UnloadTerm = mPort
- AND DestTerm = mBLFdes;
- END LOOP LoopB;
- -- Cleanup
- CLOSE mCursorB;
- -- *******************************************************
- -- Loop C
- -- Invoices by Voyage Destination
- OPEN mCursorC;
- -- Debug Entry
- IF mDEBUG = TRUE THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, CONCAT( "DEBUG: Cursor opened, starting loop C." ) );
- END IF;
- LoopC: LOOP
- -- Reset
- SET mTotalInvoice = 0.00;
- SET mBLTerm = "";
- SET mPort = "";
- SET mDRTerm = "";
- SET mDRNum = "";
- SET mGLTerm = "";
- SET mCntVoy = "";
- SET mBLFdes = "";
- SET mTRecs = 0;
- -- Get next record from the table.
- SET mCursorEOL = FALSE;
- FETCH mCursorC INTO mGLTerm, mPort, mBLFdes, mCntVoy;
- IF mCursorEOL = TRUE THEN
- LEAVE LoopC;
- END IF;
- -- Get the INVOICE charges associated with this BL.
- SELECT SUM( `InvAmts` ) INTO mTotalInvoice
- FROM
- (
- SELECT DISTINCT invoic.invnum,
- (
- IF ( ChgsChgCdeInGLNumb( invoic.chg01, "3492" ), invoic.amt01, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg01, "4030" ), invoic.amt01, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg02, "3492" ), invoic.amt02, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg02, "4030" ), invoic.amt02, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg03, "3492" ), invoic.amt03, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg03, "4030" ), invoic.amt03, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg04, "3492" ), invoic.amt04, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg04, "4030" ), invoic.amt04, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg05, "3492" ), invoic.amt05, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg05, "4030" ), invoic.amt05, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg06, "3492" ), invoic.amt06, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg06, "4030" ), invoic.amt06, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg07, "3492" ), invoic.amt07, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg07, "4030" ), invoic.amt07, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg08, "3492" ), invoic.amt08, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg08, "4030" ), invoic.amt08, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg09, "3492" ), invoic.amt09, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg09, "4030" ), invoic.amt09, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg10, "3492" ), invoic.amt10, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg10, "4030" ), invoic.amt10, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg11, "3492" ), invoic.amt11, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg11, "4030" ), invoic.amt11, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg12, "3492" ), invoic.amt12, 0.00 ) +
- IF ( ChgsChgCdeInGLNumb( invoic.chg12, "4030" ), invoic.amt12, 0.00 )
- ) AS `InvAmts`
- FROM histry, bldr, invdrs, invoic
- WHERE histry.saildt = pSailDate
- AND histry.port = mPort
- AND histry.glterm = mGLTerm
- AND histry.airocn = 'O'
- AND histry.cntvoy = mCntVoy
- AND bldr.blterm = histry.`blterm`
- AND bldr.PORT = histry.`port`
- AND bldr.drterm = histry.`drterm`
- AND bldr.drnum = histry.`drnum`
- AND invdrs.drcnum = CONCAT( bldr.dr1, bldr.dr2 )
- AND invoic.cmpnum = invdrs.cmpnum
- AND invoic.trmnum = invdrs.trmnum
- AND invoic.invnum = invdrs.invnum
- AND invoic.cntnum = invdrs.cntnum
- ) AS `SumAllInvoices`;
- IF ISNULL( mTotalInvoice ) = FALSE OR mTotalInvoice != 0.00 THEN
- -- Update temporary table - with TotalUnitVY.
- UPDATE ExportDailyStats SET
- TotalInvoice = TotalInvoice + mTotalInvoice,
- RecLastUpdated = NULL
- WHERE SailDate = mSailDate
- AND LoadTerm = mGLTerm
- AND UnloadTerm = mPort
- AND DestTerm = mBLFdes;
- END IF;
- END LOOP LoopC;
- -- Cleanup
- CLOSE mCursorC;
- -- *******************************************************
- -- General Expenses
- -- NOT CORRECT
- UPDATE ExportDailyStats, genexp SET
- TotalGenExp = TotalGenExp +
- ( amnt01 + amnt02 + amnt03 + amnt04 + amnt05 + amnt06 +
- amnt07 + amnt08 + amnt09 + amnt10 + amnt11 + amnt12 ),
- RecLastUpdated = NULL
- WHERE SailDate = mSailDate
- AND LoadTerm = genexp.trmnum
- AND UnloadTerm = genexp.prtnum
- AND genexp.geyear = "0000"
- AND genexp.gemnth = "00";
- COMMIT;
- -- Debug Entry
- IF mDEBUG = TRUE THEN
- CALL LogSQLEntry( 1, mSLS, mSLT, "DEBUG: Ends normally." );
- END IF;
- END MAIN $$
Add Comment
Please, Sign In to add comment