Customer Interface Program

/********************************************************************************/
/* */
/* Script file for Customer interface. */
/* Created by Srikanth Kotipalli. */
/* Created on 12/31/2008. */
/* */
/********************************************************************************/

/* Staging Table. */
CREATE TABLE xxtr_ksk_cust_data( cust_number VARCHAR2(240)
, cust_name VARCHAR2(360)
, address1 VARCHAR2(240)
, address2 VARCHAR2(240)
, city VARCHAR2(60)
, state VARCHAR2(60)
, country VARCHAR2(60)
, cust_ship_via_code VARCHAR2(30)
, orig_system_address_ref VARCHAR2(240)
, site_use_code VARCHAR2(30)
, profile_class VARCHAR2(30) DEFAULT 'DEFAULT'
, status VARCHAR2(25) DEFAULT 'Not Processed'
, description VARCHAR2(100)
);
/


/*Insert data into Staging Table.*/
INSERT INTO xxtr_ksk_cust_data values(2,'XYZ','GA-16, SBH COLONY','DILSUKHNAGAR','HYDERABAD','AP','IN','DHL','2','SOLD_TO','Average');
/

/*Package Specification.*/
CREATE OR REPLACE PACKAGE xxtr_ksk_cust_pkg
IS
PROCEDURE xxtr_ksk_cust_insert(errbuf OUT VARCHAR2,retcode OUT NUMBER);
END xxtr_ksk_cust_pkg;
/

/*Package Body*/
CREATE OR REPLACE PACKAGE BODY xxtr_ksk_cust_pkg
IS
/*Procedure to insert data from staging table to interface table.*/
PROCEDURE xxtr_ksk_cust_insert (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
/* Local variable declaration.*/
lv_org_id NUMBER (15);
lv_count_site_use NUMBER (3);
lv_count_profile_class NUMBER (3);
lv_req_id NUMBER (15);
lv_cust_count NUMBER (3);
lv_count NUMBER (3) := 0;
lv_description VARCHAR2(100);

/* Cursor Declaration.*/
CURSOR cur_cust_data
IS
SELECT *
FROM xxtr_ksk_cust_data
WHERE status <> 'Processed' OR status is null;
BEGIN
lv_org_id := fnd_profile.VALUE ('org_id'); --To get Org_Id.

/* Cursor for loop to fetch the data from staging table.*/
FOR cur_cust_data_rec IN cur_cust_data
LOOP
BEGIN
/* Select statement to check whether the site_use_code is valid or not.*/
SELECT COUNT (1)
INTO lv_count_site_use
FROM ar_lookups
WHERE lookup_type = 'SITE_USE_CODE'
AND lookup_code = cur_cust_data_rec.site_use_code;

/* Select statement to check whether the profile class name is valid or not.*/
SELECT COUNT (1)
INTO lv_count_profile_class
FROM hz_cust_profile_classes
WHERE NAME = cur_cust_data_rec.profile_class AND status = 'A';

SELECT COUNT (1)
INTO lv_cust_count
FROM ar_customers
WHERE customer_name = cur_cust_data_rec.cust_name
AND status = 'A';

IF (lv_cust_count = 0)
THEN
IF (lv_count_site_use > 0)
THEN
/*Insert data into interface table ra_customers_interface_all from staging table.*/
INSERT INTO ra_customers_interface_all ( orig_system_customer_ref
, insert_update_flag
, customer_name
, primary_site_use_flag
, customer_status
, address1
, address2
, city
, state
, country
, cust_ship_via_code
, last_updated_by
, last_update_date
, created_by
, creation_date
, org_id
, orig_system_address_ref
, site_use_code
)
VALUES ( cur_cust_data_rec.cust_number
, 'I'
, cur_cust_data_rec.cust_name
, 'Y'
, 'A'
, cur_cust_data_rec.address1
, cur_cust_data_rec.address2
, cur_cust_data_rec.city
, cur_cust_data_rec.state
, cur_cust_data_rec.country
, cur_cust_data_rec.cust_ship_via_code
, -1
, SYSDATE
, -1
, SYSDATE
, lv_org_id
, cur_cust_data_rec.orig_system_address_ref
, cur_cust_data_rec.site_use_code
);

IF (lv_count_profile_class > 0)
THEN
/* Insert data into interface table ra_customer_profiles_interface from staging table.*/
INSERT INTO ra_customer_profiles_int_all ( orig_system_customer_ref
, insert_update_flag
, credit_hold
, created_by
, creation_date
, last_updated_by
, last_update_date
, customer_profile_class_name
, org_id
)
VALUES ( cur_cust_data_rec.cust_number
, 'I'
, 'Y'
, -1
, SYSDATE
, -1
, SYSDATE
, cur_cust_data_rec.profile_class
, lv_org_id
);

/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Processed'
, description = 'Valid'
WHERE cust_number = cur_cust_data_rec.cust_number;

lv_count := lv_count + 1;
ELSE
fnd_file.put_line (fnd_file.LOG,
'Profile class is not valid'
);

/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Rejected'
, description='Profile class is not valid'
WHERE cust_number = cur_cust_data_rec.cust_number;


END IF;
ELSE
fnd_file.put_line
(fnd_file.LOG,
'site_use_code or profile class is not valid'
);

/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Rejected'
, description='Site use code is not valid'
WHERE cust_number = cur_cust_data_rec.cust_number;


END IF;
ELSE
fnd_file.put_line (fnd_file.LOG,
'The customer already exists for customer number :'||cur_cust_data_rec.cust_number);

lv_description:=lv_description||'Customer already exists';
/* Updating status of the staging table for the record.*/
UPDATE xxtr_ksk_cust_data
SET status = 'Rejected'
, description:='Customer already exists'
WHERE cust_number = cur_cust_data_rec.cust_number;


END IF;
END;
END LOOP; --End of Cursor-For loop.
COMMIT;

IF (lv_count <> 0)
THEN /*Submiting the program for customer interface.*/
lv_req_id :=
fnd_request.submit_request (application => 'AR',
program => 'RACUST',
argument1 => 'N'
);
END IF;

IF (lv_req_id > 0)
THEN
fnd_file.put_line (fnd_file.LOG,
'concurrent program is successfully submitted'
);
ELSE
fnd_file.put_line (fnd_file.LOG,
'concurrent program is not submitted'
);
END IF;
END xxtr_ksk_cust_insert; --End of procedure.
END xxtr_ksk_cust_pkg; --End of Package body.
/