Calculation of Shipping Charge

 Flow

 

Queries to calculate shipping charge


Queries step by step

-- Get the calcode for the catentry
select calcode_id from catencalcd where catentry_id = 12345 and code = 'Your Shipping Code'; -- say it returns, 11001

-- Pass the calcode_id from the previous step here
select calrule_id from calrule where calcode_id in (11001); -- say it returns, 21001, 31001 etc

-- Pass the calrule_id from the previous step here, also pass the shipmode_id
select calrule_id from shpjcrule where calrule_id in (21001) and shipmode_id = 18790; -- say it returns, 31001

-- Pass the calrule_id from the previous step here
select * from crulescale where calrule_id in (31001); -- say it returns, 41001

-- Pass the calscale_id from the previous step here
select * from calrange where calscale_id in (41001); -- say it returns, 51001

-- Pass the calrange_id from the previous step here
select * from calrlookup where calrange_id in (51001); -- say it returns, 61001

The complete query

select * from calrlookup where calrange_id in (
  select calrange_id from calrange where calscale_id in (
    select calscale_id from crulescale where calrule_id in (
      select calrule_id from shpjcrule where calrule_id in (
        select calrule_id from calrule where calcode_id in (
          select calcode_id from calcode where calcode_id in (
            select calcode_id from catencalcd where catentry_id = 12345
            ) and code = '1$ Code'
          )
        ) and shipmode_id = 11552
      )
    )
  );