Sub sales orders ()
Dim strConn As String
Dim dbconn As Object
Dim resSet As Object
The Set dbconn=CreateObject (" ADODB. Connection ")
The Set resSet=CreateObject (" ADODB. You ")
StrConn="Driver={Oracle in Oracleinstantclient_12_2}; User id=XXXXXXX. Password=XXXXXXXX; Data source=atiprod; Persist Security info=True "
Dbconn.Com mandTimeout=380
Dbconn. Open strConn
SQLS="select CZH order_number, CZ. Line_number, CZ. Ordered_item, CZH. Cust_po_number," & amp; _
"CZ. Customer_job MAC sales orders, MAC cust_po_number end customer PO, CZ. Ordered_quantity," & amp; _
"Hz_parties party_name, & amp;" _
"B_use. Address1 | |" | | b_use. Address2 | | "| | b_use. Address3 | |" | | b_use. Address4 | | "| | b_use. City | |" | | b_use. Postal_code | | "| | b_use. The state | |" | | b_use. Province | | "| | b_use. Country invoice address," & amp; _
"S_use. Address1 | |" | | s_use. Address2 | | "| | s_use. Address3 | |" | | s_use. Address4 | | "| | s_use. City | |" | | s_use. Postal_code | | "| | s_use. The state | |" | | s_use. Province | | "| | s_use. Country shipping address," & amp; _
"CZ order_quantity_uom, CZ. Unit_selling_price, CZH transactional_curr_code, CZ. Request_date, CZ. Promise_date," & amp; _
"CZ. Schedule_ship_date, CZ ship_to_org_id, CZ. Invoice_to_org_id, CZ. User_item_description, CZ. Booked_flag" & amp; _
"From oe_order_lines_all CZ, oe_order_headers_all CZH, oe_order_headers_all FRE, hz_parties, hz_cust_site_uses_all ship_use," & amp; _
"Hz_cust_site_uses_all bill_use hz_cust_accounts, hz_cust_acct_sites_all ssite, hz_cust_acct_sites_all bsite," & amp; _
"Hz_party_sites SHPS, hz_party_sites BHPS, hz_locations s_use, hz_locations b_use" & amp; _
"Where hz_parties. Party_id=hz_cust_accounts. Party_id and hz_cust_accounts. Cust_account_id=ssite. Cust_account_id" & amp; _
"And hz_cust_accounts. Cust_account_id=bsite. Cust_account_id and ssite. Party_site_id=SHPS. Party_site_id" & amp; _
"AND bsite. Party_site_id=BHPS. Party_site_id AND s_use, location_id,=SHPS, location_id, AND b_use, location_id,=BHPS, location_id," & amp; _
"AND ship_use. Cust_acct_site_id=ssite. Cust_acct_site_id AND bill_use. Cust_acct_site_id=bsite. Cust_acct_site_id" & amp; _
"And CZ.org _id=2251 and MAC order_number (+)=CZ. Customer_job and CZH. Sold_to_org_id=hz_cust_accounts. Cust_account_id" & amp; _
"And CZ. Ship_to_org_id=ship_use. Site_use_id and CZ. Invoice_to_org_id=bill_use. Site_use_id and CZH. Header_id=CZ. Header_id" & amp; _
"And CZ. Flow_status_code not in (' CLOSED ', 'CANCELLED') and CZH. Order_number='515007007' and CZ. Line_type_id not in (' 1966 ')" & amp; _
"And CZ. Invoice_to_org_id not in (' 455804 ') order by CZ. Schedule_ship_date, CZH. Order_number, CZ. Line_number;"
The Set x=dbconn. Execute (SQLS)
Sheet1. Range (" a1 "). CopyFromRecordset x
End Sub
CodePudding user response:
https://blog.csdn.net/chengjj2001/article/details/50075815The code is only one explanation, inconsistent character set!
As to how to solve, it is recommended that you study, the CSDN on introduce a lot of
Usage scenario, you didn't do it, no comment