SQL query

usppodivximf00(usoponshximf00):/vol01/sites/provisioning/IG/46762> cat 46762.sql


SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 20000
SET FEEDBACK OFF
SET NEWPAGE NONE
SET UNDERLINE OFF


SPOOL /vol01/sites/provisioning/IG/46762/OBTM_Report.csv

SELECT  req.request_sak, trim(req.vin) as vin_suffix,
req.account_number  ,
         req.lineitem_name  as request_type,
         req.payment_type  as payment_type,
         req.transaction_date  as transaction_date,
         req.payment_date  as payment_date,
         req.product_marketing_id as package_id,
          req.product_description,
         req.total_price as payment_amount,
         req.total_currency  as payment_currency,
         req.credit_card_number ,
         NVL (oor.status, 'Failed') as status,
         req.created_timstm as run_date,
         NVL (des.description,
              NVL2 (ipa.response_reason,
                    'VIN: ' || ipa.vin || ' - ' || ipa.response_reason,
                    ''
                   )
             )  as error_description
    FROM (SELECT oor_stg.request_sak, oor_stg.vin, oor_stg.account_number,
                 rb_stg.transaction_date, oor_stg.payment_type,
                 oor_stg.payment_date, ool_stg.product_marketing_id,    ool_stg.product_description,
                 ool_stg.total_price, ool_stg.total_currency,
                 SUBSTR (oor_stg.credit_card_number,
                         -4) AS credit_card_number, oor_stg.created_timstm,
                 ool_stg.lineitem_name
            FROM igappstg.obtm_order_request oor_stg,
                 igappstg.renewal_body rb_stg,
                 igappstg.obtm_order_lineitem ool_stg
           WHERE oor_stg.body_sak = rb_stg.body_sak
             AND oor_stg.request_sak = ool_stg.request_sak(+)
             AND trunc( oor_stg.created_timstm)  = trunc(sysdate)
         ) req,
         igappprod.obtm_order_request oor,
         igappprod.ig_prm_activity ipa,
         igappprod.datafeed_error_skip des
   WHERE req.request_sak = oor.request_sak(+)
     AND req.vin = NVL (SUBSTR (ipa.vin(+), -8), 0)
     AND req.request_sak = des.sak(+)
     AND 'OBTM_ORDER_REQUEST' = des.table_name(+)
     AND 'ProcessOBTMFeedOrchestration' = ipa.bpel_name(+)
     AND 'Closed.HardFailure' = ipa.status(+)
     and  req.lineitem_name in (  'HFC','Renewal');

SPOOL OFF