Untitled

--####################################################################################################################################################
  -- NOTE: when changing this query make sure to apply changes also to pkrema_esort_art.getDiscOnParentNodeExisItem since it is practicaly same query
  --####################################################################################################################################################
  /*CURSOR C_ESM_DISC_CANDIDATES(i_root       IN VARCHAR2,
                               i_lv         IN VARCHAR2,
                               i_dir_deliv  IN NUMBER,
                               i_re_opening IN NUMBER) IS*/
    SELECT pricederivationid refcexrem,
           description refcnuf,
           NVL(concurrencecode, -1) refcnum,
           businessunit refsite,
           root refcexr,
           lv refcexvl,
           '0' refcexgat,
           1 refregl,
           2 refglob,
           NVL((SELECT parpost
               FROM   rema_parameters t
               WHERE  t.code_nr = 'REFUREM'
               AND    t.tekst = (CASE
                        WHEN pricederivationid LIKE '%W' THEN
                         SUBSTR(pricederivationid, 1, LENGTH(pricederivationid) - 1)
                        ELSE
                         pricederivationid
                      END)),
               -1) refurem,
           (CASE
             WHEN pricederivationid LIKE '%W' THEN
              181
             ELSE
              NULL
           END) refuapp,
          NVL(CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpricepercent END,CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpriceamount END) refrepa,
          NVL(CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpricepercent END,CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpriceamount END) refrepr,
          NVL(CASE WHEN pricederivationid LIKE '910S%' THEN -retailpricepercent ELSE retailpricepercent END,CASE WHEN pricederivationid LIKE '910S%' THEN -retailpriceamount ELSE retailpriceamount END) refrepv,
          NVL(CASE WHEN pricederivationid LIKE '910S%' THEN -retailpricepercent ELSE retailpricepercent END,CASE WHEN pricederivationid LIKE '910S%' THEN -retailpriceamount ELSE retailpriceamount END) refrepf,
          --NVL(CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpricepercent END, CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpriceamount END) refrepf,
          NVL(CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpricepercent END, CASE WHEN pricederivationid LIKE '910%' THEN 0 WHEN pricederivationid LIKE 'W%' THEN 0 WHEN pricederivationid LIKE 'J%' THEN 0 WHEN pricederivationid LIKE 'S%' THEN 0 WHEN pricederivationid LIKE 'B%' THEN 0 WHEN pricederivationid LIKE 'E%' THEN 0 ELSE retailpriceamount END) refreps,
           GREATEST(NVL(TO_DATE(SUBSTR(validfrom, 0, 10), 'RRRR-MM-DD'), TRUNC(CURRENT_DATE + 1)),
                    TRUNC(CURRENT_DATE) + 1) refddeb,
           TO_DATE(NVL(SUBSTR(expirationdate, 0, 10), '2049-12-31'), 'RRRR-MM-DD') refdfin,
           1 refact,
           5 refflag,
           0 reftrt,
           TRUNC(CURRENT_DATE) refdtrt,
           CURRENT_DATE refdcre,
           CURRENT_DATE refdmaj,
           'ESM' refutil,
           'ESM_ART_01-' || TO_CHAR(SYSDATE, 'RRRRMMDDhh24miss') || '_' || root || '_' || lv reffich,
           ROWNUM refnlig,
           NVL(CASE WHEN pricederivationid LIKE '910S%' THEN -retailpricepercent ELSE retailpricepercent END,CASE WHEN pricederivationid LIKE '910S%' THEN -retailpriceamount ELSE retailpriceamount END) refrepm


           ---------------- INFO COLS FOR FURTHER DATA PROCESSING ---------------
          ,
           prof network_level,
           pkartvl.Get_Arlseqvl(0, lv, pkartrac.Get_Artcinr(0, root)) seqvl,
           pkfoudgene.get_CFIN(0, description) cfin,
           pkfouccom.get_CCIN(0, NVL(concurrencecode, -1)) ccin,
           pkrema_esort_art.getDiscOnParentNodeExisItem(num_log        => 0,
                                                      i_bussinesunit => businessunit,
                                                      i_root         => root,
                                                      i_lv           => lv,
                                                      i_cexrem       => pricederivationid,
                                                      i_dir_deliv    => 0/*i_dir_deliv*/,
                                                      i_re_opening   => 0/*i_re_opening*/) parentNodeDisc
    FROM   (SELECT DISTINCT e3.priceruleid        AS priceruleid,
                            e3.pricederivationid  AS pricederivationid,
                            e1.description        AS description,
                            e1.concurrencecode    concurrencecode,
                            resPERE               businessunit,
                            a.root                root,
                            a.lv                  lv,
                            e3.retailpricepercent AS retailpricepercent,
                            e3.retailpriceamount  AS retailpriceamount,
                            a.validfrom           AS validfrom,
                            e1.expirationdate     AS expirationdate,
                            resPROF               prof
            FROM   esort_art_xml a, esort_art_pp_xml e1, esort_art_pp_xml e3, reseau, rema_parameters t
            WHERE  (a.process_type IN ('price', 'year') OR
                   (0/*i_re_opening*/ != 0 AND a.process_type IN ('assortment')))
            AND    e1.record_type = 1
            AND    e3.record_type = 3
            AND    a.lv_type = 'ItemID'
            AND    a.root = '10073839'--i_root
            AND    e1.root = a.root
            AND    e3.root = e1.root
            AND    a.lv = 1--i_lv
            AND    e1.lv = a.lv
            AND    e3.lv = e1.lv
            AND    a.sv IS NOT NULL
            AND    e1.businessunit = e3.businessunit
                  -- in case records have validFrom date information, take into account only those. In case no validFrom date is available take also null validfrom data
            AND    (CASE
                    WHEN a.validfrom IS NULL THEN
                     DECODE((SELECT 1
                            FROM   esort_art_xml i
                            WHERE  (i.process_type IN ('price', 'year') OR
                                   (0/*i_re_opening*/ != 0 AND i.process_type IN ('assortment')))
                            AND    i.lv_type = 'ItemID'
                            AND    i.root = a.root
                            AND    i.lv = a.lv
                            AND    i.sv IS NOT NULL
                            AND    i.validfrom IS NOT NULL
                            AND    ROWNUM = 1),
                            1,
                            NULL,
                            '1') --case validFrom date line exists in esort_art_xml skip lines that have validFrom blank (null), else take them into account
                    ELSE
                     a.validfrom
                  END) IS NOT NULL
                  --
            AND    t.code_nr = 'REFTREM'
            AND    t.tekst = (CASE
                     WHEN e3.pricederivationid LIKE '%W' THEN
                      SUBSTR(e3.pricederivationid, 1, LENGTH(e3.pricederivationid) - 1)
                     ELSE
                      e3.pricederivationid
                   END)
            AND    e1.businessunit = resSITE
                  --Current rule to determine the bussinesUnit site/node
            AND    resPERE = (CASE
                     WHEN 0/*i_dir_deliv*/ = 1 AND e1.businessunit > 8000 THEN
                      e1.businessunit - 8000
                     WHEN 0/*i_dir_deliv*/ = 0 AND e1.businessunit = 8006 AND t.parpost BETWEEN 1 AND 199 THEN
                      1
                     WHEN 0/*i_dir_deliv*/ = 0 AND e1.businessunit = 8006 AND t.parpost BETWEEN 200 AND 299 THEN
                      2
                     WHEN e1.businessunit > 8000 THEN
                      e1.businessunit - 8000
                     ELSE
                      e1.businessunit
                   END)
            ORDER  BY pricederivationid, prof);
    ORDER  BY root, lv;