Untitled
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