/*if u need ship_to also, then copy the above insert code twice and change the BILL_TO to SHIP_TO and change the sequence NEXTVAL to CURRVAL for SHIP_TO */ -- -- Insert records inot profile interface table -- INSERT into ar.ra_customer_profiles_int_all ( ORIG_SYSTEM_CUSTOMER_REF , INSERT_UPDATE_FLAG , ORG_ID , CREDIT_HOLD , CUSTOMER_PROFILE_CLASS_NAME , LAST_UPDATE_DATE , LAST_UPDATED_BY , CREATION_DATE , CREATED_BY ) VALUES ( 'FNCE'||'-'||FUJ_CUST_S.CURRVAL -- ORIG_SYSTEM_CUSTOMER_REF , 'I' -- INSERT_UPDATE_FLAG , 1770 -- ORG_ID , 'N' -- CREDIT_HOLD , 'DEFAULT' -- CUSTOMER_PROFILE_CLASS_NAME , SYSDATE -- LAST_UPDATE_DATE , 2605 -- LAST_UPDATED_BY , SYSDATE -- CREATION_DATE , 2605 -- CREATED_BY ) ; END LOOP; COMMIT; END; /
2. run the 'Customer Interface' concurrent program from the Receivables Responsibility with default parameter values.
3. Check output of concprogram for the exceptions and other details.
4. once created successfully, the data from the interface tables will be deleted auto.
How to Assign
all the items from Item Master to a different Organization
Prerequisite:-
Organization Hierarchy need to setup
Need to run a request :- Item Organization Assignment from Inventory
responsibility.
Parameter:- Source Organization as Item Master or any other organization(From
where you want to assign)
Hierarchy Origin:- Destination Organization(Where you want to assign all the
item)
Hierarchy:- Organization Hierarchy Name
Request Count:-1
Submit the request
Issues and Solutions:
1.
If you dont find Item Organization Assignment Concurrent Program, add manually to your responsibility request group.
2. Once concurrent Prorgram completes with warning or error, with below msg in log then.
MESSAGE NAME : INV_IOI_MASTER_CHILD_4D ERROR
MESSAGE : Master - Child Conflict in one of these Attributes: Demand
Time Fence(DEMAND_TIME_FENCE_CODE), Lead Time Lot
Size(LEAD_TIME_LOT_SIZE), Standard Lot Size(STD_LOT_SIZE), Cumulative
Manufacturing(CUM_MANUFACTURING_LEAD_TIME), Overrun
Percentage(OVERRUN_PERCENTAGE), Calculation ATP(MRP_CALCULATE_ATP_FLAG),
Positive Acceptable Rate(ACCEPTABLE_RATE_INCREASE), Negative Acceptable
Rate(ACCEPTABLE_RATE_DECREASE).
Solution: some columns have null values, so we will upload manually using below script, run before in test instance after taking backup ofcourse.
REM datafix after uploading master items_before_assign_to_Other_orgs REM take backup of mtl_systems_items_b before running this script
REM $Header: b19630365.sql 120.0.12010000.1 2014/10/28 12:08:24 liswang noship $ REM
REM dbdrv sql ~PROD ~PATH ~FILE none none none sqlplus \ REM dbdrv phase=upg checkfile:~PROD:~PATH:~FILE REM +======================================================================+ REM | Copyright (c) 1997, 2013 Oracle Corporation | REM | Redwood Shores, California, USA | REM | All rights reserved. | REM +======================================================================+ REM SQL Script File Name: REM b19630365.sql REM
REM Description: REM This sql script updates item attributes with REM R12 default value for all items. REM
REM ========================================================================
set verify off WHENEVER SQLERROR EXIT FAILURE ROLLBACK; WHENEVER OSERROR EXIT FAILURE ROLLBACK;
Update mtl_system_items_b set CATALOG_STATUS_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND CATALOG_STATUS_FLAG is null;
COMMIT;
Update mtl_system_items_b set COLLATERAL_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND COLLATERAL_FLAG is null;
COMMIT;
Update mtl_system_items_b set CONTAINER_ITEM_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND CONTAINER_ITEM_FLAG is null;
COMMIT;
Update mtl_system_items_b set DOWNLOADABLE_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND DOWNLOADABLE_FLAG is null;
COMMIT;
Update mtl_system_items_b set ELECTRONIC_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND ELECTRONIC_FLAG is null;
COMMIT;
Update mtl_system_items_b set EVENT_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND EVENT_FLAG is null;
COMMIT;
Update mtl_system_items_b set INDIVISIBLE_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND INDIVISIBLE_FLAG is null;
COMMIT;
Update mtl_system_items_b set MRP_CALCULATE_ATP_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND MRP_CALCULATE_ATP_FLAG is null;
COMMIT;
Update mtl_system_items_b set VEHICLE_ITEM_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND VEHICLE_ITEM_FLAG is null;
COMMIT;
Update mtl_system_items_b set RECIPE_ENABLED_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND RECIPE_ENABLED_FLAG is null;
COMMIT;
Update mtl_system_items_b set BOM_ENABLED_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND BOM_ENABLED_FLAG is null;
COMMIT;
Update mtl_system_items_b set LOT_STATUS_ENABLED = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND LOT_STATUS_ENABLED is null;
COMMIT;
Update mtl_system_items_b set SERIAL_STATUS_ENABLED = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND SERIAL_STATUS_ENABLED is null;
COMMIT;
Update mtl_system_items_b set LOT_DIVISIBLE_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND LOT_DIVISIBLE_FLAG is null;
COMMIT;
Update mtl_system_items_b set LOT_SPLIT_ENABLED = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND LOT_SPLIT_ENABLED is null;
COMMIT;
Update mtl_system_items_b set LOT_MERGE_ENABLED = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND LOT_MERGE_ENABLED is null;
COMMIT;
Update mtl_system_items_b set LOT_TRANSLATE_ENABLED = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND LOT_TRANSLATE_ENABLED is null;
COMMIT;
Update mtl_system_items_b set LOT_SUBSTITUTION_ENABLED = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND LOT_SUBSTITUTION_ENABLED is null;
COMMIT;
Update mtl_system_items_b set BULK_PICKED_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND BULK_PICKED_FLAG is null;
COMMIT;
Update mtl_system_items_b set SERIAL_TAGGING_FLAG = 'N' WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND SERIAL_TAGGING_FLAG is null;
COMMIT;
Update mtl_system_items_b set EQUIPMENT_TYPE = 2 WHERE ORGANIZATION_ID = 1771 AND ENABLED_FLAG = 'Y' AND EQUIPMENT_TYPE is null;
--to insert single item and import item concurrent program INSERT INTO mtl_system_items_interface (organization_id, process_flag, set_process_id, transaction_type,segment1, description ,PRIMARY_UOM_CODE ,ITEM_TYPE ,COST_OF_SALES_ACCOUNT ,COSTING_ENABLED_FLAG ,INVENTORY_ASSET_FLAG ,PURCHASING_ENABLED_FLAG ,PURCHASING_ITEM_FLAG ,INVENTORY_ITEM_FLAG ,STOCK_ENABLED_FLAG ,INVOICEABLE_ITEM_FLAG ,INVOICE_ENABLED_FLAG) VALUES ( 1771, -- organization_id 1, -- process_flag the value "1" mean the item ready to be imported. 1, -- set_process_id, this is group id and is used as the "Process Set" in the IOI report submission 'CREATE', -- transaction_type 'Test3', -- segment1 the Item name that is displayed or printed. 'Demo of item creation' ,'KG' ,UPPER('FG') ,3351191 ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' );
COMMIT
-------------------------Concurrent Program:-------------------------------------- Import Items in Inventory Super User Select Organization 'Inventory Master Org'
All Organizations: No Validate Items: Yes Process Items : Yes Delete Processed Rows : No Process Set : 1 Create or Update Items: 1 Gather Statistics: No
---to insert into interface table from the custom table created
INSERT INTO mtl_system_items_interface (organization_id, process_flag, set_process_id, transaction_type,segment1, description ,PRIMARY_UOM_CODE ,ITEM_TYPE ,COST_OF_SALES_ACCOUNT ,COSTING_ENABLED_FLAG ,INVENTORY_ASSET_FLAG ,PURCHASING_ENABLED_FLAG ,PURCHASING_ITEM_FLAG ,INVENTORY_ITEM_FLAG ,STOCK_ENABLED_FLAG ,INVOICEABLE_ITEM_FLAG ,INVOICE_ENABLED_FLAG) SELECT 1771, -- organization_id 1, -- process_flag the value "1" mean the item ready to be imported. 1, -- set_process_id, this is group id and is used as the "Process Set" in the IOI report submission 'CREATE', -- transaction_type ITEM_CODE, -- segment1 the Item name that is displayed or printed. DESCRIPTION ,UOM ,USER_ITEM_TYPE ,COST_OF_GOODS_ACT ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' ,'Y' FROM XX_INV_ITEM_INTER_STG
INSERT INTO
fuj_upload_int
values('Test9'
,'Test9'
,'Address line 1'
,3349192
,'Afzal'
,'+971555'
,'+971555'
,'afzalbaig@xx.fuja.ae'
,null
,null
,null
,null
,null)
commit
--run each procedure at once to validate each and all
declare
cursor c1 is
select * from fuj_upload_int;
begin
for i in c1 loop
fuj_crt_supp_suppsite.create_supplier(i.segment1 ,i.vendor_name ,i.vendor_name );
--fuj_crt_supp_suppsite.create_supplier_site(i.segment1,'Fujairah',i.address_line1,'AE',' National Est. FNCE',i.PREPAY_CODE_COMBINATION_ID,i.email_address,i.PHONE,i.FAX);
--fuj_crt_supp_suppsite.create_supp_contact(i.segment1, i.contact,null,'Est. FNCE',i.email_address,i.PHONE,i.FAX);
end loop;
exception when others then
dbms_output.put_line('exception-'||sqlerrm);
end;
/*
select * from ap_suppliers
where segment1 = 'Test1'
select * from hz_parties
where
party_id = 10167490
*/
--------------------------------------------------------------------------------------------
-----------Supplier Site
set serveroutput on;
DECLARE
l_vendor_contact_rec ap_vendor_pub_pkg.r_vendor_contact_rec_type;
l_return_status VARCHAR2(10);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);
l_vendor_contact_id NUMBER;
l_per_party_id NUMBER;
l_rel_party_id NUMBER;
l_rel_id NUMBER;
l_org_contact_id NUMBER;
l_party_site_id NUMBER;
--Variables:
l_vendor_name varchar2(200) := 'Test4';
l_org_id varchar2(200) := 1770;
l_person_first_name varchar2(200):= 'Afzal';
l_person_last_name varchar2(200):= 'Abdur Rahman';
l_operating_unit varchar2(200) := 'Fujairah National Catering Est. FNCE'; --1770
l_email_address varchar2(200):= 'contact1@test.com';
l_phone varchar2(200):= '+971555265290';
l_fax_phone varchar2(200):= '+971555265292';
BEGIN
--
-- Required
--
SELECT vendor_id
INTO l_vendor_contact_rec.vendor_id
FROM ap_suppliers
WHERE vendor_name = l_vendor_name;
select organization_id
INTO l_vendor_contact_rec.org_id
from hr_operating_units
where upper(name) = upper(l_operating_unit) ;
l_vendor_contact_rec.person_first_name := l_person_first_name;
l_vendor_contact_rec.person_last_name := l_person_last_name;
l_vendor_contact_rec.email_address := l_email_address;
l_vendor_contact_rec.phone := l_phone;
l_vendor_contact_rec.FAX_PHONE := l_fax_phone;
--
-- Optional
--
--l_vendor_contact_rec.phone := '12343210';
--l_vendor_contact_rec.email_address := 'axyz@oracle.com';
-- etc... --
pos_vendor_pub_pkg.create_vendor_contact(
p_vendor_contact_rec => l_vendor_contact_rec,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_vendor_contact_id => l_vendor_contact_id,
x_per_party_id => l_per_party_id,
x_rel_party_id => l_rel_party_id,
x_rel_id => l_rel_id,
x_org_contact_id => l_org_contact_id,
x_party_site_id => l_party_site_id);
dbms_output.put_line('return_status: '||l_return_status);
dbms_output.put_line('msg_data: '||l_msg_data);
dbms_output.put_line('vendor_contact_id: '||l_vendor_contact_id);
dbms_output.put_line('party_site_id: '||l_party_site_id);
dbms_output.put_line('per_party_id: '||l_per_party_id);
dbms_output.put_line('rel_party_id: '||l_rel_party_id);
dbms_output.put_line('rel_id: '||l_rel_id);
dbms_output.put_line('org_contact_id: '||l_org_contact_id);
IF l_msg_count >=1 THEN FOR I IN 1..l_msg_count LOOP
dbms_output.put_line(I||'. '||SubStr(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));
END LOOP; END IF;
if (l_return_status = 'S') then
commit;
end if;
end;
--parameters can be find more from below package, as the above api is calling below package.
--ap_vendor_pub_pkg.create_vendor_contact
---------------------------------------------------------------------------------------------------------------
Adding above supplier to different org:
http://mogalafzal.blogspot.ae/2016/07/adding-new-ous-to-existing-supplier.html
post to view above 3 blocks as package and upload mutliple suppliers:
http://mogalafzal.blogspot.ae/2018/03/oracle-apps-r12-api-to-create-supplier_66.html
The Login page is made of following images:
- FNDSSCORP.gif - Oracle logo - $OA_MEDIA
- globalTop.jpg - Globe Image - $OA_MEDIA
- global.jpg - Globe Image - $OA_MEDIA
- people.jpg - People Image - $OA_MEDIA
- topLines.gif - Blue Color Shade - $OA_MEDIA
- headerBg.jpg - Blue Color shade - $OA_HTML/cabo/images/swan
- navBarUnderTopTabsBg.gif - $OA_HTML/cabo/images/swan
- footerBg.gif - Footer - $OA_HTML/cabo/images/swan
Take a backup of these images, replace these images with the images of your preference.
--select * from xxeec_cust_site_api where CREATION_STATUS='N'order by account_number; --and vendor_id in(326173);--This table contains below query data only --table details below
select * from xxeec_cust_site_api where CREATION_STATUS='N' order by account_number;
--and ACCOUNT_NUMBER in(1000,1001);--This table contains below query data only
/*select distinct hcas.CUST_ACCOUNT_ID,hcas.PARTY_SITE_ID,hp.PARTY_NAME,CUST_ACCT_SITE_ID
,hl.ADDRESS1,hl.ADDRESS2,hl.ADDRESS3,hl.ADDRESS4,hl.POSTAL_CODE,hl.STATE
,(SELECT ftt.territory_short_name
FROM fnd_territories_tl ftt, hz_locations hl
WHERE hl.country = ftt.territory_code
AND hl.location_id = hps.location_id and rownum=1) country
,(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'SHIP_TO'
and hcsua.status='A') ship_to_location,HCA.account_number
from
hz_cust_accounts hca
,HZ_CUST_ACCT_SITES_ALL hcas
,hz_parties hp
,hz_party_sites hps
,hz_locations hl
where 1=1
and hca.cust_account_id=hcas.cust_account_id
--and hcas.ORG_ID=801
and hca.PARTY_ID=hp.PARTY_ID
and hca.PARTY_ID=hps.PARTY_ID
and hps.LOCATION_ID=hl.LOCATION_ID
and hcas.ORG_ID=102
order by HCA.account_number
;*/
BEGIN
DBMS_OUTPUT.ENABLE(1000000); -- Clear DBMS_OUTPUT buffer.
ln_user_id := fnd_profile.VALUE ('USER_ID');-- Get the user_id
ln_org_id := fnd_profile.VALUE ('ORG_ID'); -- Get the Org_id
ln_resp_id := fnd_profile.VALUE ('RESP_ID');-- Get the Resp_id
FND_FILE.put_line(FND_FILE.output,'user id '||ln_user_id);
FND_FILE.put_line(FND_FILE.output,'org id ' ||ln_org_id);
FND_FILE.put_line(FND_FILE.output,'resp id '||ln_resp_id);
--Initializing the variables
--Initializing the variables
fnd_global.apps_initialize (user_id => 2605,--ln_user_id,
resp_id => 50603,--ln_resp_id, --50639,
resp_appl_id => 222--800
);
--mo_global.init ('PER');
mo_global.set_policy_context ('S', 1770);--ln_org_id);
For REC IN C1
Loop
begin
p_cust_acct_site_rec.cust_account_id := REC.cust_account_id; --219251;
p_cust_acct_site_rec.party_site_id := rec.party_site_id; --212126;
p_cust_acct_site_rec.org_id := P_ORGID;
--p_cust_acct_site_rec.location_id := 6640;
--p_cust_acct_site_rec.language := 'US';
--The Above Language Column is Obsolete in R12
p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
CREATE TABLE APPS.XXEEC_CUST_SITE_API
(
CUST_ACCOUNT_ID NUMBER(15) NOT NULL,
PARTY_SITE_ID NUMBER(15) NOT NULL,
PARTY_NAME VARCHAR2(360 BYTE) NOT NULL,
CUST_ACCT_SITE_ID NUMBER(15) NOT NULL,
ADDRESS1 VARCHAR2(240 BYTE) NOT NULL,
ADDRESS2 VARCHAR2(240 BYTE),
ADDRESS3 VARCHAR2(240 BYTE),
ADDRESS4 VARCHAR2(240 BYTE),
POSTAL_CODE VARCHAR2(60 BYTE),
STATE VARCHAR2(60 BYTE),
COUNTRY VARCHAR2(80 BYTE),
BILL_TO_LOCATION VARCHAR2(40 BYTE),
SHIP_TO_LOCATION VARCHAR2(40 BYTE),
ACCOUNT_NUMBER VARCHAR2(30 BYTE) NOT NULL,
SITE_CREATION_DATE DATE,
CREATION_STATUS VARCHAR2(10 BYTE),
ERROR_MESSAGE VARCHAR2(240 BYTE)
)
truncate table XXEEC_CUST_SITE_API
commit
INSERT INTO XXEEC_CUST_SITE_API
select distinct hcas.CUST_ACCOUNT_ID,hcas.PARTY_SITE_ID,hp.PARTY_NAME,CUST_ACCT_SITE_ID
,hl.ADDRESS1,hl.ADDRESS2,hl.ADDRESS3,hl.ADDRESS4,hl.POSTAL_CODE,hl.STATE
,(SELECT ftt.territory_short_name
FROM fnd_territories_tl ftt, hz_locations hl
WHERE hl.country = ftt.territory_code
AND hl.location_id = hps.location_id and rownum=1) country
,(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'BILL_TO') bill_to_location,
(SELECT DISTINCT hcsua.LOCATION
FROM hz_cust_site_uses_all hcsua
WHERE hcsua.cust_acct_site_id = hcas.cust_acct_site_id
AND hcsua.site_use_code = 'SHIP_TO'
and hcsua.status='A') ship_to_location,
lpad(HCA.account_number,7) account_number
,null ABC,'N',null DEF
from
hz_cust_accounts hca
,HZ_CUST_ACCT_SITES_ALL hcas
,hz_parties hp
,hz_party_sites hps
,hz_locations hl
where 1=1
and hca.cust_account_id=hcas.cust_account_id
and hcas.ORG_ID=1770 --existing customers orgID
and hca.PARTY_ID=hp.PARTY_ID
and hca.PARTY_ID=hps.PARTY_ID
and hps.LOCATION_ID=hl.LOCATION_ID
--AND ROWNUM <=50
--and hcas.ORG_ID=102
AND ACCOUNT_NUMBER <> 'FNCE1'
order by lpad(HCA.account_number,7)
---------------------------------------------
To Select the Customer Account Id run the query:
SELECT cust_account_id, created_by_module, party_id--, org_Id
FROM hz_cust_accounts
where account_number like 'FNCE1'
To Select the Party Site Id run the Query :
SELECT *--party_site_id
FROM hz_party_sites
--where creation_date like sysdate
where party_id = 10166487
select * from
hz_parties
where party_id = 10167321
select * from XXEEC_CUST_SITE_API
where creation_status <> 'Y'
SELECT cust_acct_site_id,
cust_account_id,
party_site_id,
org_id, status, bill_to_flag, ship_to_flag, created_by_module,application_id, creation_date
FROM hz_cust_acct_sites_all
where cust_account_id IN
(SELECT cust_account_id from
XXEEC_CUST_SITE_API)
and org_id = 1733
