0

I need to charge the customer if he matches one pricing system or two or more pricing systems from CONCAT_REL table and then come up with the pricing system.

For e.g. Rahul wants an airway mode AND sending a box AND also needs acknowledgement, then he needs to be tagged to PRICE_SYSTEM1 AND PRICE_SYSTEM3

If Rajkumar wants an rail_road mode AND sending a container that needs to delivered to a house AND delivery_ph is not to call, then he needs to be tagged to PRICE_SYSTEM1

If Ashok is sending a fragile item that needs to delivered to a house AND the delivery_ph is text OR delivery_type is office, then he needs to be tagged to PRICE_SYSTEM2

How to write this scenario in dynamic PL-SQL programming ? Could the experts help in this regard ? Thank you !!!

Please find the table structures below.

CONCAT_REL table 

    UNIQUE_MET                     UNIQUE_EXPRESION
    METHOD1        SHIPMENT_MODE = 'AIRWAY' AND TYP = 'BOX' OR TYP = 'ENVELOPE'
    METHOD2        SHIPMENT_MODE = 'RAIL_ROAD' AND TYP = 'CONTAINER' AND DELIVERY_PH <> 'CALL'AND DELIVERY_TYP = 'HOUSE'        
    METHOD3        TYP = 'FRAGILE' AND DELIVERY_PH = 'TEXT' OR DELIVERY_TYP = 'OFFICE'
    METHOD4            ACKNOWLDGE              =         YES

CON_CHK table

       PRICING           UNIQUE_MET
    PRICE_SYSTEM1         METHOD1
    PRICE_SYSTEM1         METHOD2
    PRICE_SYSTEM2         METHOD3
    PRICE_SYSTEM3         METHOD4

USER_PRICNG

      USERS                PRICING
      Rahul             PRICE_SYSTEM1
      Rahul             PRICE_SYSTEM3
     Rajkumar           PRICE_SYSTEM1
      Ashok             PRICE_SYSTEM2
Cool_Oracle
  • 311
  • 1
  • 4
  • 15
  • I am not sure that I see how this is different than your earlier question https://stackoverflow.com/questions/69003429/oracle-pl-sql-using-multiple-methods-conditions-and-generate-pricing-mechanism that was closed as needing more focus. It would be better to edit the earlier question to focus in on the specific problem you're having (anything that talks about an "entire scenario" is probably too broad). Example code, any errors you're getting, sample data, and expected output would be very helpful. – Justin Cave Sep 01 '21 at 06:52
  • You got the conditions right there. Would you be able to write the SQL? If so, are you stuck on the dynamic, PL/SQL part? Where did that criterium come from anyway; are solutions invalid if they are are just a single parameterized query? – GolezTrol Sep 01 '21 at 07:55
  • @GolezTrol Yes, I was unable to write the queries using dynamic SQL / procedure. The criteria are already given by business. The problem is I need to pick two methods for one pricing systems and then stamp them to a user to create an invoice for him. – Cool_Oracle Sep 01 '21 at 08:37

0 Answers0