Sales Order Details

SELECT wdd.source_header_number order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date,
       mtrh.creation_date move_order_date, wdd.creation_date,
       wdd.source_header_type_name order_type, hp.party_name bill_to_customer,
       hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity
               ) = 0
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPED'
       END AS order_status,
       rct.trx_number, rct.trx_date, rct.status_trx,
       DECODE (wdd.released_status,
               'D', 'Cancelled',
               'C', 'Shipped',
               'R', 'Ready to Release',
               'N', 'Not Ready to Release',
               'S', 'Released to Warehouse',
               'Y', 'Staged/Pick Confirmed',
               'X', 'Not Applicable'
              ) released_status_name,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - mtrh.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status, NULL, NULL
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       ra_customer_trx_lines_all rctl,
       ra_customer_trx_all rct,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
   AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND NVL (wda.TYPE, 'S') IN ('S', 'C')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   ------------following join to get those order whose invoices are not created--------
   AND rctl.interface_line_attribute6(+)=oel.line_id
   AND rct.customer_trx_id(+) = rctl.customer_trx_id
   --AND rctl.interface_line_attribute1 = TO_CHAR (oeh.order_number)
   AND wdd.move_order_line_id = mtrl.line_id
   AND oel.ship_from_org_id = ood.organization_id
   AND mtrh.header_id = mtrl.header_id
   AND wdd.move_order_line_id = mtrl.line_id
    --and wdd.source_header_number=112104873 --112909588 --112400057
    AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)
union
SELECT wdd.source_header_number order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date, mtrh.creation_date move_order_date,
       wdd.creation_date, wdd.source_header_type_name order_type,
       hp.party_name bill_to_customer, hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status<>'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
             WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status='D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status,null,null/*, oer.reason_code, oer.comments*/
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
        hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh   
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
  -- AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
    AND mtrh.header_id = mtrl.header_id
    AND wdd.move_order_line_id =NVL( mtrl.line_id,wdd.move_order_line_id)
  AND wdd.released_status IN ( 'B','S','R')
   --and wdd.source_header_number=112104873 --112909588
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)
   Union
   SELECT wdd.source_header_number order_number,
       NULL move_order_number,
       wnd.global_attribute14 actual_delivery_date,
       NULL move_order_date, wdd.creation_date,
       wdd.source_header_type_name order_type, hp.party_name bill_to_customer,
       hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity
               ) = 0
          AND wdd.released_status <> 'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) = 0
          AND wdd.released_status = 'D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status, NULL,NULL --oer.reason_code, oer.comments
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
   -- AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
   AND wdd.released_status IN   ( 'B','S','R')
   AND wdd.move_order_line_id IS NULL
   --AND wdd.source_header_number = 112104873
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date) 
   UNION
SELECT wdd.source_header_number order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date,
       mtrh.creation_date move_order_date, wdd.creation_date,
       wdd.source_header_type_name order_type, hp.party_name bill_to_customer,
       hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity
       , oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity
               ) = 0
          AND wdd.released_status <> 'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) = 0
          AND wdd.released_status = 'D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status, oer.reason_code, oer.comments
  FROM wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh  ,oe_order_lines_history lh ,                                      
                                               oe_reasons oer
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
   AND wda.delivery_id = wnd.delivery_id(+)
   -- AND NVL (wdd.line_direction, 'O') IN ('O', 'IO')
   AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id = oel.line_id
   AND oeh.header_id = oel.header_id
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
   AND mtrh.header_id(+) = mtrl.header_id
   AND mtrl.line_id(+) = wdd.move_order_line_id
   AND oel.line_id = lh.line_id
    AND lh.reason_id = oer.reason_id
   AND wdd.released_status IN  ('D')
   --AND wdd.source_header_number = 112104873                         --112909588
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)
    UNION       
SELECT TO_CHAR (oeh.order_number) order_number, NULL move_order_number,
       NULL actual_delivery_date, NULL move_order_date, NULL, NULL order_type,
       hp.party_name bill_to_customer, hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       oel.ordered_quantity ordered_quantity, NULL, NULL,
       oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       oel.flow_status_code order_status, NULL, NULL, NULL, NULL, NULL, NULL,
       NULL, NULL
  FROM oe_order_headers_all oeh,
       oe_order_lines_all oel,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hps,
       mtl_system_items_b msi,
       org_organization_definitions ood
WHERE oeh.header_id = oel.header_id(+)--this join for rows whose line level data is not available
   AND hp.party_id = hca.party_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND msi.inventory_item_id(+)=oel.inventory_item_id--this join for rows whose line level data is not available
   AND msi.organization_id(+) = oel.ship_from_org_id--this join for rows whose line level data is not available
   AND oel.ship_from_org_id = ood.organization_id(+)--this join for rows whose line level data is not available
   --  and wdd.RELEASED_STATUS in('D','B')
     --and oeh.ORDER_NUMBER =112105131 --112909588--112104621
    AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                     AND NVL (:t_date, oeh.ordered_date)
      AND oeh.flow_status_code = 'ENTERED' 
      Union
---------this union for order Booked but his transactions are not done in delivery table and for cancelled order
      SELECT to_char(oeh.ORDER_NUMBER)  order_number,
       mtrh.request_number move_order_number,
       wnd.global_attribute14 actual_delivery_date, mtrh.creation_date move_order_date,
       wdd.creation_date, wdd.source_header_type_name order_type,
       hp.party_name bill_to_customer, hps.party_site_number ship_to_loc_num,
       hps.party_site_name ship_to_loc_name, oel.ordered_item,
       msi.description, oel.order_quantity_uom,
       wdd.src_requested_quantity ordered_quantity, wdd.shipped_quantity,
       CASE when oel.SOURCE_DOCUMENT_ID is null then
             wdd.cancelled_quantity
             else 
       null
       end as cancelled_quantity,
        oel.unit_selling_price unit_selling_price,
       (oel.ordered_quantity * oel.unit_selling_price) extended_price,
       oeh.ordered_date, ood.organization_code,
       CASE
          WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status<>'D'
             THEN 'CLOSED'
          WHEN wdd.shipped_quantity IS NULL
             THEN 'AWAITING SHIPPING'
          WHEN wdd.shipped_quantity > 0
          AND wdd.shipped_quantity < wdd.src_requested_quantity
             THEN 'PARTIALLY SHIPPED'
             WHEN (wdd.src_requested_quantity - wdd.shipped_quantity) =
                                                            0 AND wdd.released_status='D'
             THEN 'CANCELLED'
       END AS order_status,
       NULL, NULL, NULL, NULL,
       ROUND (DECODE ((oel.ordered_quantity - wdd.shipped_quantity),
                      0, NULL,
                      TRUNC (SYSDATE) - wdd.creation_date
                     ),
              0
             ) pending_since,
       wdd.released_status,null,null/*, oer.reason_code, oer.comments*/
  FROM oe_order_headers_all oeh,
       oe_order_lines_all oel,
       wsh_delivery_details wdd,
       wsh_delivery_assignments wda,
       wsh_new_deliveries wnd,
       hz_party_sites hps,
       hz_cust_accounts hca,
       hz_parties hp,
       mtl_system_items_b msi,
       org_organization_definitions ood,
       mtl_txn_request_lines mtrl,
       mtl_txn_request_headers mtrh
WHERE oeh.header_id = oel.header_id
--AND wdd.source_header_id = oeh.header_id
   AND wdd.source_line_id(+) = oel.line_id
   AND wda.delivery_id = wnd.delivery_id(+)
   AND wda.delivery_detail_id(+) = wdd.delivery_detail_id
   AND hps.location_id(+) = oel.ship_to_org_id
   AND oeh.sold_to_org_id = hca.cust_account_id
   AND hp.party_id = hca.party_id
   AND msi.inventory_item_id = oel.inventory_item_id
   AND msi.organization_id = oel.ship_from_org_id
   AND oel.ship_from_org_id = ood.organization_id
   AND wdd.move_order_line_id = mtrl.line_id(+)
   AND mtrh.header_id(+) = mtrl.header_id
   AND NVL (wdd.released_status, 'N') IN ('N')
   --AND oeh.order_number = 112909462
  AND oeh.flow_status_code in ( 'BOOKED','CANCELLED')
   AND TO_DATE (oeh.ordered_date) BETWEEN NVL (:f_date, oeh.ordered_date)
                                      AND NVL (:t_date, oeh.ordered_date)

Query To Find Coverage Details in Oracle Service Contracts

/* Formatted on 2014/01/15 18:47 (Formatter Plus v4.8.8) */
SELECT DECODE (coverage1, NULL, 'No Coverage', coverage1) service_coverage,
       DECODE (warranty1, NULL, 'No', warranty1) warranty
  FROM (SELECT MAX (DECODE (cov.NAME,
                            'Servicepass Complete', DECODE (cle.sts_code,
                                                            'ACTIVE', cov.NAME,
                                                            NULL
                                                           ),
                            'Servicepass Standard', DECODE (cle.sts_code,
                                                            'ACTIVE', cov.NAME,
                                                            NULL
                                                           ),
                            'Qualitypass', DECODE (cle.sts_code,
                                                   'ACTIVE', cov.NAME,
                                                   NULL
                                                  ),
                            NULL
                           )
                   ) coverage1,
               MAX (DECODE (cov.NAME,
                            'WARRANTY', DECODE (cle.sts_code,
                                                'ACTIVE', 'Yes',
                                                'No'
                                               ),
                            NULL
                           )
                   ) warranty1
          FROM okc_k_lines_b cle,
               oks_k_lines_b kln,
               okc_k_lines_tl cov,
               okc_line_styles_tl lse,
               okc_statuses_tl sts,
               okc_k_items cit,
               oks_auth_lines_v x
         WHERE cle.cle_id IS NULL
           AND kln.cle_id = cle.ID
           AND lse.ID = cle.lse_id
           AND lse.LANGUAGE = USERENV ('LANG')
           AND cit.cle_id = cle.ID
           AND cit.jtot_object1_code IN
                  ('OKX_SERVICE', 'OKX_WARRANTY', 'OKX_USAGE',
                   'OKS_SUBSCRIPTION')
           AND sts.code = cle.sts_code
--AND sts.meaning NOT IN ('Terminated')
           AND sts.LANGUAGE = USERENV ('LANG')
           AND cov.ID(+) = kln.coverage_id
           AND cov.LANGUAGE(+) = USERENV ('LANG')
           AND x.cle_id = cle.ID
           AND (x.ID IN (
                   SELECT cle_id
                     FROM okc_k_items
                    WHERE dnz_chr_id = cit.dnz_chr_id
                      AND object1_id1 IN (
                             SELECT instance_id
                               FROM csi_item_instances
                              WHERE 1 = 1
                                AND serial_number = NVL (:1, serial_number)))
               ))

Query to Find Responsibility Attached to Request

SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              fcpt.user_concurrent_program_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_concurrent_programs_tl fcpt
        WHERE fr.responsibility_id = frt.responsibility_id
          AND fr.request_group_id = frg.request_group_id
          AND frgu.request_group_id = frg.request_group_id
          AND frgu.request_unit_id = fcpt.concurrent_program_id
          AND fcpt.LANGUAGE = 'US'
          AND frt.LANGUAGE = 'US'
          AND fcpt.user_concurrent_program_name LIKE
                                                '' 

Query to Find Renewed Contracts

Query To Find the Contracts that have been Renewed

/* Formatted on 2014/01/03 14:58 (Formatter Plus v4.8.8) */
SELECT   a.contract_number, a.contract_number_modifier, a.date_renewed,
         a.start_date, a.end_date, a.sts_code,
         DECODE (a.renewal_type_code,
                 'DNR', 'DO NOT RENEW',
                 'NSR', 'MANNUAL'
                ) "RENEWAL TYPE"
    FROM okc_k_headers_all_b a
   WHERE sts_code = 'ACTIVE' AND a.end_date = TRUNC (SYSDATE) + 119
      OR a.start_date = TRUNC (SYSDATE) + 120
ORDER BY contract_number ASC

Query To Find Scheduled Concurrent Request

/*##############################################################################
#        SCHEDULED REQUESTS-
/*############################################################################*/
SELECT r.request_id
     , CASE
          WHEN pt.user_concurrent_program_name = 'Report Set'
             THEN DECODE(
                    r.description
                  , NULL, pt.user_concurrent_program_name
                  ,    r.description
                    || ' ('
                    || pt.user_concurrent_program_name
                    || ')'
                 )
          ELSE pt.user_concurrent_program_name
       END job_name
     , u.user_name requestor
     , u.description requested_by
     , u.email_address
     , frt.responsibility_name requested_by_resp
     , r.request_date
     , r.requested_start_date
     , DECODE(
          r.hold_flag
        , 'Y', 'Yes'
        , 'N', 'No'
       ) on_hold
     , CASE
          WHEN r.hold_flag = 'Y'
             THEN SUBSTR(
                    u2.description
                  , 0
                  , 40
                 )
       END last_update_by
     , CASE
          WHEN r.hold_flag = 'Y'
             THEN r.last_update_date
       END last_update_date
     , r.printer
     , r.number_of_copies print_count
     , r.argument_text PARAMETERS
     , NVL2(
          r.resubmit_interval
        , 'Periodically'
        , NVL2(
             r.release_class_id
           , 'On specific days'
           , 'Once'
          )
       ) AS schedule_type
     , r.resubmit_interval resubmit_every
     , r.resubmit_interval_unit_code resubmit_time_period
     , DECODE(
          r.resubmit_interval_type_code
        , 'START', 'From the start of the prior run'
        , 'END', 'From the Completion of the prior run'
       ) apply_the_update_option
     , r.increment_dates
     , TO_CHAR((r.requested_start_date), 'HH24:MI:SS') start_time
  FROM applsys.fnd_concurrent_programs_tl pt
     , applsys.fnd_concurrent_programs pb
     , applsys.fnd_user u
     , applsys.fnd_user u2
     , applsys.fnd_printer_styles_tl s
     , applsys.fnd_concurrent_requests r
     , applsys.fnd_responsibility_tl frt
 WHERE pb.application_id = r.program_application_id
   AND pb.concurrent_program_id = r.concurrent_program_id
   AND pb.application_id = pt.application_id
   AND r.responsibility_id = frt.responsibility_id
   AND pb.concurrent_program_id = pt.concurrent_program_id
   AND u.user_id = r.requested_by
   AND u2.user_id = r.last_updated_by
   AND s.printer_style_name(+) = r.print_style
   AND r.phase_code = 'P'
   AND 1 = 1;