CREATE TABLE xx_CUST_LOC_PIN_UPDATE_STG
(
OPERATING_UNIT VARCHAR2(500 BYTE),
CUSTOMER_NAME VARCHAR2(50 BYTE),
CUSTOMER_NUMBER VARCHAR2(30 BYTE),
CUSTOMER_GROUP VARCHAR2(500 BYTE),
CUSTOMER_SITE VARCHAR2(500 BYTE),
PIN_CODE VARCHAR2(60 BYTE),
STATUS_FLAG VARCHAR2(10 BYTE),
STATUS_MESSAGE VARCHAR2(2000 BYTE),
PROCESS_FLAG VARCHAR2(10 BYTE)
)
CREATE OR REPLACE PROCEDURE APPS.xx_cust_loc_pin_update_prc
IS
---DECLARE
p_location_rec hz_location_v2pub.location_rec_type;
p_object_version_number NUMBER := 1;
x_return_status VARCHAR2 (2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_error_message VARCHAR2 (2000);
l_location_id NUMBER;
l_status_flag VARCHAR2 (10);
l_object_version_number NUMBER := 1;
CURSOR c1
IS
SELECT stg.ROWID, stg.*
FROM xx_cust_loc_pin_update_stg stg ;-- where CUSTOMER_GROUP='ATITH FIBER PVT. LTD.';
BEGIN
fnd_global.apps_initialize (user_id => 1130,
resp_id => 50855,
resp_appl_id => 222
);
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
-- Initializing the Mandatory API parameters
FOR i IN c1
LOOP
IF c1%NOTFOUND
THEN
COMMIT;
CONTINUE;
END IF;
l_error_message := NULL;
l_status_flag := NULL;
DBMS_OUTPUT.put_line ('Looop started..');
BEGIN
SELECT DISTINCT hps.location_id
INTO l_location_id
FROM apps.hz_parties hp,
apps.hz_cust_accounts hca,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_acct_sites_all hcasa1,
hr_operating_units ood,
hz_cust_site_uses_all hsu
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND nvl(party_site_name,hp.province)=i.customer_site
AND hps.location_id = hl.location_id
AND hcasa1.party_site_id = hps.party_site_id
AND ood.NAME = i.operating_unit
AND ood.organization_id = hcasa1.org_id
AND account_number = i.customer_number
AND hsu.cust_acct_site_id = hcasa1.cust_acct_site_id;
DBMS_OUTPUT.put_line ('location ' || l_location_id);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_status_flag := 'E';
UPDATE xx_cust_loc_pin_update_stg
SET status_flag = 'E',
status_message = 'Customer Site not found',
process_flag = 'I'
WHERE ROWID = i.ROWID;
DBMS_OUTPUT.put_line ('Customer Site not found '
|| i.customer_site
);
COMMIT;
CONTINUE;
WHEN TOO_MANY_ROWS
THEN
l_status_flag := 'E';
UPDATE xx_cust_loc_pin_update_stg
SET status_flag = 'E',
status_message = 'Customer Site not found-too many rows',
process_flag = 'I'
WHERE ROWID = i.ROWID;
DBMS_OUTPUT.put_line ( 'Customer Site not found too many'
|| i.customer_site
);
COMMIT;
CONTINUE;
END;
IF l_location_id IS NOT NULL
THEN
SELECT object_version_number
INTO l_object_version_number
FROM hz_locations
WHERE location_id = l_location_id;
END IF;
DBMS_OUTPUT.put_line ('before process ' || l_location_id);
DBMS_OUTPUT.put_line ('l_status_flag ' || l_status_flag);
IF l_status_flag IS NULL
THEN
DBMS_OUTPUT.put_line ('Validated ' || l_location_id);
p_location_rec.location_id := l_location_id;
p_location_rec.postal_code := i.pin_code;
p_object_version_number := l_object_version_number;
DBMS_OUTPUT.put_line
('Calling the API hz_location_v2pub.update_location');
hz_location_v2pub.update_location
(p_init_msg_list => fnd_api.g_true,
p_location_rec => p_location_rec,
p_object_version_number => p_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
COMMIT;
DBMS_OUTPUT.put_line ('x_return_status' || x_return_status);
IF x_return_status = fnd_api.g_ret_sts_success
THEN
-- COMMIT;
DBMS_OUTPUT.put_line ('Creation of Location is Successful ');
DBMS_OUTPUT.put_line ('Output information ....');
DBMS_OUTPUT.put_line ('New Address1: ' || p_location_rec.address1);
DBMS_OUTPUT.put_line ( 'p_object_version_number = '
|| p_object_version_number
);
UPDATE xx_cust_loc_pin_update_stg
SET status_flag = 'S',
status_message = 'Sucess',
process_flag = 'C'
WHERE ROWID = i.ROWID;
COMMIT;
CONTINUE;
DBMS_OUTPUT.put_line ('sucess ' || x_return_status);
ELSE
DBMS_OUTPUT.put_line ('Creation of Location 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;
l_error_message := SQLCODE || SQLERRM || x_msg_data;
DBMS_OUTPUT.put_line ('error-2 ' || l_error_message);
UPDATE xx_cust_loc_pin_update_stg
SET status_flag = 'E',
status_message = l_error_message,
process_flag = 'I';
DBMS_OUTPUT.put_line ('error-2 ' || l_error_message);
COMMIT;
CONTINUE;
END IF;
DBMS_OUTPUT.put_line ('Completion of API');
END IF;
END LOOP;
END;
/
