CREATE PROCEDURE "PMX_SP_InspectionCertificate" (
	p_luid int,
	p_resocode nvarchar(50)
)
LANGUAGE SQLSCRIPT AS
BEGIN

    DECLARE v_returnSSCC nvarchar(18);
    DECLARE v_returnGTIN nvarchar (254); 
    DECLARE v_returnLogisticCarrierCode nvarchar (50);  
    DECLARE v_returnItemCode nvarchar(50); 
    DECLARE v_returnProductDescription nvarchar(200); 
    DECLARE v_returnQuantity numeric(19,0); 
    DECLARE v_returnIsLogisticCarrier nvarchar(1); 
    DECLARE v_returnBatchNumber nvarchar(36); 
    DECLARE v_returnBestBeforeDate datetime;
    DECLARE v_returnBarcodeType nvarchar (1);
    DECLARE v_returnSSCCBarcode nvarchar (100);
    DECLARE v_returnSSCCBarcodeDigit nvarchar (100);
    DECLARE v_returnGTINBarcode nvarchar (200);
    DECLARE v_returnGTINBarcodeDigit nvarchar (200);
    DECLARE v_returnGTINLabel nvarchar (20);
    DECLARE v_ItemCode nvarchar(50); 
    DECLARE v_BatchNumber nvarchar(36); 
    DECLARE v_BestBeforeDate datetime;
	DECLARE v_TempQuantityUom2 numeric(19,6); 
    DECLARE v_TempWeightDecimals int;
	DECLARE v_TempWeightAI nvarchar (3); 
	DECLARE v_returnInspectionOrderCode numeric(11,0);


    SELECT CASE WHEN PMX_LUID."SsccType" = 'S' THEN PMX_LUID.SSCC ELSE null END AS SSCC,
           MIN( PMX_OITM_MANAGED_BY_PMX."CodeBars" ),
           PMX_LUID."LogisticCarrierCode", 
           MIN( PMX_OITM_MANAGED_BY_PMX."ItemCode" ), MAX( PMX_OITM_MANAGED_BY_PMX."ItemCode" ), IFNULL(MIN( PMX_OITM_MANAGED_BY_PMX."ItemName" ), MIN( PMX_OITM_MANAGED_BY_PMX."ItemCode" )), 
           SUM( PMX_INVT."Quantity" ), 
           IFNULL(MIN( PMX_OITM_MANAGED_BY_PMX.U_PMX_LOCA ), 'N'), 
           MIN( PMX_ITRI."BatchNumber" ), MAX( PMX_ITRI."BatchNumber" ),
           MIN( PMX_ITRI."BestBeforeDate" ), MAX( PMX_ITRI."BestBeforeDate" ),
           IFNULL( MIN( PMX_OITM_MANAGED_BY_PMX.U_PMX_BCTY ),'G'),
           SUM( PMX_INVT."QuantityUom2" ),
           MIN("U_PMX_UM2D"), 
           MIN("U_PMX_U2MT"||'0')

    INTO v_returnSSCC,
           v_returnGTIN,
           v_returnLogisticCarrierCode,
           v_returnItemCode, v_ItemCode, v_returnProductDescription,
           v_returnQuantity,
           v_returnIsLogisticCarrier,
           v_returnBatchNumber, v_BatchNumber,
           v_returnBestBeforeDate, v_BestBeforeDate,
           v_returnBarcodeType, v_TempQuantityUom2 , 
           v_TempWeightDecimals, v_TempWeightAI
           default null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
    FROM PMX_LUID
    INNER JOIN PMX_INVT ON PMX_INVT."LogUnitIdentKey" = PMX_LUID."InternalKey"
                       AND ( PMX_LUID."LogisticCarrierCode" IS NULL OR PMX_INVT."ItemCode" <> PMX_LUID."LogisticCarrierCode" OR PMX_INVT."Quantity" <> 1 )
    INNER JOIN PMX_OITM_MANAGED_BY_PMX     ON PMX_OITM_MANAGED_BY_PMX."ItemCode" = PMX_INVT."ItemCode"
    LEFT  JOIN PMX_ITRI ON PMX_ITRI."InternalKey" = PMX_INVT."ItemTransactionalInfoKey"
    WHERE PMX_LUID."InternalKey" = p_luid
    GROUP BY PMX_LUID.SSCC, PMX_LUID."LogisticCarrierCode", PMX_LUID."SsccType";

    IF v_returnSSCC IS NOT NULL THEN

        IF v_returnItemCode <> v_ItemCode THEN

            v_returnGTIN := NULL;
            v_returnItemCode := NULL;
            v_returnProductDescription := NULL;
            v_returnQuantity := NULL;
            v_returnBatchNumber := NULL;
            v_returnBestBeforeDate := NULL;
            v_returnBarcodeType := NULL;
            v_TempQuantityUom2 := NULL; 
            v_TempWeightDecimals := NULL; 
            v_TempWeightAI := NULL;

        ELSE

            IF v_returnBatchNumber    <> v_BatchNumber    THEN v_returnBatchNumber    := NULL; END IF;
            IF v_returnBestBeforeDate <> v_BestBeforeDate THEN v_returnBestBeforeDate := NULL; END IF;

        END IF;

    ELSE

        SELECT PMX_LUID.SSCC,
               PMX_OITM_MANAGED_BY_PMX."CodeBars",
               PMX_LUID."LogisticCarrierCode",
               PMX_OITM_MANAGED_BY_PMX."ItemCode", IFNULL(PMX_OITM_MANAGED_BY_PMX."ItemName", PMX_OITM_MANAGED_BY_PMX."ItemCode"),
               PMX_ASND."Quantity",
               IFNULL(PMX_OITM_MANAGED_BY_PMX.U_PMX_LOCA, 'N'),
               PMX_ITRI."BatchNumber",
               PMX_ITRI."BestBeforeDate",
               IFNULL(U_PMX_BCTY,'G'),
	           PMX_ASND."QuantityUom2",
			   "U_PMX_UM2D", 
			   "U_PMX_U2MT"||'0'
        INTO v_returnSSCC,
               v_returnGTIN,
               v_returnLogisticCarrierCode,
               v_returnItemCode, v_returnProductDescription,
               v_returnQuantity,
               v_returnIsLogisticCarrier,
               v_returnBatchNumber,
               v_returnBestBeforeDate,
               v_returnBarcodeType,
               v_TempQuantityUom2,
               v_TempWeightDecimals, 
               v_TempWeightAI 
               default null, null, null, null, null, null, null, null, null, null, null, null, null
        FROM   PMX_LUID
        INNER JOIN PMX_ASND ON PMX_ASND."LogUnitIdentKey" = PMX_LUID."InternalKey" AND PMX_ASND."LineStatus" = 'O'  -- Get only open PMX_ASND's
        INNER JOIN PMX_OITM_MANAGED_BY_PMX     ON PMX_OITM_MANAGED_BY_PMX."ItemCode" = PMX_ASND."ItemCode"
        LEFT  JOIN PMX_ITRI ON PMX_ITRI."InternalKey" = PMX_ASND."ItemTransactionalInfoKey"
        WHERE   PMX_LUID."InternalKey" = p_luid
        ORDER BY IFNULL(PMX_OITM_MANAGED_BY_PMX.U_PMX_LOCA, 'N'), PMX_ASND."ItemCode";
        
    END IF;


    IF v_returnIsLogisticCarrier = 'Y' THEN
        v_returnGTINLabel := 'GTIN';
    ELSE 
        v_returnGTINLabel := 'CONTENT';
    END IF;

	IF v_returnBestBeforeDate IS NULL THEN v_returnBestBeforeDate := '1899-12-31'; END IF;
	IF v_returnQuantity IS NULL THEN v_returnQuantity := -1; END IF;
	IF v_tempQuantityUom2 IS NULL THEN v_tempQuantityUom2 := -1; END IF;
	IF v_tempWeightDecimals IS NULL THEN v_tempWeightDecimals := -1; END IF;

    --Set the barcodes
	SELECT barcode     INTO v_returnSSCCBarcode      FROM "PMX_FN_GenerateGS1Barcode"      ( :v_returnSSCC, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Ê' );
	SELECT barcodeText INTO v_returnSSCCBarcodeDigit FROM "PMX_FN_GenerateGS1BarcodeDigit" ( :v_returnSSCC, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL );
	SELECT barcode     INTO v_returnGTINBarcode      FROM "PMX_FN_GenerateGS1Barcode"      ( NULL, :v_returnGTIN, :v_returnIsLogisticCarrier, :v_returnBarcodeType, :v_returnBestBeforeDate, :v_returnBatchNumber, :v_returnQuantity, :v_tempQuantityUom2, :v_tempWeightDecimals, :v_tempWeightAI, 'Ê' );
	SELECT barcodeText INTO v_returnGTINBarcodeDigit FROM "PMX_FN_GenerateGS1BarcodeDigit" ( NULL, :v_returnGTIN, :v_returnIsLogisticCarrier, :v_returnBarcodeType, :v_returnBestBeforeDate, :v_returnBatchNumber, :v_returnQuantity, :v_tempQuantityUom2, :v_tempWeightDecimals, :v_tempWeightAI );

	SELECT "U_DocNum" INTO v_returnInspectionOrderCode default null FROM "@BOY_CLOUD_INSP_RESO" WHERE "Code" = p_resocode;

    --Return the values
    SELECT	v_returnSSCC SSCC, 	
        IFNULL(v_returnGTIN,'N/A') GTIN,
        IFNULL(v_returnProductDescription,'N/A') ProductDescription, 
        v_returnQuantity AS Quantity,
        IFNULL(v_returnBatchNumber,'N/A') BatchNumber, 
        v_returnBestBeforeDate AS BestBeforeDate,
        v_returnSSCCBarcode SSCCBarcode,
        v_returnSSCCBarcodeDigit SSCCBarcodeDigit,
        v_returnGTINBarcode GTINBarcode,
        v_returnGTINBarcodeDigit GTINBarcodeDigit,
        v_returnGTINLabel GTINLabel,
        v_returnInspectionOrderCode InspectionOrderCode
        FROM dummy;
	
END