Skip to main content

I would like to create a quick report on the Customer Order History which basically lists:

 

Order No

Date Added

Date Released

Date Order Conf Printed

Date Delivered 

UserId

etc.,

 

The problem is that there can be many Order Conf Printed rows for the same order no.

I have tried using Order No, MIN(history_no), Message Text, State etc., but would also want the UserId displayed but as different users may have printed the order confirmation then I could still end up with multiple rows being reported for the same order with the message_text ‘Order confirmation printed’.

 

I did try:

SELECT    c1.order_no, c1.date_entered entered_date, c1.userid entered_userid,

                  c1.hist_state entered_state, c2.date_entered released_date,

                  c2.userid released_userid, c2.hist_state release_state, 
                  c6.date_entered conf_printed_date, c6.userid conf_printed_userid,

                  c6.hist_state conf_printed_state,
                  ABS(to_date(c1.date_entered,'dd/mm/yyyy') - to_date(c6.date_entered,'dd/mm/yyyy')) entered_to_conf_printed_days_taken,
                  c3.date_entered reserved_date, c3.userid reserved_userid, c3.hist_state reserved_state,      
                  c4.date_entered delivered_date, c4.userid delivered_user, c4.hist_state delivered_state, 
                  ABS(to_date(c2.date_entered,'dd/mm/yyyy') - to_date(c4.date_entered,'dd/mm/yyyy')) delivered_days_taken,
                  ROUND((((c2.date_entered - c4.date_entered) * 1440)/60),0)*-1 delivered_hours_taken,
                  c5.date_entered invoiced_date, c5.userid invoiced_user, c5.hist_state invoiced_state,
                  ABS(to_date(c2.date_entered,'dd/mm/yyyy') - to_date(c5.date_entered,'dd/mm/yyyy')) invoiced_days_taken,
                  ROUND((((c2.date_entered - c5.date_entered) * 1440)/60),0)*-1 invoiced_hours_taken,
                  ifsapp.CUSTOMER_ORDER_API.get_objstate(c1.order_no) order_status
FROM ifsapp.CUSTOMER_ORDER_HISTORY c1
LEFT OUTER JOIN ifsapp.CUSTOMER_ORDER_HISTORY c2 ON c1.order_no = c2.order_no AND c2.message_text = 'Released' AND c2.history_no = (SELECT MIN(history_no) FROM ifsapp.CUSTOMER_ORDER_HISTORY h1
WHERE h1.message_text = 'Released' AND h1.order_no = c1.order_no)
LEFT OUTER JOIN ifsapp.CUSTOMER_ORDER_HISTORY c3 ON c1.order_no = c3.order_no AND c3.message_text = 'Reserved' AND c3.history_no = (SELECT MIN(history_no) FROM ifsapp.CUSTOMER_ORDER_HISTORY h2
WHERE h2.message_text = 'Released' AND h2.order_no = c1.order_no)
LEFT OUTER JOIN ifsapp.CUSTOMER_ORDER_HISTORY c4 ON c1.order_no = c4.order_no AND c4.message_text = 'Delivered' AND c4.history_no = (SELECT MIN(history_no) FROM ifsapp.CUSTOMER_ORDER_HISTORY h3
WHERE h3.message_text = 'Released' AND h3.order_no = c1.order_no)
LEFT OUTER JOIN ifsapp.CUSTOMER_ORDER_HISTORY c5 ON c1.order_no = c5.order_no AND c5.message_text = 'Invoiced/Closed' AND c5.history_no = (SELECT MIN(history_no) FROM ifsapp.CUSTOMER_ORDER_HISTORY h4
WHERE h4.message_text = 'Released' AND h4.order_no = c1.order_no)
LEFT OUTER JOIN ifsapp.CUSTOMER_ORDER_HISTORY c6 ON c1.order_no = c6.order_no AND c6.message_text = 'Order confirmation printed' AND c6.history_no = (SELECT MIN(history_no) FROM ifsapp.CUSTOMER_ORDER_HISTORY h5
WHERE h5.message_text = 'Released' AND h5.order_no = c1.order_no)
WHERE  c1.message_text = 'Planned' 
ORDER BY c1.order_no

 

This works in PL/SQL but cannot be used in a view/IAL or Quick Report as the following is received:

 

 

Can anyone please advise.

 

 

I don’t understand your problem.  Are you only looking for the latest order confirmation printed?

Hi,

This error suggests you're running out of TEMP space, and it's because your joins are doing a lot of extra work.
http://www.dba-oracle.com/t_ora_12801_parallel_query.htm

The TO_DATE function is intended to convert a string into a date. If you want just the day portion of a date, it's better to use TRUNC.

There's no index on DATE_ENTERED, but there is one on HISTORY_NO that we can leverage.

I recommend that aliases should be descriptive whenever possible.

Oracle names can only be 30 characters long. (ENTERED_TO_CONF_PRINTED_DAYS_TAKEN is 34.)


Here's a revised query:

 

   SELECT planned_.order_no,
          planned_.date_entered AS entered_date,
          planned_.userid       AS entered_userid,
          planned_.hist_state   AS entered_state,

          released_.date_entered AS released_date,      
          released_.userid       AS released_userid,
          released_.hist_state   AS release_state,

          order_confirmation_printed_.date_entered AS conf_printed_date,
          order_confirmation_printed_.userid       AS conf_printed_userid,

          order_confirmation_printed_.hist_state AS conf_printed_state,
          ABS(TRUNC(planned_.date_entered) - TRUNC(order_confirmation_printed_.date_entered)) AS entered_to_conf_print_in_days,

          reserved_.date_entered AS reserved_date,
          reserved_.userid AS reserved_userid,
          reserved_.hist_state AS reserved_state,

          delivered_.date_entered AS delivered_date,
          delivered_.userid AS delivered_user,
          delivered_.hist_state AS delivered_state,

          ABS(TRUNC(released_.date_entered) - TRUNC(delivered_.date_entered)) AS delivered_days_taken,
          ROUND((((released_.date_entered - delivered_.date_entered) * 1440) / 60), 0) * -1 AS delivered_hours_taken,

          invoiced_closed_.date_entered AS invoiced_date,
          invoiced_closed_.userid AS invoiced_user,
          invoiced_closed_.hist_state AS invoiced_state,

          ABS(TRUNC(released_.date_entered) - TRUNC(invoiced_closed_.date_entered)) AS invoiced_days_taken,
          ROUND((((released_.date_entered - invoiced_closed_.date_entered) * 1440) / 60), 0) * -1 AS invoiced_hours_taken,

          ifsapp.customer_order_api.get_objstate(planned_.order_no) AS order_status

     FROM (SELECT coh_.order_no,
                  MIN(coh_.date_entered) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS date_entered,
                  MIN(coh_.userid      ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS userid,
                  MIN(coh_.hist_state  ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS hist_state
             FROM ifsapp.customer_order_history coh_
            WHERE coh_.message_text = 'Planned'
         GROUP BY coh_.order_no) planned_

LEFT JOIN (SELECT coh_.order_no,
                  MIN(coh_.date_entered) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS date_entered,
                  MIN(coh_.userid      ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS userid,
                  MIN(coh_.hist_state  ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS hist_state
             FROM ifsapp.customer_order_history coh_
            WHERE coh_.message_text = 'Released'
         GROUP BY coh_.order_no) released_
       ON released_.order_no = planned_.order_no

LEFT JOIN (SELECT coh_.order_no,
                  MIN(coh_.date_entered) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS date_entered,
                  MIN(coh_.userid      ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS userid,
                  MIN(coh_.hist_state  ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS hist_state
             FROM ifsapp.customer_order_history coh_
            WHERE coh_.message_text = 'Reserved'
         GROUP BY coh_.order_no) reserved_
       ON reserved_.order_no = planned_.order_no

LEFT JOIN (SELECT coh_.order_no,
                  MIN(coh_.date_entered) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS date_entered,
                  MIN(coh_.userid      ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS userid,
                  MIN(coh_.hist_state  ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS hist_state
             FROM ifsapp.customer_order_history coh_
            WHERE coh_.message_text = 'Delivered'
         GROUP BY coh_.order_no) delivered_
       ON delivered_.order_no = planned_.order_no

LEFT JOIN (SELECT coh_.order_no,
                  MIN(coh_.date_entered) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS date_entered,
                  MIN(coh_.userid      ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS userid,
                  MIN(coh_.hist_state  ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS hist_state
             FROM ifsapp.customer_order_history coh_
            WHERE coh_.message_text = 'Invoiced/Closed'
         GROUP BY coh_.order_no) invoiced_closed_
       ON invoiced_closed_.order_no = planned_.order_no

LEFT JOIN (SELECT coh_.order_no,
                  MIN(coh_.date_entered) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS date_entered,
                  MIN(coh_.userid      ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS userid,
                  MIN(coh_.hist_state  ) KEEP (DENSE_RANK FIRST ORDER BY coh_.history_no ASC) AS hist_state
             FROM ifsapp.customer_order_history coh_
            WHERE coh_.message_text = 'Order confirmation printed'
         GROUP BY coh_.order_no) order_confirmation_printed_
       ON order_confirmation_printed_.order_no = planned_.order_no



 

Edited to change JOINs to LEFT JOINs. (Without this fix, it only picks up invoiced orders.)

 Oracle 12 allows for 60 characters in object and column names.

 Oracle 12 allows for 60 characters in object and column names.

Thanks. I see now that they’re on Apps 10.

12cR1 only supports 30.

12cR2 goes up to 128.

Reply


Terms & ConditionsCookie settings