In SAS Merchandise Allocation, incorrect values appear in a Single Size pack (SSP) definition if the definition uses a merchandise set whose To level is lower than the pre-aggregated merchandise level. The application erroneously uses pre-aggregated data rather than regular data.

This problem affects all pre-aggregate KPIs in any SSP definition that uses a merchandise set whose To level is lower than the pre-aggregated merchandise level.

To circumvent this problem, use SQL*Plus and run the modified P_AL_FILL_SKU_ATTR database procedure that is available on the Full Code tab in this note.

The modification of the database procedure P_AL_FILL_SKU_ATTR obtains the regular data instead of the pre-aggregated data. As described previously, this is for an SSP definition if the definition uses a merchandise set whose To level is lower than the pre-aggregated merchandise level.

 

Operating System and Release Information

Product Family	Product	System	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	SAS Merchandise Allocation	64-bit Enabled Solaris	2.8 M2	3.4	9.2 TS2M2	9.4 TS1M0
		64-bit Enabled AIX	2.8 M2	3.4	9.2 TS2M2	9.4 TS1M0

* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.

Run the following database script using SQL*Plus.

---

CREATE OR REPLACE
PROCEDURE           "P_AL_FILL_SKU_ATTR" (
        in_cube_id              NUMBER,
        in_parent_alloc_id      NUMBER,  -- parent allocation_id
        in_future1              NUMBER,  -- placeholder. Pass in -1.
        in_future2              NUMBER,  -- placeholder. Pass in -1.
        in_future3              NUMBER,  -- placeholder. Pass in -1.
        in_debug_flg            NUMBER,  -- debug flag, <=0=off, >0=on
        out_row_count   OUT     NUMBER   -- output param
) AS

   /* ----------------------------------------------------------------------------

   Change History:

   $Log: 136_p_al_fill_sku_attr.sql,v $
   Revision 1.5  2008/09/24 19:02:02  saghai
   S0536456 Handle Full base merch templates

   Revision 1.4  2008/09/18 12:56:04  saghai
   Changed version number from 5.7 to 2.7

   Revision 1.3  2008/09/17 19:03:15  saghai
   S0535841 Only Detail records will be considered

   Revision 1.2  2008/06/27 18:19:06  saghai
   Fix due to Oracle Bug

   Revision 1.1  2008/06/11 13:57:52  saghai
   FIXID : For Allocation Pre-Aggregation



   Usage: Used by the application


   Description:

   This procedure populates the maxdata.t_cube_al_attr table using the attribute data
   in the maxdata.alloc_def table for the given parent_alloc_id.
   The output parameter stores the number of rows that are inserted.
   If no rows are inserted , 0 (zero) is passed to the application.

   ---------------------------------------------------------------------------- */

n_sqlnum                NUMBER(10)      := 1000;
t_proc_name             VARCHAR2(30)    := 'p_al_fill_sku_attr';
t_call                  VARCHAR2(1000);
v_sql                   VARCHAR2(4000)  := NULL;
t_sql2                  VARCHAR2(255);
t_sql3                  VARCHAR2(255);
t_error_level           VARCHAR2(6)     := 'info';

t_base_merch_level      NUMBER(10)      := -1;
t_row_count             NUMBER(10)      := 0;

t_sql                   VARCHAR2(4000)  := NULL;
t_alloc_def_id          NUMBER(10);
t_to_level              NUMBER(6);
t_base_merch_id         NUMBER(10);
t_merch_attr1           NUMBER(10);
t_merch_attr1_value     VARCHAR2(255);
t_merch_attr2           NUMBER(10);
t_merch_attr2_value     VARCHAR2(255);
t_merch_attr_and_or     VARCHAR2(5);

TYPE return_cur IS REF CURSOR;
return_ref_cur return_cur;

BEGIN

-- Log the parameters of the procedure only when debug_flg is set.
-- This procedure is called many times a day.

IF in_debug_flg > 0 THEN

        t_call := t_proc_name                           || ' ( ' ||
                COALESCE(in_cube_id, -123)              || ',' ||
                COALESCE(in_parent_alloc_id, -123)      || ',' ||
                COALESCE(in_future1, -123)              || ',' ||
                COALESCE(in_future2, -123)              || ',' ||
                COALESCE(in_future3, -123)              || ',' ||
                COALESCE(in_debug_flg, -123)            || ',' ||
                'OUT ' || COALESCE(out_row_count, -123)||
                ' ) ';

        maxdata.ins_import_log (t_proc_name, t_error_level, t_call, v_sql, n_sqlnum, NULL);
        COMMIT;
END IF;

n_sqlnum := 2000;
IF (in_cube_id IS NULL) THEN
BEGIN
        v_sql := '"in_cube_id" cannot be null.';
        RAISE_APPLICATION_ERROR (-20001,v_sql);
END;
END IF;

n_sqlnum := 2500;
IF (in_parent_alloc_id IS NULL) THEN
BEGIN
        v_sql := '"in_parent_alloc_id" cannot be null.';
        RAISE_APPLICATION_ERROR (-20001,v_sql);
END;
END IF;


n_sqlnum := 3000;

SELECT value_1
INTO t_base_merch_level
FROM maxapp.userpref
WHERE key_1 = 'ALLOC_PREAGG_BASE_MERCH_LEVEL';


n_sqlnum := 3500;
--First, remove any rows from previous run:
DELETE FROM maxdata.t_cube_al_attr
WHERE cube_id=in_cube_id;
COMMIT;

n_sqlnum := 4000;
-- First take care of case where base_merch_level of the allocation definition is
-- equal to the 'ALLOC_PREAGG_BASE_MERCH_LEVEL'

INSERT INTO maxdata.t_cube_al_attr
(       cube_id,
        alloc_def_id,
        attr_id
)
SELECT
        in_cube_id,
        alloc_def_id,
        attr_id
FROM maxdata.alloc_def ad, maxdata.ALAC_attr_config alac
WHERE ad.parent_alloc_id = in_parent_alloc_id
AND ad.detail_flg = 1
AND
(
        (ad.base_merch_template_id IS NULL
        AND ad.base_merch_level is NOT NULL
        AND ad.base_merch_level = (alac.base_merch_level + 10)
        AND ad.base_merch_level = t_base_merch_level
        AND ad.base_merch_id = alac.base_merch_id
        )
        OR
        (ad.base_merch_template_id IS NOT NULL
        AND (alac.base_merch_level + 10) = t_base_merch_level
        AND (alac.base_merch_id) IN (   SELECT member_id
                                        FROM maxapp.template_member
                                        WHERE template_id = ad.base_merch_template_id
   /* Begin Code Change - Limit template member records to those at the pre-aggregate level. */
                                        AND level_number = (alac.base_merch_level + 10)
   /* End Code Change */
                                        AND level_number = (    SELECT MAX(level_number)
                                                                FROM maxapp.template_level
                                                                WHERE template_id = ad.base_merch_template_id
                                                           )
                                    )
        )
)
AND (   (ad.merch_attr1 = alac.merch_attr1_field_id
        AND REPLACE(ad.merch_attr1_value,'''','') = alac.merch_attr1_value
        AND COALESCE(ad.merch_attr2,-99) = COALESCE(alac.merch_attr2_field_id,-99)
        AND COALESCE(REPLACE(ad.merch_attr2_value,'''',''),'NULL') = COALESCE(alac.merch_attr2_value,'NULL')
        )
    OR
        (ad.merch_attr1 = alac.merch_attr2_field_id
        AND REPLACE(ad.merch_attr1_value,'''','') = alac.merch_attr2_value
        AND COALESCE(ad.merch_attr2,-99) = COALESCE(alac.merch_attr1_field_id,-99)
        AND COALESCE(REPLACE(ad.merch_attr2_value,'''',''),'NULL') = COALESCE(alac.merch_attr1_value,'NULL')
        )
    )
AND (   (ad.merch_attr_and_or = alac.merch_attr_and_or AND ad.merch_attr2 IS NOT NULL)
        OR ad.merch_attr2 IS NULL
    )
;

t_row_count := SQL%ROWCOUNT;

COMMIT;


-- Now to take care of cases in which the base_merch_level of the allocation definition is
-- higher than the 'ALLOC_PREAGG_BASE_MERCH_LEVEL'.

n_sqlnum := 5000;
DECLARE
CURSOR alloc_def_cur IS
SELECT
        alloc_def_id,
        base_merch_level,
        base_merch_id,
        COALESCE(merch_attr1,-99) merch_attr1,
        COALESCE(REPLACE(merch_attr1_value,'''',''),'NULL') merch_attr1_value,
        COALESCE(merch_attr2,-99) merch_attr2,
        COALESCE(REPLACE(merch_attr2_value,'''',''),'NULL') merch_attr2_value,
        merch_attr_and_or
FROM maxdata.alloc_def
WHERE parent_alloc_id = in_parent_alloc_id
AND detail_flg = 1
AND base_merch_template_id IS NULL
AND base_merch_level is NOT NULL
AND base_merch_level < t_base_merch_level
UNION
SELECT
        alloc_def_id,
        level_number base_merch_level,
        member_id base_merch_id,
        COALESCE(merch_attr1,-99) merch_attr1,
        COALESCE(REPLACE(merch_attr1_value,'''',''),'NULL') merch_attr1_value,
        COALESCE(merch_attr2,-99) merch_attr2,
        COALESCE(REPLACE(merch_attr2_value,'''',''),'NULL') merch_attr2_value,
        merch_attr_and_or
FROM maxdata.alloc_def,maxapp.template_member
WHERE parent_alloc_id = in_parent_alloc_id
AND detail_flg = 1
AND base_merch_template_id IS NOT NULL
AND base_merch_template_id = template_id
AND level_number = (    SELECT MAX(level_number)
                        FROM maxapp.template_level
                        WHERE template_id = base_merch_template_id
                   )
AND level_number < t_base_merch_level
;

BEGIN
FOR c_row IN alloc_def_cur
LOOP

        n_sqlnum := c_row.alloc_def_id;

        v_sql :=' INSERT INTO maxdata.t_cube_al_attr '||
                ' (     cube_id, '||
                '       alloc_def_id, '||
                '       attr_id '||
                ' ) '||
                ' SELECT '||
                in_cube_id||','||
                c_row.alloc_def_id||','||
                ' attr_id '||
                ' FROM maxdata.ALAC_attr_config alac '||
                ' WHERE '||
                ' alac.base_merch_id IN (SELECT lv'||(t_base_merch_level - 10)||'ctree_id '||
                                        'FROM maxdata.lv'||(t_base_merch_level - 10)||'ctree '||
                                        'WHERE lv'||COALESCE((c_row.base_merch_level-10),0)||
                                        CASE WHEN (c_row.base_merch_level-10) = 1
                                        THEN 'cmast_id = '
                                        ELSE 'ctree_id = '
                                        END||
                                        c_row.base_merch_id||
                                        ') '||
                ' AND ((alac.merch_attr1_field_id = '||c_row.merch_attr1||
                ' AND COALESCE(alac.merch_attr1_value,''NULL'') = '''||c_row.merch_attr1_value||''''||
                ' AND COALESCE(alac.merch_attr2_field_id,-99) = '||c_row.merch_attr2||
                ' AND COALESCE(alac.merch_attr2_value,''NULL'') = '''||c_row.merch_attr2_value||''')'||
                ' OR (alac.merch_attr1_field_id = '||c_row.merch_attr2||
                ' AND COALESCE(alac.merch_attr1_value,''NULL'') = '''||c_row.merch_attr2_value||''''||
                ' AND COALESCE(alac.merch_attr2_field_id,-99) = '||c_row.merch_attr1||
                ' AND COALESCE(alac.merch_attr2_value,''NULL'') = '''||c_row.merch_attr1_value||'''))'||
                ' AND ((alac.merch_attr_and_or = '''||c_row.merch_attr_and_or||''' AND '||c_row.merch_attr2||' <> -99)'||
                ' OR '||c_row.merch_attr2||' = -99)'
                ;

        IF in_debug_flg > 0 THEN
                t_error_level := 'debug';
                maxdata.ins_import_log (t_proc_name, t_error_level, SUBSTR(v_sql,1,255), SUBSTR(v_sql,256,255), n_sqlnum, NULL);
                maxdata.ins_import_log (t_proc_name, t_error_level, SUBSTR(v_sql,512,255), SUBSTR(v_sql,768,255), n_sqlnum, NULL);
                COMMIT;
        END IF;

        EXECUTE IMMEDIATE v_sql;

        t_row_count := t_row_count + SQL%ROWCOUNT;

        COMMIT;

END LOOP;
END;


-- Now take care of conditions in which the templates
-- are definied as base_merch_template are FULL.

n_sqlnum := 6000;
DECLARE
CURSOR full_templates_cur IS
SELECT
        alloc_def_id,
        from_level,
        from_id,
        to_level,
        COALESCE(merch_attr1,-99) merch_attr1,
        COALESCE(merch_attr1_value,'NULL') merch_attr1_value,
        COALESCE(merch_attr2,-99) merch_attr2,
        COALESCE(merch_attr2_value,'NULL') merch_attr2_value,
        CASE WHEN merch_attr_and_or IS NULL THEN 'NULL' ELSE ''''||merch_attr_and_or||'''' END merch_attr_and_or
FROM maxdata.alloc_def,maxapp.template
WHERE parent_alloc_id = in_parent_alloc_id
AND detail_flg = 1
AND base_merch_template_id IS NOT NULL
AND base_merch_template_id = template_id
AND to_level <= t_base_merch_level
AND 0 = (SELECT MAX(partial_flag)
         FROM maxapp.template_level
         WHERE template_id = base_merch_template_id);
BEGIN
FOR c_row IN full_templates_cur
LOOP
        n_sqlnum := c_row.alloc_def_id;
        BEGIN
        t_sql :=' SELECT '||c_row.alloc_def_id||','
                          ||c_row.to_level||','
                          ||'lv'||(c_row.to_level - 10)||'ctree_id base_merch_id,'
                          ||c_row.merch_attr1||','
                          ||c_row.merch_attr1_value||','
                          ||c_row.merch_attr2||','
                          ||c_row.merch_attr2_value||','
                          ||c_row.merch_attr_and_or||
                ' FROM maxdata.lv'||(c_row.to_level - 10)||'ctree '||
                ' WHERE lv'||COALESCE((c_row.from_level-10),0)||
                                        CASE WHEN (c_row.from_level-10) = 1
                                        THEN 'cmast_id = '
                                        ELSE 'ctree_id = '
                                        END||
                                        c_row.from_id;
        IF in_debug_flg > 0 THEN
                t_error_level := 'debug';
                maxdata.ins_import_log (t_proc_name, t_error_level, SUBSTR(t_sql,1,255), SUBSTR(t_sql,256,255), n_sqlnum, NULL);
                COMMIT;
        END IF;
        OPEN return_ref_cur FOR t_sql;
        WHILE TRUE
        LOOP
                FETCH return_ref_cur INTO t_alloc_def_id,
                                          t_to_level,
                                          t_base_merch_id,
                                          t_merch_attr1,
                                          t_merch_attr1_value,
                                          t_merch_attr2,
                                          t_merch_attr2_value,
                                          t_merch_attr_and_or;
                EXIT WHEN return_ref_cur%NOTFOUND;


                n_sqlnum := t_alloc_def_id;

                v_sql :=' INSERT INTO maxdata.t_cube_al_attr '||
                        ' (     cube_id, '||
                        '       alloc_def_id, '||
                        '       attr_id '||
                        ' ) '||
                        ' SELECT '||
                        in_cube_id||','||
                        t_alloc_def_id||','||
                        ' attr_id '||
                        ' FROM maxdata.ALAC_attr_config alac '||
                        ' WHERE '||
                        ' alac.base_merch_id IN (SELECT lv'||(t_base_merch_level - 10)||'ctree_id '||
                                                'FROM maxdata.lv'||(t_base_merch_level - 10)||'ctree '||
                                                'WHERE lv'||COALESCE((t_to_level-10),0)||
                                                CASE WHEN (t_to_level-10) = 1
                                                THEN 'cmast_id = '
                                                ELSE 'ctree_id = '
                                                END||
                                                t_base_merch_id||
                                                ') '||
                        ' AND ((alac.merch_attr1_field_id = '||t_merch_attr1||
                        ' AND COALESCE(alac.merch_attr1_value,''NULL'') = '''||COALESCE(t_merch_attr1_value,'NULL')||''''||
                        ' AND COALESCE(alac.merch_attr2_field_id,-99) = '||t_merch_attr2||
                        ' AND COALESCE(alac.merch_attr2_value,''NULL'') = '''||COALESCE(t_merch_attr2_value,'NULL')||''')'||
                        ' OR (alac.merch_attr1_field_id = '||t_merch_attr2||
                        ' AND COALESCE(alac.merch_attr1_value,''NULL'') = '''||COALESCE(t_merch_attr2_value,'NULL')||''''||
                        ' AND COALESCE(alac.merch_attr2_field_id,-99) = '||t_merch_attr1||
                        ' AND COALESCE(alac.merch_attr2_value,''NULL'') = '''||COALESCE(t_merch_attr1_value,'NULL')||'''))'||
                        ' AND ((alac.merch_attr_and_or = '''||t_merch_attr_and_or||''' AND '||t_merch_attr2||' <> -99)'||
                        ' OR '||t_merch_attr2||' = -99)'
                        ;

                IF in_debug_flg > 0 THEN
                        t_error_level := 'debug';
                        maxdata.ins_import_log (t_proc_name, t_error_level, SUBSTR(v_sql,1,255), SUBSTR(v_sql,256,255), n_sqlnum, NULL);
                        maxdata.ins_import_log (t_proc_name, t_error_level, SUBSTR(v_sql,512,255), SUBSTR(v_sql,768,255), n_sqlnum, NULL);
                        COMMIT;
                END IF;

                EXECUTE IMMEDIATE v_sql;

                t_row_count := t_row_count + SQL%ROWCOUNT;

                COMMIT;

        END LOOP;

        CLOSE return_ref_cur;
        END;

END LOOP;
END;

out_row_count := t_row_count;

EXCEPTION
        WHEN OTHERS THEN
                ROLLBACK;

                IF v_sql IS NOT NULL THEN
                        t_error_level := 'info';
                        t_sql2 := 'Most recent dynamic SQL. Not necessarily related with the current error';
                        t_sql3 := SUBSTR(v_sql,1,255);
                        maxdata.ins_import_log (t_proc_name, t_error_level, t_sql2, t_sql3, n_sqlnum, NULL);
                END IF;

                -- Log the error message
                t_error_level := 'error';
                v_sql := SQLERRM || ' (' || t_call ||
                                ', SQL#:' || n_sqlnum || ')';

                t_sql2 := SUBSTR(v_sql,1,255);
                t_sql3 := SUBSTR(v_sql,256,255);
                maxdata.ins_import_log (t_proc_name, t_error_level, t_sql2, t_sql3, n_sqlnum, NULL);
                --COMMIT;

                RAISE_APPLICATION_ERROR(-20001,v_sql);
END;

Type:	Problem Note
Priority:	high

Date Modified:	2010-11-17 08:33:19
Date Created:	2010-11-10 16:39:49