--find org
SELECT OOD.OPERATING_UNIT OU_ID ,
HRO.NAME OU_NAME,
OOD.ORGANIZATION_ID,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_NAME
FROM APPS.ORG_ORGANIZATION_DEFINITIONS OOD ,
HR.HR_ALL_ORGANIZATION_UNITS HRO
WHERE OOD.OPERATING_UNIT = HRO.ORGANIZATION_ID
ORDER BY HRO.NAME,OOD.ORGANIZATION_CODE
--find queries
--Calling the API hz_cust_account_v2pub.create_cust_account
--Creation of Party and customer account is Successful
--Output information ....
--x_cust_account_id : 13054
--x_account_number : 5001
--x_party_id : 101571
--x_party_number : 23107
--x_profile_id : 53492
select * from ar_customers where customer_name like 'A.SAI GUNARANJAN (UNREG0293S)%';
-- customer_id 10064,
--customer number 2005
select * from hz_parties where party_name like '%A.SAI GUNARANJAN (UNREG0293S)%' ;
-- party_id 87576,
--party Number 18421
SELECT * FROM hz_party_sites hps WHERE PARTY_ID = 101571;
--PARTY_SITE_ID = 56701; PARTY_SITE_NUMBER 51618
--PARTY_SITE_ID = 54711; PARTY_SITE_NUMBER 49623
SELECT * FROM hz_party_sites hps WHERE PARTY_SITE_ID = 65701;
--Party Site Id = 65701
--Party Site Number = 60618
SELECT * FROM hz_cust_accounts hca WHERE PARTY_ID = 101571;
--customer_account_id = 10064
-- ACCOUNT_NUMBER 2005
SELECT * FROM hz_cust_acct_sites_all hcas WHERE CUST_ACCOUNT_ID = 13054;
--CUST_ACCT_SITE_ID 12063 -- PARTY_SITE_ID 54711
--CUST_ACCT_SITE_ID 13058 -- PARTY_SITE_ID 56701
select * from HZ_CUSTOMER_PROFILES where cust_account_id = 13054
select * from HZ_CUSTOMER_PROFILES where CUST_ACCOUNT_PROFILE_ID = 53492
select * from ra_terms
select * from CUST_DATA_CKBP_STG where x_customer_number = 5001
SELECT * FROM CUST_DATA_SHIVNG_STG
SELECT * FROM CUST_DATA_CKBP_STG WHERE X_CUSTOMER_NUMBER =300696
SELECT * FROM CUST_DATA_SHIVNG_STG WHERE X_CUSTOMER_NUMBER =300696
FND_GLOBAL.APPS_INITIALIZE (0, 50857, 7000);
WHERE
BEGIN
CUSTOMER_VALIDATEX;
END;
BEGIN
CUSTOMER_LOADX;
END;
BEGIN
DELETE CUST_DATA_CKBP_STG;
COMMIT;
END;
SELECT * FROM CUST_DATA_STG
SELECT * FROM XXAVX_CUSTOMERS_STG
select * from ar_customers where customer_name = 'AV Customer1' -- 11055 -- ABCDE5678Z
select * from hz_parties where PARTY_ID =89595 -- HZ_CPUI
SELECT * FROM hz_party_sites hps WHERE PARTY_ID = 89595; --PARTY_SITE_ID = 45725;
SELECT * FROM hz_cust_accounts hca WHERE PARTY_ID = 89595;
SELECT * FROM hz_cust_acct_sites_all hcas WHERE CUST_ACCOUNT_ID = 12076;
SELECT * FROM HZ_LOCATIONS WHERE LOCATION_ID = 12054
SELECT * FROM HZ_LOCATIONS WHERE ADDRESS1 LIKE 'Shivajinagar' order by creation_date desc --Location id =26835
SELECT * FROM HZ_LOCATIONS WHERE ADDRESS1 LIKE 'Chickaballapura' order by creation_date desc -- Location id = 23134
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd
, fnd_responsibility_tl fresp
where fnd.user_name = 'SYSADMIN'
and fresp.responsibility_name = 'BESCOM AR Super User (Chikkaballapur)';
--Sequence
CREATE SEQUENCE CUST_DATA_SEQ
INCREMENT BY 1
START WITH 300000
MINVALUE 300000
MAXVALUE 999999
NOCYCLE;
--TABLE
DROP TABLE apps.CUST_DATA_CKBP_STG;
CREATE TABLE apps.CUST_DATA_CKBP_STG
(
RECORD_NO NUMBER,
CUSTOMER_NAME VARCHAR2 (350),
CUSTOMER_SHORT_NAME VARCHAR2 (350),
PAN_NUMBER VARCHAR2 (25)--,
--OPERATING_UNIT VARCHAR2 (30),
--CUSTOMER_SITE VARCHAR2 (30),
--COUNTRY VARCHAR2 (25),
--ADDRESS_LINE1 VARCHAR2 (30),
--CITY VARCHAR2 (25),
--STATE VARCHAR2 (25),
--PAYMENT_TERMS VARCHAR2 (25)
);
CREATE SEQUENCE CUST_DATA_SEQ
INCREMENT BY 1
START WITH 100000
MINVALUE 100000
MAXVALUE 999999
CYCLE;
ALTER TABLE CUST_DATA_CKBP_STG ADD PROCESS_FLAG VARCHAR2(10) DEFAULT 'N';
ALTER TABLE CUST_DATA_CKBP_STG ADD ERROR_MSG VARCHAR2(250);
ALTER TABLE CUST_DATA_CKBP_STG ADD X_PARTY_ID NUMBER;
ALTER TABLE CUST_DATA_CKBP_STG ADD X_PARTY_SITE_ID NUMBER;
ALTER TABLE CUST_DATA_CKBP_STG ADD X_CUST_ACCOUNT_ID NUMBER;
ALTER TABLE CUST_DATA_CKBP_STG ADD X_LOCATION_ID NUMBER;
ALTER TABLE CUST_DATA_CKBP_STG ADD X_PROFILE_ID NUMBER;
ALTER TABLE CUST_DATA_CKBP_STG ADD X_PARTY_NUMBER VARCHAR2(30);
ALTER TABLE CUST_DATA_CKBP_STG ADD X_PARTY_SITE_NUMBER VARCHAR2(30);
ALTER TABLE CUST_DATA_CKBP_STG ADD X_CUSTOMER_NUMBER VARCHAR2(30);
ALTER TABLE CUST_DATA_CKBP_STG ADD X_CUST_ACCOUNT_NUMBER VARCHAR2(30);
ALTER TABLE CUST_DATA_CKBP_STG ADD x_cust_acct_site_id VARCHAR2(30);
ALTER TABLE CUST_DATA_CKBP_STG ADD X_SITE_USE_CODE_BILL_TO NUMBER;
ALTER TABLE CUST_DATA_CKBP_STG ADD X_SITE_USE_CODE_SHIP_TO NUMBER;
--INSERT
BEGIN
INSERT INTO CUST_DATA_CKBP_STG VALUES (CUST_DATA_SEQ.NEXTVAL,'KPTCL Accounts Offic (AAAAK0296M)','KPTCL Accounts Offic','AAAAK0296M');
INSERT INTO CUST_DATA_CKBP_STG VALUES (CUST_DATA_SEQ.NEXTVAL,'MANAGING DIRECTOR KM (AAAAK1110G)','MANAGING DIRECTOR KM','AAAAK1110G');
INSERT INTO CUST_DATA_CKBP_STG VALUES (CUST_DATA_SEQ.NEXTVAL,'GENERAL SECRETARY KP (AAAAK5218R)','GENERAL SECRETARY KP','AAAAK5218R');
INSERT INTO CUST_DATA_CKBP_STG VALUES (CUST_DATA_SEQ.NEXTVAL,'ABB INDIA LIMITED (AAACA3834B)','ABB INDIA LIMITED','AAACA3834B');
INSERT INTO CUST_DATA_CKBP_STG VALUES (CUST_DATA_SEQ.NEXTVAL,'Bharti Air Tel Ltd (AAACB2894G)','Bharti Air Tel Ltd','AAACB2894G');
END;
API1: 01E_HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT(CHIKKABALLAPUR)
/* Formatted on 26/09/2022 14:46:34 (QP5 v5.336) */
DECLARE
p_cust_account_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
p_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
p_customer_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
x_cust_account_id NUMBER;
x_account_number VARCHAR2 (2000);
x_party_id NUMBER;
x_party_number VARCHAR2 (2000);
x_profile_id NUMBER;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
v_profile_class_id hz_cust_profile_classes.profile_class_id%TYPE;
CURSOR cur_customer IS
SELECT *
FROM CUST_DATA_CKBP_STG
WHERE PROCESS_FLAG = 'N';
BEGIN
FND_GLOBAL.APPS_INITIALIZE (0, 50859, 7000);
FOR v_cust_rec IN cur_customer
LOOP
v_profile_class_id := 0;
p_cust_account_rec.account_name := v_cust_rec.CUSTOMER_NAME;--'API_ACC';
p_cust_account_rec.created_by_module := 'TCA_V2_API';
p_organization_rec.organization_name := v_cust_rec.CUSTOMER_NAME;
p_organization_rec.known_as := v_cust_rec.CUSTOMER_SHORT_NAME;
p_organization_rec.tax_reference := v_cust_rec.pan_number;
p_organization_rec.created_by_module := 'TCA_V2_API';
p_customer_profile_rec.profile_class_id := v_profile_class_id;
DBMS_OUTPUT.PUT_LINE (
'Calling the API hz_cust_account_v2pub.create_cust_account');
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT (
p_init_msg_list => FND_API.G_TRUE,
p_cust_account_rec => p_cust_account_rec,
p_organization_rec => p_organization_rec,
p_customer_profile_rec => p_customer_profile_rec,
p_create_profile_amt => FND_API.G_FALSE,
x_cust_account_id => x_cust_account_id,
x_account_number => x_account_number,
x_party_id => x_party_id,
x_party_number => x_party_number,
x_profile_id => x_profile_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
COMMIT;
DBMS_OUTPUT.PUT_LINE ('Creation of Party and customer account is Successful ');
DBMS_OUTPUT.PUT_LINE ('Output information ....');
DBMS_OUTPUT.PUT_LINE ('x_cust_account_id : ' || x_cust_account_id);
DBMS_OUTPUT.PUT_LINE ('x_account_number : ' || x_account_number);
DBMS_OUTPUT.PUT_LINE ('x_party_id : ' || x_party_id);
DBMS_OUTPUT.PUT_LINE ('x_party_number : ' || x_party_number);
DBMS_OUTPUT.PUT_LINE ('x_profile_id : ' || x_profile_id);
UPDATE CUST_DATA_CKBP_STG
SET process_flag = 'P',
x_party_id = x_party_id,
x_party_number = x_party_number,
x_cust_account_id = x_cust_account_id,
x_customer_number = x_account_number,
error_msg = x_msg_data
WHERE RECORD_NO = v_cust_rec.RECORD_NO;
COMMIT;
ELSE
UPDATE CUST_DATA_CKBP_STG
SET process_flag = 'E',
error_msg = x_msg_data
WHERE RECORD_NO = v_cust_rec.RECORD_NO;
DBMS_OUTPUT.put_line ( 'Creation of Party and customer account failed:'
|| x_msg_data);
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data :=
oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');
DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Completion of API');
END;
/
API 2: 02A-UPDATING ID'S FOR CHIKKABALLAPURA
/* Formatted on 15-09-2022 23:21:30 (QP5 v5.336) */
DECLARE
CURSOR C1 IS
SELECT *
FROM ar_customers;
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET x_cust_account_id = I.CUSTOMER_ID
WHERE X_CUSTOMER_NUMBER = I.CUSTOMER_NUMBER;
COMMIT;
END LOOP;
END;
-----------------------------------------------------------
/* Formatted on 15-09-2022 23:21:30 (QP5 v5.336) */
DECLARE
CURSOR C1 IS
SELECT *
FROM hz_parties;
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET X_PARTY_ID = I.PARTY_ID,
X_PARTY_NUMBER = I.PARTY_NUMBER
WHERE PAN_NUMBER = I. TAX_REFERENCE;
COMMIT;
END LOOP;
END;
--------------------------Updating Chickaballapura location id ------------------------
--SELECT * FROM HZ_LOCATIONS WHERE ADDRESS1 LIKE 'Shivajinagar' order by creation_date desc --Location id =26835
--SELECT * FROM HZ_LOCATIONS WHERE ADDRESS1 LIKE 'Chickaballapura' order by creation_date desc -- Location id = 23134
DECLARE
CURSOR C1 IS
SELECT *
FROM CUST_DATA_CKBP_STG;
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET X_LOCATION_ID = 23134
WHERE X_PARTY_ID = I.X_PARTY_ID;
COMMIT;
END LOOP;
END;
-------------------------------------------------------------
DECLARE
CURSOR C1 IS
SELECT *
FROM hz_party_sites;
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET x_party_site_id = I.party_site_id,
x_party_site_number = I.party_site_number
WHERE X_PARTY_ID = I.PARTY_ID;
COMMIT;
END LOOP;
END;
-------------------------------
DECLARE
CURSOR C1 IS
SELECT *
FROM hz_cust_acct_sites_all;
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET x_CUST_ACCT_SITE_ID = I.CUST_ACCT_SITE_ID
WHERE X_CUST_ACCOUNT_ID = I.CUST_ACCOUNT_ID
AND X_PARTY_SITE_ID = I.PARTY_SITE_ID;
COMMIT;
END LOOP;
END;
---------------------ship to bill to use id update-------------------------------------------
DECLARE
CURSOR C1 IS
SELECT *
FROM hz_cust_site_uses_all WHERE SITE_USE_CODE = 'BILL_TO';
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET X_SITE_USE_CODE_BILL_TO = I.SITE_USE_ID
WHERE X_CUST_ACCT_SITE_ID = I.CUST_ACCT_SITE_ID;
COMMIT;
END LOOP;
END;
DECLARE
CURSOR C1 IS
SELECT *
FROM hz_cust_site_uses_all WHERE SITE_USE_CODE = 'SHIP_TO';
BEGIN
FOR i IN c1
LOOP
UPDATE CUST_DATA_CKBP_STG
SET X_SITE_USE_CODE_SHIP_TO = I.SITE_USE_ID
WHERE X_CUST_ACCT_SITE_ID = I.CUST_ACCT_SITE_ID;
COMMIT;
END LOOP;
END;
API 5: 05A_HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE (CHIKKABALLAPURA OPERATING UNIT)
/* Formatted on 26/09/2022 18:04:06 (QP5 v5.336) */
/*DESCRIPTION:
This procedure creates a site for the customer account using an existing site.
API: HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE
BASE TABLES AFFECTED : HZ_CUST_ACCT_SITES
TEST INSTANCE : R12.1.3
NOTE:
The language column is obsolete. You are not allowed to pass a value to this column.
To Select the Customer Account Id run the query:
SELECT cust_account_id
FROM hz_cust_accounts;
To Select the Party Site Id run the Query :
SELECT party_site_id
FROM hz_party_sites;
*/
--SCRIPT:
--SET SERVEROUTPUT ON;
DECLARE
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_cust_acct_site_id NUMBER;
CURSOR cur_customer IS select * from CUST_DATA_CKBP_STG where PROCESS_FLAG = 'P';
BEGIN
-- Setting the Context --
mo_global.init ('AR');
fnd_global.apps_initialize ( user_id => 0
,resp_id => 50859
,resp_appl_id => 7000);
mo_global.set_policy_context ('S', 83); ---Chikkaballapura OU
fnd_global.set_nls_context ('AMERICAN');
FOR v_cust_rec IN cur_customer
LOOP
-- Initializing the Mandatory API parameters
p_cust_acct_site_rec.cust_account_id := v_cust_rec.X_CUST_ACCOUNT_ID;--12076;
p_cust_acct_site_rec.party_site_id := v_cust_rec.X_PARTY_SITE_ID; --56718;
p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
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);
DBMS_OUTPUT.put_line (
'x_return_status = ' || SUBSTR (x_return_status, 1, 255));
DBMS_OUTPUT.put_line ('x_msg_count = ' || TO_CHAR (x_msg_count));
DBMS_OUTPUT.put_line ('x_msg_data = ' || SUBSTR (x_msg_data, 1, 255));
IF x_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE ('Creation of Customer Account Site is Successful ');
DBMS_OUTPUT.PUT_LINE ('Output information ....');
DBMS_OUTPUT.PUT_LINE ( 'Customer Account Site Id is = ' || x_cust_acct_site_id);
UPDATE CUST_DATA_CKBP_STG
SET x_cust_acct_site_id = v_cust_rec.x_cust_acct_site_id
WHERE RECORD_NO= v_cust_rec.RECORD_NO;
COMMIT;
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');
DBMS_OUTPUT.put_line (i || ') ' || x_msg_data);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE ('Completion of API');
end loop;
END;
/
--VERFICATION SCRIPT:
/*
SELECT cust_acct_site_id,
cust_account_id,
party_site_id,
org_id
FROM hz_cust_acct_sites_all
WHERE cust_acct_site_id = 14058;
*/
API 6: 06A_HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE (BILL_TO, SHIP_TO) (CHIKKABALLAPURA OPERATING UNIT)