Untitled

mail@pastecode.io avatar
unknown
plain_text
5 months ago
2.5 kB
1
Indexable
Never
import cx_Oracle
import configparser
cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\2009319.INDIA\Documents\ETPI\instantclient_21_6")

coun=cx_Oracle.makedsn(host='192.168.217.206',port='1596',sid='hobs2stdb')
username='hobread'
password='hobread'
conn=cx_Oracle.connect(username,password,coun)
cursor=conn.cursor()

#cx_Oracle.init_oracle_client(lib_dir=r"C:\Users\2009319.INDIA\Documents\ETPI\instantclient_21_6")

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"
invoice_num_cursor = conn.cursor()
invoice_num_cursor.execute(invoice_num_query)
for invoice_num in invoice_num_cursor:
   invoice_num = invoice_num[0]
   bill_lines_query = "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"
   print('')
   bill_lines_cursor = conn.cursor()
   bill_lines_cursor.execute(bill_lines_query)
   if bill_lines_cursor.fetchone():
      print("invoice was successful:"+invoice_num)
   else:
      print("invoice failed:"+invoice_num)
with open("/home/hobread/Yamini/PaymentFailureDataIssue/payment_failures.log", "a") as log_file:
   log_file.write("payment_failures.log")
with open("payment_failures.log", "r") as log_file:
   failed_records_count = sum(1 for line in log_file)
   print("Number of failed records")



in the above bill_lines_query iam passing invoice but its not taking..my incoice num will be like EST-10415893-22 and i want my query as 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 and i want pass invoice in above query