To achieve above mentioned task, we have to create below 3 Objecs :
1. Table
CREATE TABLE xx_cust_acct_site_link
(
cust_account_id NUMBER,
legacy_org_id NUMBER,
legacy_cust_acct_site_id NUMBER,
org_id NUMBER,
cust_acct_site_id NUMBER,
status VARCHAR2(50 byte),
error_message VARCHAR2(2000 byte)
);
2. View
CREATE VIEW xx_cust_acct_site_v
select a.cust_acct_site_id,
a.cust_account_id,
a.party_site_id,
SYSDATE last_update_date,
fnd_global.user_id last_updated_by,
SYSDATE creation_date,
fnd_global.user_id created_by,
fnd_global.login_id last_update_login,
fnd_global.conc_request_id request_id,
fnd_global.prog_appl_id program_application_id,
fnd_global.conc_program_id program_id,
SYSDATE program_update_date,
NULL wh_update_date,
a.attribute_category,
a.attribute1,
a.attribute2,
a.attribute3,
a.attribute4,
a.attribute5,
a.attribute6,
a.attribute7,
a.attribute8,
a.attribute9,
a.attribute10,
a.attribute11,
a.attribute12,
a.attribute13,
a.attribute14,
a.attribute15,
a.attribute16,
a.attribute17,
a.attribute18,
a.attribute19,
a.attribute20,
a.global_attribute_category,
a.global_attribute1,
a.global_attribute2,
a.global_attribute3,
a.global_attribute4,
a.global_attribute5,
a.global_attribute6,
a.global_attribute7,
a.global_attribute8,
a.global_attribute9,
a.global_attribute10,
a.global_attribute11,
a.global_attribute12,
a.global_attribute13,
a.global_attribute14,
a.global_attribute15,
a.global_attribute16,
a.global_attribute17,
a.global_attribute18,
a.global_attribute19,
a.global_attribute20,
NULL orig_system_reference,
'A' status,
a.org_id,
a.bill_to_flag,
a.market_flag,
a.ship_to_flag,
a.customer_category_code,
a.LANGUAGE,
a.key_account_flag,
a.tp_header_id,
a.ece_tp_location_code,
a.service_territory_id,
a.primary_specialist_id,
a.secondary_specialist_id,
a.territory_id,
a.address_text,
a.territory,
a.translated_customer_name,
NULL object_version_number,
'TCA_FORM_WRAPPER' created_by_module,
222 application_id,
b.account_number
FROM hz_cust_acct_sites_all a,
hz_cust_accounts b,
hz_party_sites c
WHERE a.cust_account_id = b.cust_account_id
AND a.party_site_id = c.party_site_id
AND b.status = 'A'
AND a.status = 'A';
3. PROCEDURE Creation
CREATE PROCEDURE Xx_copy_customer_sites_api(
errbuff OUT VARCHAR2,
retcode OUT VARCHAR2,
p_cust_account_id NUMBER,
p_sourece_org_id NUMBER,
p_target_org_id NUMBER)
AS
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_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
x_cust_acct_site_id NUMBER;
x_site_use_id NUMBER;
l_customer_id NUMBER;
CURSOR c_cust_acct_site IS
SELECT cust_acct_site_id,
cust_account_id,
party_site_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
orig_system_reference,
NULL orig_system,
status,
customer_category_code,
LANGUAGE,
key_account_flag,
tp_header_id,
ece_tp_location_code,
primary_specialist_id,
secondary_specialist_id,
territory_id,
territory,
translated_customer_name,
created_by_module,
application_id,
org_id
FROM xx_cust_acct_site_v A
WHERE org_id = p_sourece_org_id
AND a.cust_account_id = Nvl(p_cust_account_id, a.cust_account_id)
AND account_number NOT IN ( '---------EXCLUDED CUSTOMERS----------' )
AND NOT EXISTS (SELECT 1
FROM xx_cust_acct_site_link X
WHERE X.legacy_cust_acct_site_id =
A.cust_acct_site_id
AND X.org_id = p_target_org_id
AND X.cust_acct_site_id IS NOT NULL);
CURSOR c_site_use IS
SELECT site_use_id,
cust_acct_site_id,
site_use_code,
primary_flag,
status,
location,
contact_id,
bill_to_site_use_id,
orig_system_reference,
NULL orig_system,
sic_code,
payment_term_id,
gsa_indicator,
ship_partial,
ship_via,
fob_point,
order_type_id,
price_list_id,
freight_term,
warehouse_id,
territory_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
tax_reference,
sort_priority,
tax_code,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
demand_class_code,
tax_header_level_flag,
tax_rounding_rule,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
global_attribute_category,
primary_salesrep_id,
finchrg_receivables_trx_id,
dates_negative_tolerance,
dates_positive_tolerance,
date_type_preference,
over_shipment_tolerance,
under_shipment_tolerance,
item_cross_ref_pref,
over_return_tolerance,
under_return_tolerance,
ship_sets_include_lines_flag,
arrivalsets_include_lines_flag,
sched_date_push_flag,
invoice_quantity_rule,
pricing_event,
gl_id_rec,
gl_id_rev,
gl_id_tax,
gl_id_freight,
gl_id_clearing,
gl_id_unbilled,
gl_id_unearned,
gl_id_unpaid_rec,
gl_id_remittance,
gl_id_factor,
tax_classification,
created_by_module,
application_id,
org_id
FROM xxcns_cust_site_uses_v A
WHERE org_id = p_sourece_org_id
AND a.cust_account_id = Nvl(p_cust_account_id, a.cust_account_id)
AND account_number NOT IN ( '---------EXCLUDED CUSTOMERS----------')
AND NOT EXISTS (SELECT 1
FROM xx_cust_site_uses_link X
WHERE X.legacy_site_use_id = A.site_use_id
AND X.org_id = p_target_org_id
AND X.site_use_id IS NOT NULL);
BEGIN
FOR c1 IN c_cust_acct_site LOOP
INSERT INTO xx_cust_acct_site_link
(cust_account_id,
legacy_org_id,
legacy_cust_acct_site_id,
org_id)
SELECT c1.cust_account_id,
c1.org_id,
c1.cust_acct_site_id,
p_target_org_id
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM xx_cust_acct_site_link X
WHERE X.legacy_cust_acct_site_id =
c1.cust_acct_site_id
AND X.org_id = p_target_org_id);
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
x_cust_acct_site_id := NULL;
p_cust_acct_site_rec := c1;
--<
--p_cust_acct_site_rec.party_site_id := XX;--< p_cust_acct_site_rec.language := 'US';
--p_cust_acct_site_rec.created_by_module := 'TCAPI_EXAMPLE';
p_cust_acct_site_rec.org_id := p_target_org_id;
p_cust_acct_site_rec.cust_acct_site_id := NULL;
hz_cust_account_site_v2pub.Create_cust_acct_site('T',
p_cust_acct_site_rec,
x_cust_acct_site_id, x_return_status, x_msg_count, x_msg_data);
UPDATE xx_cust_acct_site_link
SET cust_acct_site_id = x_cust_acct_site_id,
status = Substr(x_return_status, 1, 50),
error_message = Substr(x_msg_data, 1, 2000)
WHERE legacy_cust_acct_site_id = c1.cust_acct_site_id
AND org_id = p_target_org_id;
COMMIT;
/*FND_FILE.put_line(FND_FILE.LOG,'***************************');
FND_FILE.put_line(FND_FILE.LOG,'x_cust_acct_site_id: '||x_cust_acct_site_id);
FND_FILE.put_line(FND_FILE.LOG,'x_return_status: '||x_return_status);
FND_FILE.put_line(FND_FILE.LOG,'x_msg_count: '||x_msg_count);
FND_FILE.put_line(FND_FILE.LOG,'x_msg_data: '||x_msg_data);
FND_FILE.put_line(FND_FILE.LOG,'***************************');
*/
IF x_cust_acct_site_id IS NOT NULL THEN
INSERT INTO jai_party_regs
SELECT jai_party_regs_s.NEXTVAL PARTY_REG_ID,
--NULL PARTY_REG_ID,
A.party_type_code,
A.supplier_flag,
A.customer_flag,
A.site_flag,
A.party_id,
B.cust_acct_site_id PARTY_SITE_ID,
A.org_classification_code,
A.item_category_list,
A.invoice_tax_category_id,
B.org_id,
SYSDATE CREATION_DATE,
fnd_global.user_id CREATED_BY,
SYSDATE LAST_UPDATE_DATE,
fnd_global.login_id LAST_UPDATE_LOGIN,
fnd_global.user_id LAST_UPDATED_BY,
A.record_type_code,
A.legal_name
FROM jai_party_regs a,
xx_cust_acct_site_link b
WHERE a.party_site_id = b.legacy_cust_acct_site_id
AND A.org_id = B.legacy_org_id
AND A.customer_flag = 'Y'
AND b.cust_acct_site_id = x_cust_acct_site_id
AND NOT EXISTS (SELECT 1
FROM jai_party_regs x
WHERE x.party_site_id = b.cust_acct_site_id
AND x.customer_flag = 'Y'
AND X.org_id = B.org_id);
INSERT INTO jai_party_reg_lines
SELECT D.party_reg_id,
jai_party_reg_lines_s.NEXTVAL PARTY_REG_LINE_ID,
C.line_context,
C.regime_id,
C.registration_type_code,
C.registration_number,
C.sec_registration_type_code,
C.secondary_registration_number,
C.num_of_return_days,
C.tax_authority_id,
C.tax_authority_site_id,
C.assessable_price_list_id,
C.default_section_code,
C.exemption_type,
C.exemption_num,
C.intercompany_receivable_ccid,
C.intercompany_payable_ccid,
C.tracking_num,
C.effective_from,
C.effective_to,
D.creation_date,
D.created_by,
D.last_update_date,
D.last_update_login,
D.last_updated_by,
D.record_type_code
FROM jai_party_regs a,
xx_cust_acct_site_link b,
jai_party_reg_lines C,
jai_party_regs D
WHERE a.party_site_id = b.legacy_cust_acct_site_id
AND A.party_reg_id = C.party_reg_id
AND A.org_id = B.legacy_org_id
AND B.cust_acct_site_id = D.party_site_id
AND A.customer_flag = 'Y'
AND D.customer_flag = 'Y'
AND C.effective_to IS NULL
AND b.cust_acct_site_id = x_cust_acct_site_id
AND NOT EXISTS (SELECT 1
FROM jai_party_reg_lines x
WHERE x.party_reg_id = d.party_reg_id
AND x.regime_id = c.regime_id
AND x.registration_type_code =
c.registration_type_code
AND x.registration_number =
c.registration_number)
;
INSERT INTO jai_reporting_associations
SELECT jai_reporting_associations_s.NEXTVAL REPORTING_ASSOCIATION_ID,
B.reporting_type_id,
B.reporting_code_id,
B.reporting_type_name,
B.reporting_usage,
B.reporting_code_description,
B.reporting_code,
B.entity_code,
D.party_reg_id ENTITY_ID,
B.entity_source_table,
B.regime_id,
(SELECT x.regime_name
FROM jai_regimes x
WHERE x.regime_id = B.regime_id) REGIME_CODE,
B.effective_from,
B.effective_to,
D.creation_date,
D.created_by,
D.last_update_date,
D.last_update_login,
D.last_updated_by,
B.record_type_code,
B.stl_hdr_id
FROM jai_party_regs A,
jai_reporting_associations B,
xx_cust_acct_site_link C,
jai_party_regs D
WHERE A.party_reg_id = B.entity_id
AND A.party_site_id = C.legacy_cust_acct_site_id
AND A.org_id = C.legacy_org_id
AND C.cust_acct_site_id = D.party_site_id
AND A.customer_flag = 'Y'
AND D.customer_flag = 'Y'
AND B.effective_to IS NULL
AND B.entity_source_table = 'JAI_PARTY_REGS'
AND c.cust_acct_site_id = x_cust_acct_site_id
AND NOT EXISTS (SELECT 1
FROM jai_reporting_associations x
WHERE x.entity_id = d.party_reg_id
AND x.entity_source_table =
'JAI_PARTY_REGS'
AND x.regime_id = b.regime_id
AND x.reporting_type_id =
b.reporting_type_id);
COMMIT;
END IF;
END LOOP;
/*------------------------------SITE USE----------------------------------------------*/
mo_global.Set_policy_context('S', p_target_org_id);
FOR c2 IN c_site_use LOOP
BEGIN
SELECT cust_account_id
INTO l_customer_id
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = c2.cust_acct_site_id;
EXCEPTION
WHEN OTHERS THEN
l_customer_id := NULL;
END;
INSERT INTO xx_cust_site_uses_link
(cust_account_id,
legacy_org_id,
legacy_site_use_id,
org_id)
SELECT l_customer_id,
c2.org_id,
c2.site_use_id,
p_target_org_id
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM xx_cust_site_uses_link X
WHERE X.legacy_site_use_id = c2.site_use_id
AND X.org_id = p_target_org_id);
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
x_site_use_id := NULL;
p_cust_site_use_rec.site_use_id := c2.site_use_id;
p_cust_site_use_rec.cust_acct_site_id := c2.cust_acct_site_id;
p_cust_site_use_rec.site_use_code := c2.site_use_code;
p_cust_site_use_rec.primary_flag := c2.primary_flag;
p_cust_site_use_rec.status := c2.status;
p_cust_site_use_rec.location := c2.location;
p_cust_site_use_rec.contact_id := c2.contact_id;
p_cust_site_use_rec.bill_to_site_use_id := c2.bill_to_site_use_id;
p_cust_site_use_rec.orig_system_reference := c2.orig_system_reference;
p_cust_site_use_rec.orig_system := c2.orig_system;
p_cust_site_use_rec.sic_code := c2.sic_code;
p_cust_site_use_rec.payment_term_id := c2.payment_term_id;
p_cust_site_use_rec.gsa_indicator := c2.gsa_indicator;
p_cust_site_use_rec.ship_partial := c2.ship_partial;
p_cust_site_use_rec.ship_via := c2.ship_via;
p_cust_site_use_rec.fob_point := c2.fob_point;
p_cust_site_use_rec.order_type_id := c2.order_type_id;
p_cust_site_use_rec.price_list_id := c2.price_list_id;
p_cust_site_use_rec.freight_term := c2.freight_term;
p_cust_site_use_rec.warehouse_id := c2.warehouse_id;
p_cust_site_use_rec.territory_id := c2.territory_id;
p_cust_site_use_rec.attribute_category := c2.attribute_category;
p_cust_site_use_rec.attribute1 := c2.attribute1;
p_cust_site_use_rec.attribute2 := c2.attribute2;
p_cust_site_use_rec.attribute3 := c2.attribute3;
p_cust_site_use_rec.attribute4 := c2.attribute4;
p_cust_site_use_rec.attribute5 := c2.attribute5;
p_cust_site_use_rec.attribute6 := c2.attribute6;
p_cust_site_use_rec.attribute7 := c2.attribute7;
p_cust_site_use_rec.attribute8 := c2.attribute8;
p_cust_site_use_rec.attribute9 := c2.attribute9;
p_cust_site_use_rec.attribute10 := c2.attribute10;
p_cust_site_use_rec.tax_reference := c2.tax_reference;
p_cust_site_use_rec.sort_priority := c2.sort_priority;
p_cust_site_use_rec.tax_code := NULL; --C2.tax_code;
p_cust_site_use_rec.attribute11 := c2.attribute11;
p_cust_site_use_rec.attribute12 := c2.attribute12;
p_cust_site_use_rec.attribute13 := c2.attribute13;
p_cust_site_use_rec.attribute14 := c2.attribute14;
p_cust_site_use_rec.attribute15 := c2.attribute15;
p_cust_site_use_rec.attribute16 := c2.attribute16;
p_cust_site_use_rec.attribute17 := c2.attribute17;
p_cust_site_use_rec.attribute18 := c2.attribute18;
p_cust_site_use_rec.attribute19 := c2.attribute19;
p_cust_site_use_rec.attribute20 := c2.attribute20;
p_cust_site_use_rec.attribute21 := c2.attribute21;
p_cust_site_use_rec.attribute22 := c2.attribute22;
p_cust_site_use_rec.attribute23 := c2.attribute23;
p_cust_site_use_rec.attribute24 := c2.attribute24;
p_cust_site_use_rec.attribute25 := c2.attribute25;
p_cust_site_use_rec.demand_class_code := c2.demand_class_code;
p_cust_site_use_rec.tax_header_level_flag := c2.tax_header_level_flag;
p_cust_site_use_rec.tax_rounding_rule := c2.tax_rounding_rule;
p_cust_site_use_rec.global_attribute1 := c2.global_attribute1;
p_cust_site_use_rec.global_attribute2 := c2.global_attribute2;
p_cust_site_use_rec.global_attribute3 := c2.global_attribute3;
p_cust_site_use_rec.global_attribute4 := c2.global_attribute4;
p_cust_site_use_rec.global_attribute5 := c2.global_attribute5;
p_cust_site_use_rec.global_attribute6 := c2.global_attribute6;
p_cust_site_use_rec.global_attribute7 := c2.global_attribute7;
p_cust_site_use_rec.global_attribute8 := c2.global_attribute8;
p_cust_site_use_rec.global_attribute9 := c2.global_attribute9;
p_cust_site_use_rec.global_attribute10 := c2.global_attribute10;
p_cust_site_use_rec.global_attribute11 := c2.global_attribute11;
p_cust_site_use_rec.global_attribute12 := c2.global_attribute12;
p_cust_site_use_rec.global_attribute13 := c2.global_attribute13;
p_cust_site_use_rec.global_attribute14 := c2.global_attribute14;
p_cust_site_use_rec.global_attribute15 := c2.global_attribute15;
p_cust_site_use_rec.global_attribute16 := c2.global_attribute16;
p_cust_site_use_rec.global_attribute17 := c2.global_attribute17;
p_cust_site_use_rec.global_attribute18 := c2.global_attribute18;
p_cust_site_use_rec.global_attribute19 := c2.global_attribute19;
p_cust_site_use_rec.global_attribute20 := c2.global_attribute20;
p_cust_site_use_rec.global_attribute_category :=
c2.global_attribute_category;
p_cust_site_use_rec.primary_salesrep_id := c2.primary_salesrep_id;
p_cust_site_use_rec.finchrg_receivables_trx_id :=
c2.finchrg_receivables_trx_id;
p_cust_site_use_rec.dates_negative_tolerance :=
c2.dates_negative_tolerance;
p_cust_site_use_rec.dates_positive_tolerance :=
c2.dates_positive_tolerance;
p_cust_site_use_rec.date_type_preference := c2.date_type_preference;
p_cust_site_use_rec.over_shipment_tolerance :=
c2.over_shipment_tolerance;
p_cust_site_use_rec.under_shipment_tolerance :=
c2.under_shipment_tolerance;
p_cust_site_use_rec.item_cross_ref_pref := c2.item_cross_ref_pref;
p_cust_site_use_rec.over_return_tolerance := c2.over_return_tolerance;
p_cust_site_use_rec.under_return_tolerance := c2.under_return_tolerance;
p_cust_site_use_rec.ship_sets_include_lines_flag :=
c2.ship_sets_include_lines_flag;
p_cust_site_use_rec.arrivalsets_include_lines_flag :=
c2.arrivalsets_include_lines_flag;
p_cust_site_use_rec.sched_date_push_flag := c2.sched_date_push_flag;
p_cust_site_use_rec.invoice_quantity_rule := c2.invoice_quantity_rule;
p_cust_site_use_rec.pricing_event := c2.pricing_event;
p_cust_site_use_rec.gl_id_rec := c2.gl_id_rec;
p_cust_site_use_rec.gl_id_rev := c2.gl_id_rev;
p_cust_site_use_rec.gl_id_tax := c2.gl_id_tax;
p_cust_site_use_rec.gl_id_freight := c2.gl_id_freight;
p_cust_site_use_rec.gl_id_clearing := c2.gl_id_clearing;
p_cust_site_use_rec.gl_id_unbilled := c2.gl_id_unbilled;
p_cust_site_use_rec.gl_id_unearned := c2.gl_id_unearned;
p_cust_site_use_rec.gl_id_unpaid_rec := c2.gl_id_unpaid_rec;
p_cust_site_use_rec.gl_id_remittance := c2.gl_id_remittance;
p_cust_site_use_rec.gl_id_factor := c2.gl_id_factor;
p_cust_site_use_rec.tax_classification := c2.tax_classification;
p_cust_site_use_rec.created_by_module := c2.created_by_module;
p_cust_site_use_rec.application_id := c2.application_id;
p_cust_site_use_rec.org_id := p_target_org_id;
p_cust_site_use_rec.site_use_id := NULL;
hz_cust_account_site_v2pub.Create_cust_site_use('T', p_cust_site_use_rec
,
p_customer_profile_rec, '', '', x_site_use_id, x_return_status,
x_msg_count,
x_msg_data);
UPDATE xx_cust_site_uses_link
SET site_use_id = x_site_use_id,
status = Substr(x_return_status, 1, 50),
error_message = Substr(x_msg_data, 1, 2000)
WHERE legacy_site_use_id = c2.site_use_id
AND org_id = p_target_org_id;
UPDATE hz_cust_site_uses_all
SET tax_code = c2.tax_code
WHERE site_use_id = x_site_use_id;
COMMIT;
END LOOP;
END;
/
No comments:
Post a Comment