Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement