AR*客户地点分配OU

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

DECLARE
    p_cust_acct_site_rec   hz_cust_account_site_v2pub.cust_acct_site_rec_type;
    p_cust_site_use_rec    hz_cust_account_site_v2pub.cust_site_use_rec_type;
    p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
    x_site_use_id          NUMBER;
    x_return_status        VARCHAR2(20000);
    x_msg_count            NUMBER;
    x_msg_data             VARCHAR2(20000);
    x_cust_acct_site_id    NUMBER;
    l_org_id               NUMBER;
    l_party_site_id        NUMBER;
    l_cust_account_id      NUMBER;
    l_location_id          NUMBER;
    l_error_msg            VARCHAR2(32000);
    v_location_rec   hz_location_v2pub.location_rec_type;
    v_location_clear hz_location_v2pub.location_rec_type;
    v_location_id    NUMBER;
 
    v_version_number NUMBER;
    CURSOR cur IS
        SELECT t.import_id,
                     t.ou_name,
                     t.party_name,
                     t.party_site_number,
                     t.error_flag,
                     t.error_msg,
                     t.attribute1,
                     t.attribute2,
                     t.attribute3,
                     t.attribute4,
                     t.attribute5
        FROM   cux_party_site_import_temp t
        WHERE  1 = 1
        --AND    t.party_name = '东莞市安德丰电池有限公司'
        --  and t.import_id=406
        ;
    -- AND    t.error_flag = 'Y';
BEGIN
    -- Setting the Context --
    mo_global.init('AR');
    fnd_global.apps_initialize(user_id      => 1318,
                                                         resp_id      => 50559,
                                                         resp_appl_id => 222);
 
    l_error_msg := NULL;
    FOR rec IN cur
    LOOP
        l_error_msg := NULL;
        -- Initializing the Mandatory API parameters
        l_org_id := NULL;
        BEGIN
            SELECT hou.organization_id
            INTO   l_org_id
            FROM   hr_operating_units hou
            WHERE  1 = 1
            AND    hou.name = rec.ou_name;
        EXCEPTION
            WHEN OTHERS THEN
                l_org_id    := NULL;
                l_error_msg := l_error_msg || ' 业务实体不存在';
        END;
     
        mo_global.set_policy_context('S',
                                                                 l_org_id);
        fnd_global.set_nls_context('AMERICAN');
     
        BEGIN
            SELECT hps.party_site_id, hl.location_id
            INTO   l_party_site_id, l_location_id
            FROM   hz_party_sites hps, hz_parties hp, hz_locations hl
            WHERE  hps.party_id = hp.party_id
            AND    hps.location_id = hl.location_id(+)
            AND    hps.party_site_number = rec.party_site_number
            AND    hp.party_name = rec.party_name;
        EXCEPTION
            WHEN OTHERS THEN
                l_party_site_id := NULL;
                l_location_id   := NULL;
                --l_error_msg     := l_error_msg || ' 客户地址不存在';
        END;
     
        IF (l_location_id IS NULL) THEN
        END IF;
     
        BEGIN
            SELECT DISTINCT hca.cust_account_id
            INTO   l_cust_account_id
            FROM   hz_cust_acct_sites_all hcas,
                         hz_cust_accounts_all   hca,
                         hz_parties             hp
            WHERE  hp.party_id = hca.party_id
            AND    hca.cust_account_id = hcas.cust_account_id
            AND    hcas.party_site_id = l_party_site_id
            AND    hp.party_name = rec.party_name;
        EXCEPTION
            WHEN OTHERS THEN
                l_cust_account_id := NULL;
                l_error_msg       := l_error_msg || ' 客户账户不存在 ';
        END;
     
        IF (l_error_msg IS NULL) THEN
            --创建客户地点
            p_cust_acct_site_rec.org_id            := l_org_id;
            p_cust_acct_site_rec.cust_account_id   := l_cust_account_id;
            p_cust_acct_site_rec.party_site_id     := l_party_site_id;
            p_cust_acct_site_rec.location_id       := l_party_site_id;
            p_cust_acct_site_rec.created_by_module := 'HZ_CPUI';
         
            dbms_output.put_line('Calling the API hz_cust_account_site_v2pub.create_cust_acct_site****************************');
         
            hz_cust_account_site_v2pub.create_cust_acct_site(p_init_msg_list      => fnd_api.g_true,
                                                                                                             p_cust_acct_site_rec => p_cust_acct_site_rec,
                                                                                                             x_cust_acct_site_id  => x_cust_acct_site_id,
                                                                                                             x_return_status      => x_return_status,
                                                                                                             x_msg_count          => x_msg_count,
                                                                                                             x_msg_data           => x_msg_data);
         
            IF x_return_status = fnd_api.g_ret_sts_success THEN
             
                l_error_msg := NULL;
                --COMMIT;
                dbms_output.put_line('Creation of Customer Account Site is Successful,Customer Account Site Id is = ' ||
                                                         x_cust_acct_site_id);
             
                --创建客户地点的业务用途(收单方，收货方)
                -- Initializing the Mandatory API parameters 111
                p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
                p_cust_site_use_rec.site_use_code     := 'BILL_TO';
                --p_cust_site_use_rec.location          := l_location_id;
                p_cust_site_use_rec.created_by_module := 'HZ_CPUI';
             
                dbms_output.put_line('Calling the API hz_cust_account_site_v2pub.create_cust_site_use=============');
                x_site_use_id := NULL;
                hz_cust_account_site_v2pub.create_cust_site_use(p_init_msg_list        => fnd_api.g_true,
                                                                                                                p_cust_site_use_rec    => p_cust_site_use_rec,
                                                                                                                p_customer_profile_rec => p_customer_profile_rec,
                                                                                                                p_create_profile       => fnd_api.g_true,
                                                                                                                p_create_profile_amt   => fnd_api.g_true,
                                                                                                                x_site_use_id          => x_site_use_id,
                                                                                                                x_return_status        => x_return_status,
                                                                                                                x_msg_count            => x_msg_count,
                                                                                                                x_msg_data             => x_msg_data);
             
                IF x_return_status = fnd_api.g_ret_sts_success THEN
                 
                    l_error_msg := NULL;
                    --COMMIT;
                    dbms_output.put_line('Successful,Site Use Id = ' ||
                                                             x_site_use_id || ',Site Use    = ' ||
                                                             p_cust_site_use_rec.site_use_code);
                ELSE
                    dbms_output.put_line('Creation of Customer Accnt Site use got failed:' ||
                                                             x_msg_data);
                 
                    l_error_msg := l_error_msg || x_msg_data;
                    ROLLBACK;
                    FOR i IN 1 .. x_msg_count
                    LOOP
                        x_msg_data := fnd_msg_pub.get(p_msg_index => i,
                                                                                    p_encoded   => 'F');
                        dbms_output.put_line(i || ') ' || x_msg_data);
                    END LOOP;
                END IF;
             
                p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
                p_cust_site_use_rec.site_use_code     := 'SHIP_TO';
                --p_cust_site_use_rec.bill_to_site_use_id := x_site_use_id;
                --p_cust_site_use_rec.location          :=  l_location_id;
                p_cust_site_use_rec.created_by_module := 'HZ_CPUI';
             
                x_site_use_id := NULL;
                hz_cust_account_site_v2pub.create_cust_site_use(p_init_msg_list        => fnd_api.g_true,
                                                                                                                p_cust_site_use_rec    => p_cust_site_use_rec,
                                                                                                                p_customer_profile_rec => p_customer_profile_rec,
                                                                                                                p_create_profile       => fnd_api.g_true,
                                                                                                                p_create_profile_amt   => fnd_api.g_true,
                                                                                                                x_site_use_id          => x_site_use_id,
                                                                                                                x_return_status        => x_return_status,
                                                                                                                x_msg_count            => x_msg_count,
                                                                                                                x_msg_data             => x_msg_data);
             
                IF x_return_status = fnd_api.g_ret_sts_success THEN
                 
                    l_error_msg := NULL;
                    --COMMIT;
                    dbms_output.put_line('Successful,Site Use Id = ' ||
                                                             x_site_use_id || ',Site Use    = ' ||
                                                             p_cust_site_use_rec.site_use_code);
                ELSE
                    dbms_output.put_line('Creation of Customer Accnt Site use got failed:' ||
                                                             x_msg_data);
                    --ROLLBACK;
                    FOR i IN 1 .. x_msg_count
                    LOOP
                        x_msg_data  := fnd_msg_pub.get(p_msg_index => i,
                                                                                     p_encoded   => 'F');
                        l_error_msg := l_error_msg || x_msg_data;
                        dbms_output.put_line(i || ') ' || x_msg_data);
                    END LOOP;
                END IF;
            ELSE
                dbms_output.put_line('Creation of Customer Account Site got failed:' ||
                                                         x_msg_data);
                --ROLLBACK;
                FOR i IN 1 .. x_msg_count
                LOOP
                    x_msg_data  := fnd_msg_pub.get(p_msg_index => i,
                                                                                 p_encoded   => 'F');
                    l_error_msg := l_error_msg || x_msg_data;
                    dbms_output.put_line(i || ') ' || x_msg_data);
                END LOOP;
            END IF; --IF x_return_status = fnd_api.g_ret_sts_success THEN
         
        ELSE
            dbms_output.put_line('l_error_msg:=' || l_error_msg);
        END IF; --  IF (l_error_msg IS NULL) THEN
     
        IF (l_error_msg IS NOT NULL) THEN
            UPDATE cux_party_site_import_temp t
            SET    t.error_flag = 'Y', t.error_msg = l_error_msg
            WHERE  1 = 1
            AND    t.import_id = rec.import_id;
        ELSE
            UPDATE cux_party_site_import_temp t
            SET    t.error_flag = 'N', t.error_msg = NULL
            WHERE  1 = 1
            AND    t.import_id = rec.import_id;
        END IF;
    END LOOP;
 
    --COMMIT;
 
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('导入异常!');
END;

create table cux_party_site_import_temp
(IMPORT_ID         NUMBER,
 OU_NAME           VARCHAR2(240),
 PARTY_NAME        VARCHAR2(240),
 PARTY_SITE_NAME   VARCHAR2(240),
 ADDRESS           VARCHAR2(240),
 ERROR_FLAG        VARCHAR2(240),
 ERROR_MSG         VARCHAR2(4000),
 ATTRIBUTE1        VARCHAR2(240),
 ATTRIBUTE2        VARCHAR2(240),
 ATTRIBUTE3        VARCHAR2(240),
 ATTRIBUTE4        VARCHAR2(240),
 ATTRIBUTE5        VARCHAR2(240))
  
/* 
 select * from  hz_parties hps
 where hps.PARTY_NAME like '%惠州市伟江新能源有限公司%'*/