full_dump.sql

SELECT
acc.a_own_ctry_cd AS Owner_Country_Code,
acc.a_own_srv_area_cd AS Account_Owner_Station,
ty.a_acc_purpose_cd AS Account_Purpose,
ty.a_accty_cd AS Account_Type,
ty.a_sal_chn AS Sales_Channel,
ty.a_mst_sal_chn AS Master_Sales_Channel,
acc.a_accno AS Account_Number,
c.a_cust_nm_ext AS Account_Name_Long,
acc.a_acc_nm AS Account_Name_Short,
IB.a_cross_border_bil AS Cross_Border_Billing,
DICO.a_reg_ty_cd AS Legal_Identifier_DICO_Registration_Code,
DICO.a_tax_pay_rgs_cd AS Legal_Identifier_DICO_Legal_Identifier_ID__Number,
DICO.a_tax_empt_flg AS Legal_Identifier_DICO_Legal_Identifier_Exempted,
DICO.a_tax_rgs_ctry AS Legal_Identifier_DICO_Registration_Country,
CRID.a_reg_ty_cd AS Legal_Identifier_ICO_Registration_Code,
CRID.a_tax_pay_rgs_cd AS Legal_Identifier_ICO_Legal_Identifier_ID__Number,
CRID.a_tax_empt_flg AS Legal_Identifier_ICO_Legal_Identifier_Exempted,
CRID.a_tax_rgs_ctry AS Legal_Identifier_ICO_Registration_Country,
acc.a_acc_act_st AS Account_Activity_Status,
acc.a_acc_st AS Account_Status,
acc.a_acc_act_st AS Account_Activity_Status,
acc.a_acc_cls_rsn AS Reason_to_Close_Account,
acc.a_acc_cls_dt AS Account_Closure_Date,
acc.a_acc_opn_dt AS Account_ReOpening_Date,
IB.a_ib_inv_hld_flg AS Inbound_Invoice_Handling,
IB.a_ob_inv_hld_flg AS Outbound_Invoice_On_Hold,
pmth.a_pay_mth_ty AS Payment_Method,
acc.a_on_wtch_lst_flg AS On_Watchlist,
IB.a_vrtl_accno AS Virtual_Account_Number,
IB.a_inv_premt_mth AS Invoice_Presentment_Method,
IB.a_ib_shp_omit_flg AS Inbound_Shipment_Omitted,
acc.a_acc_crdt_st AS Credit_Status,
acc.a_crdt_stp_rsn AS Account_Credit_Stop_Reason,
acc.a_crdt_stp_dt AS Credit_Stop_Date,
IB.a_last_atv_dt AS Date_of_Last_Activity,
c.a_sal_trt_cd AS Sales_Territory_Code,
c.a_prnt_cust_id AS Parent_Customer_ID,
c.a_acc_id AS Account_ID,
acc.a_lc_srv_area_cd AS Local_Owner_Station,
acc.a_srva_fc_cd AS Service_Facility_Code,
c.a_cust_id AS Customer_ID,
acc.a_del_flg AS Account_Selected_for_Deletion,
COM.a_dptm AS COM_Department_Address,
COM.a_unt_no AS COM_Unit_Number,
COM.a_flr_no AS COM_Floor,
COM.a_bld AS COM_Building_Name,
COM.a_ngbhr AS COM_Neighbourhood,
COM.a_dist AS COM_DistrictSubprovince,
COM.a_hse_no AS COM_House_Number,
COM.a_str_nm AS COM_Street_Name,
COM.a_po_no AS COM_PO_Box_Number,
COM.a_zip AS COM_Zipcode,
COM.a_cty AS COM_City,
COM.a_state AS COM_StateProvince,
COM.a_ctry_cd AS COM_Country,
COM.a_addr1 AS COM_Address_Line_1,
COM.a_addr2 AS COM_Address_Line_2,
COM1.a_rt_id AS COM_Courier_Route_Code,
COM2.a_cnt_dptm AS COM_Department_Contact,
COM2.a_jb_ttl AS COM_Job_Title,
COM2.a_slttn AS COM_Salutation,
COM2.a_fst_nm AS COM_First_Name,
COM2.a_fm_nm AS COM_Last_Name,
COM2.a_cnt_ln AS COM_Contact_Line,
COM2.a_cnt_lang AS COM_Contact_Person_Language,
COM2.a_tel_no AS COM_Phone_No,
COM2.a_tel_ext AS COM_Phone_No_Ext,
COM2.a_mob_no AS COM_Mobile_Phone_No,
COM2.a_fax_no AS COM_Fax_Number,
COM2.a_eml AS COM_Email_Address,
BIL1.a_cnt_nm AS Billing_Contact_Name,
BIL.a_dptm AS BIL_Department_Address,
BIL.a_unt_no AS BIL_Unit_Number,
BIL.a_flr_no AS BIL_Floor,
BIL.a_bld AS BIL_Building_Name,
BIL.a_ngbhr AS BIL_Neighbourhood,
BIL.a_dist AS BIL_DistrictSubprovince,
BIL.a_hse_no AS BIL_House_Number,
BIL.a_str_nm AS BIL_Street_Name,
BIL.a_po_no AS BIL_PO_Box_Number,
BIL.a_zip AS BIL_Zipcode,
BIL.a_cty AS BIL_City,
BIL.a_state AS BIL_StateProvince,
BIL.a_ctry_cd AS BIL_Country,
BIL2.a_addr_cust_nm AS BIL_Customer_Name_Shown_on_Invoice,
BIL.a_addr1 AS BIL_Address_Line_1,
BIL.a_addr2 AS BIL_Address_Line_2,
BIL1.a_rt_id AS BIL_Courier_Route_Code,
BIL2.a_cnt_dptm AS BIL_Department_Contact,
BIL2.a_jb_ttl AS BIL_Job_Title,
BIL2.a_slttn AS BIL_Salutation,
BIL2.a_fst_nm AS BIL_First_Name,
BIL2.a_fm_nm AS BIL_Last_Name,
BIL2.a_cnt_ln AS BIL_Contact_Line,
BIL2.a_cnt_lang AS BIL_Contact_Person_Language,
BIL2.a_tel_no AS BIL_Phone_No,
BIL2.a_tel_ext AS BIL_Phone_No_Ext,
BIL2.a_mob_no AS BIL_Mobile_Phone_No,
BIL2.a_fax_no AS BIL_Fax_Number,
BIL2.a_eml AS BIL_Email_Address,
cOB.a_ccl_id AS Outbound_Invoice_Cycle,
cOB.a_strt_dt AS Outbound_Invoice_Cycle_Start_Date,
cOB.a_end_dt AS Outbound_Invoice_Cycle_End_Date,
cIB.a_ccl_id AS Inbound_Invoice_Cycle,
cIB.a_strt_dt AS Inbound_Invoice_Cycle_Start_Date,
ty.a_acc_bil_ty AS Billing_Type_of_Customer_Account,
IB.a_inv_dt_var AS Invoice_Date_Variable,
acc.a_crdt_lmt AS Credit_Limit,
ty.a_pay_trm_ds AS Payment_Terms,
ty.a_cur_cd AS Billing_Currency,
IB.a_inv_hrdcpy_flg AS Print_an_Invoice_Hardcopy,
rel.a_to_accno AS Parent_Account_Number,
acc.a_prnt_chld_cd AS Account_Hierarchy,
IB.a_inv_pre_cd AS Invoice_Presentation,
IB.a_ib_recv_tx_flg AS Interface_Tax_Payer_Registration,
IB.a_ob_info AS External_Billing_Agreement_Setup,
cfg.a_mltpl_prod_ovrd_flg AS SingleMulti_Product_Override,
cfg.a_acc_sur_exmt_flg AS Extra_Charge_Exempted_on_Invoice,
IB.a_lst_inv_dt AS Last_Invoice_Date,
IB.a_inv_hdr_ref_cd AS Invoice_Label,
acc.a_bil_ctry_cd AS Billing_Country_Code,
IB.a_gcl_flg AS EDI_Interface,
cfg.a_invtmpl_id AS Invoice_Template_ID,
acc.a_ob_pod_flg AS Proof_of_Delivery_OB,
acc.a_ib_pod_flg AS Proof_of_Delivery_IB,
IB.a_man_shpr_ref AS Shipper_Reference_Required,
IB.a_shpr_ref AS Shippers_Reference_Report,
IB.a_sp_hndl_flg AS Special_Invoice_Handling,
IB.a_ib_bil_flg AS Special_Inbound_Billing_Setup,
IB.a_shpr_rstr_cd AS Restrictive_Shipper_Reference,
IB.a_inv_ar_flg AS ProForma_Invoice_Confirmation,
cfg.a_reg_inv_pr_acc_flg AS Invoice_per_Page_Regular,
cfg.a_nt_inv_pr_acc_flg AS Invoice_per_Page_Credit__Debit_Note,
cfg.a_csh_inv_pr_pg_flg AS Invoice_per_Page_Cash,
cfg.a_imp_inv_pr_pg_flg AS Invoice_per_Page_Import,
cfg.a_tc_inv_pr_acc_flg AS Invoice_per_Page_Transport_Collect,
cfg.a_reg_inv_pr_awb_flg AS Invoice_per_AWB_Regular,
cfg.a_nt_inv_pr_awb_flg AS Invoice_per_AWB_Credit__Debit_Note,
cfg.a_csh_inv_pr_awb_flg AS Invoice_per_AWB_Cash,
cfg.a_imp_inv_pr_awb_flg AS Invoice_per_AWB_Import,
cfg.a_tc_inv_pr_awb_flg AS Invoice_per_AWB_Transport_Collect,
cfg.a_reg_inv_pr_shpref_flg AS Invoice_per_Shipper_Reference_Regular,
cfg.a_nt_inv_pr_shpref_flg AS Invoice_per_Shipper_Reference_Credit__Debit_Note,
cfg.a_csh_inv_pr_shpref_flg AS Invoice_per_Shipper_Reference_Cash,
cfg.a_imp_inv_pr_shpref_flg AS Invoice_per_Shipper_Reference_Import,
cfg.a_tc_inv_pr_shpref_flg AS Invoice_per_Shipper_Reference_Transport_Collect,
cfg.a_reg_inv_pr_ctrycd AS Invoice_per_Country_Code_Regular,
cfg.a_nt_inv_pr_ctrycd AS Invoice_per_Country_Code_Credit__Debit_Note,
cfg.a_csh_inv_pr_ctrycd AS Invoice_per_Country_Code_Cash,
cfg.a_imp_inv_pr_ctrycd AS Invoice_per_Country_Code_Import,
cfg.a_tc_inv_pr_ctrycd AS Invoice_per_Country_Code_Transport_Collect,
cfg.a_reg_inv_pr_shp_mth AS Invoice_per_Shipment_Month_Regular,
cfg.a_nt_inv_pr_shp_mth AS Invoice_per_Shipment_Month_Credit__Debit_Note,
cfg.a_csh_inv_pr_shp_mth AS Invoice_per_Shipment_Month_Cash,
cfg.a_imp_inv_pr_shp_mth AS Invoice_per_Shipment_Month_Import,
cfg.a_tc_inv_pr_shp_mth AS Invoice_per_Shipment_Month_Transport_Collect,
cfg.a_ob_sort_ovrd_cd AS Outbound_Invoice_Body_Sort_Order,
cfg.a_sep_by_org_inv AS CNDN_Separate_by_Original_Invoice,
cfg.a_inv_pr_dst_flg AS Invoice_per_Destination,
cfg.a_fx_rate_inv_spl_flg AS FX_Rate_Invoice_Split,
cfg.a_ib_sort_ovrd_cd AS Inbound_Invoice_Body_Sort_Order,
cfg.a_prt_sndr_rcv_flg AS SenderReceiver_Details_on_Invoice,
cfg.a_pg_bre_by_shpref_flg AS Inv_Page_for_Each_Shipper_Ref,
agr.a_pid_id AS PID_Id,
agr.a_pid_nm AS PID_Name,
agr.a_cpa_id AS CPA_Id,
agr.a_pid_ty AS PID_Type,
cfg.a_rate_blk_flg AS Protect_Rate_Quotation,
cfg.a_rate_blk_ph_no AS Phone_No_for_Rate_Quot_Prot,
GMAC.a_grp_cd  AS Global_MAC_Code,
c.a_rgn_sgm_cd AS Regional_Segment_Code,
acc.a_acc_sgm_ty AS Sales_Segment_Code,
acc.a_crst_cd AS Crest_Code,
LMAC.a_grp_cd AS Local_MAC_Code,
ARGK.a_grp_cd AS AR_Grouping_Key,
CGK.a_grp_cd AS Collection_Grouping_Code,
c.a_srv_indu_cd AS Standard_Ind_Class_Code_SIC,
c.a_lc_sgm_cd AS Local_Segment_Code,
c.a_indu_cd AS Industry_Code,
IB.a_ntw_tycd AS Network_Type,
IB.a_dst_bil_cd AS Billing_Destination_Code,
OB.a_rmt_rmk as Remittance_Name_DHL,
OB.a_rmt_nm as Remittance_Name,
OB.a_bnk_accnm as Bank_Name,
OB.a_bnk_accno as Bank_Account_Number,
OB.a_add_rmt_inf_1 as Additional_Remittance_Info_1,
OB.a_add_rmt_inf_2 as Additional_Remittance_Info_2,
OB.a_add_rmt_inf_3 as Additional_Remittance_Info_3,
OB.a_prt_ref_flg as Print_Remittance_Slip
FROM t_31_acc acc
LEFT JOIN t_31_acc_rgs DICO ON DICO.a_acc_sid = acc.a_acc_sid AND DICO.a_reg_ty_cd='DICO'
LEFT JOIN t_31_acc_rgs CRID ON CRID.a_acc_sid = acc.a_acc_sid AND CRID.a_reg_ty_cd='CRID'
LEFT JOIN t_31_acc_cnt COM2 ON COM2.a_acc_sid = acc.a_acc_sid AND COM2.a_usgty_cd='COM' and COM2.a_rec_ty=2
LEFT JOIN t_31_acc_cnt COM1 ON COM1.a_acc_sid = acc.a_acc_sid AND COM1.a_usgty_cd='COM' and COM1.a_rec_ty=3
LEFT JOIN t_30_addr COM ON COM.a_addr_sid = COM1.a_addr_sid
LEFT JOIN t_31_acc_cnt BIL2 ON BIL2.a_acc_sid = acc.a_acc_sid AND BIL2.a_usgty_cd='BIL' and BIL2.a_rec_ty=2
LEFT JOIN t_31_acc_cnt BIL1 ON BIL1.a_acc_sid = acc.a_acc_sid AND BIL1.a_usgty_cd='BIL' and BIL1.a_rec_ty=3
LEFT JOIN t_30_addr BIL ON BIL.a_addr_sid = BIL1.a_addr_sid
LEFT JOIN t_31_acc_bil OB ON OB.a_acc_sid = acc.a_acc_sid
LEFT JOIN t_31_acc_bil IB ON IB.a_acc_sid = acc.a_acc_sid
LEFT JOIN t_31_acc_bil_cycl cOB ON cOB.a_accbil_sid = OB.a_accbil_sid AND cOB.a_ccl_ty='OB'
LEFT JOIN t_31_acc_bil_cycl cIB ON cIB.a_accbil_sid = IB.a_accbil_sid AND cIB.a_ccl_ty='IB'
LEFT JOIN t_31_acc_grp GMAC ON GMAC.a_acc_sid = acc.a_acc_sid AND GMAC.a_grpty_cd='GMAC'
LEFT JOIN t_31_acc_grp CGK ON CGK.a_acc_sid = acc.a_acc_sid AND CGK.a_grpty_cd='CGK'
LEFT JOIN t_31_acc_grp LMAC ON LMAC.a_acc_sid = acc.a_acc_sid AND LMAC.a_grpty_cd='LMAC'
LEFT JOIN t_31_acc_grp ARGK ON ARGK.a_acc_sid = acc.a_acc_sid AND ARGK.a_grpty_cd='ARGK'
LEFT JOIN t_31_acc_inv_cfg cfg ON cfg.a_acc_sid = acc.a_acc_sid
LEFT JOIN t_31_acc_rel rel ON rel.a_acc_sid = acc.a_acc_sid
LEFT JOIN t_31_acc_ty ty ON ty.a_acc_sid = acc.a_acc_sid
LEFT JOIN t_31_cust c ON c.a_cust_sid = acc.a_cust_sid
LEFT JOIN t_31_agr agr ON agr.a_cust_sid = c.a_cust_sid
LEFT JOIN t_31_pay_mth pmth ON pmth.a_acc_sid = acc.a_acc_sid