-- |                                                                   |
-- | NAME       : Duplicate_invoice_num_script.sql                     |
-- |                                                                   |
-- |                                                                   |
-- | DESCRIPTION: Script to correct duplicate invoice number error     |
-- |              exists in ra_interface_lines_all                     |
-- |                                                                   |
-- To find out duplicate invoice number details

select ril.interface_line_id
              from apps.ra_customer_trx_all rct,
                   apps.ra_customer_trx_lines_all rctl,
                   apps.ra_interface_lines_all ril,
                   apps.ra_interface_errors_all riea
              where rct.customer_trx_id = rctl.customer_trx_id
                and ril.trx_number = rct.trx_number
                and ril.interface_line_attribute2 = rctl.interface_line_attribute2
                and rctl.interface_line_context = ril.interface_line_context
                and rctl.interface_line_context Like  'SKP%'
                and ril.interface_line_id = riea.interface_line_id
                and ril.amount = rctl.unit_selling_price
                and message_text is not null
                and message_text ='Duplicate invoice number'
                ;
-- use the below script to make non duplicate invoice number by appending trailing 'A'				
Update apps.ra_interface_lines_all 
   set trx_number = trx_number||'A'
WHERE  interface_line_id  in (select ril.interface_line_id
                               from apps.ra_interface_errors_all riea,
                                    apps.ra_interface_lines_all ril
                               where riea.interface_line_id = ril.interface_line_id
                                 AND   RIL.INTERFACE_LINE_CONTEXT LIKE 'SKP%'
                                 and message_text ='Duplicate invoice number'
                                AND MESSAGE_TEXT IS NOT NULL
);