Untitled

import cx_Oracle

# Set the Oracle connection details
dsn_tns = cx_Oracle.makedsn('<host>', '<port>', service_name='<service_name>')
conn = cx_Oracle.connect(user='<username>', password='<password>', dsn=dsn_tns)

# Set the invoice number query
invoice_num_query = "select invoice_num from rms_billing.rms_bill where invoice_num is not null and bill_due>0 and invoice_num like 'EST-%' and bill_date>=1640975400"

# Execute the invoice number query
invoice_num_cursor = conn.cursor()
invoice_num_cursor.execute(invoice_num_query)

# Loop over the invoice numbers
for invoice_num in invoice_num_cursor:
    invoice_num = invoice_num[0]

    # Set the bill lines query
    bill_lines_query = f"select * from rms_billing.Rms_Billines where bill_Line_Seq_Nbr in((select bill_Line_Seq_Nbr from rms_billing.Rms_Billines where invoice_Num='{invoice_num}' and op_Id ='ETPI' and bu_Id = 'ENTERPRISE' and price_Type is not null group by bill_Line_Seq_Nbr having round(sum(nvl(charges_With_Round,0.00)+ nvl(adjustments,0.00)+nvl(payment_Amount,0.00)+nvl(userfield1,0.00)+nvl(userfield2,0.00)),2) > 0.00 )) and invoice_Num ='{invoice_num}' and op_Id = 'ETPI' and bu_Id ='ENTERPRISE' order by charges_With_Round desc"

    # Execute the bill lines query
    bill_lines_cursor = conn.cursor()
    bill_lines_cursor.execute(bill_lines_query)

    # Check if there are any records
    if bill_lines_cursor.fetchone():
        print(f"Payment for invoice {invoice_num} was successful")
    else:
        print(f"Payment for invoice {invoice_num} failed")

        # Write the payment failure to a log file
        with open("payment_failures.log", "a") as log_file:
            log_file.write(f"{invoice_num}\n")

# Get the count of failed records from the log file
with open("payment_failures.log", "r") as log_file:
    failed_records_count = sum(1 for line in log_file)

print(f"Number of failed records: {failed_records_count}")