CREATE OR REPLACE PACKAGE BODY APPS.XX_TP_DMIG_LEAD_PROFILES_PKG1
AS
error_message VARCHAR2 (30000);
PROCEDURE xx_tp_ar_cust_val_proc1;
PROCEDURE xx_tp_ar_cust_process_proc1;
/* Cursor to display the Status Report of all the records*/
CURSOR cur_sum
IS
SELECT COUNT (1) num,
err_status,
NVL (err_msg, 'Validated Records') err_msg
FROM XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
GROUP BY err_status, err_msg;
CURSOR cur_sum1
IS
SELECT COUNT (1) num, err_status
-- , NVL (err_msg, 'Processed Records') err_msg
FROM XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
GROUP BY err_status;
-- , err_msg;
/* Replacing the Fnd_File.out */
PROCEDURE PRINT (p_in IN VARCHAR2);
PROCEDURE PRINT (p_in IN VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.output, p_in);
DBMS_OUTPUT.put_line (p_in);
END;
PROCEDURE main (retbuf OUT VARCHAR2,
retcode OUT NUMBER,
process_flag IN VARCHAR2)
IS
BEGIN
/*==============================================
* Purpose : Updating the err status to V in
XX_TP_DMIG_LEAD_PROFILES_STG once the records
are validated.
* =============================================*/
IF process_flag = 'V'
THEN
--
--
fnd_file.put_line (fnd_file.LOG, ' Validation started ');
xx_tp_ar_cust_val_proc1;
/*=============================================
* Purpose : Updating the err status to S in
XX_TP_DMIG_LEAD_PROFILES_STG once the
records are Processed
* =============================================*/
ELSIF process_flag = 'P'
THEN
fnd_file.put_line (fnd_file.LOG, ' PROCESSING DATA started ');
--
-- Process the data
--
xx_tp_ar_cust_process_proc1;
fnd_file.put_line (fnd_file.LOG, ' END PROCESSING DATA');
ELSE
--
-- Validatiopn and Process
--
NULL;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in main due to :' || SQLERRM);
END main; -- End for the main
/*=============================================
* Procedure : XX_TP_AR_CUST_VAL_PROC
* Purpose : This procedure validates the data in staging
Table XX_TP_DMIG_LEAD_PROFILES_STG and updates the
data err_status and err_msg with 'E'(Errored)
or V(once successfully validated) with respective
error message.
* =============================================*/
PROCEDURE xx_tp_ar_cust_val_proc1
IS
x_cntry_code fnd_territories.territory_code%TYPE;
x_site_code ar_lookups.lookup_code%TYPE;
x_stat_code ar_lookups.lookup_code%TYPE;
x_credit_rate ar_lookups.lookup_code%TYPE;
CURSOR cur_validate
IS
SELECT stg.ROWID, stg.*
FROM XXCUS.xx_tp_dmig_lead_profiles_stg1 stg
WHERE NVL (err_status, 'X') <> 'S';
BEGIN
PRINT (' ******************************** ');
PRINT (' Validation Stage for CUSTOMER ');
PRINT (' ******************************** ');
PRINT (' ');
PRINT ('ERROR DETAILS: ');
PRINT ('************* ');
PRINT (' ');
FOR rec_validate IN cur_validate
LOOP
error_message := NULL;
rec_validate.err_status := NULL;
rec_validate.err_status := 'V';
/* =============================================
* Purpose : Verify wherther the Party name is
defined or not
* =============================================*/
IF rec_validate.original_name IS NULL
THEN
error_message := 'Party name is mandatory';
rec_validate.err_status := 'E';
END IF;
/* =============================================
* Purpose : Verify whether the Country is
defined in Fnd_Territories or not.
* =============================================*/
BEGIN
SELECT territory_code
INTO x_cntry_code
FROM fnd_territories
WHERE territory_code = rec_validate.country_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
error_message :=
error_message || ',' || 'Invalid territory code';
rec_validate.err_status := 'E';
PRINT (
rec_validate.first_name || ' : Invalid territory code'
);
WHEN OTHERS
THEN
error_message :=
error_message
|| ','
|| 'Error while validating the territory code due to : '
|| SQLERRM;
rec_validate.err_status := 'E';
END;
/* =============================================
* Purpose : Updating the Error Status and
Error Message in the Staging table.
* =============================================*/
UPDATE XXCUS.XX_TP_DMIG_LEAD_PROFILES_STG1 x
SET err_msg = error_message,
err_status = rec_validate.err_status
WHERE x.ROWID = rec_validate.ROWID;
END LOOP;
COMMIT;
PRINT (' ');
PRINT ('SUMMARY OF VALIDATION ');
PRINT ('********************* ');
PRINT (' ');
PRINT ('COUNT STATUS ERROR MESSAGE');
PRINT ('---- ------ -------------');
FOR rec_sum IN cur_sum
LOOP
PRINT( RPAD (TO_CHAR (rec_sum.num), 12, ' ')
|| RPAD (rec_sum.err_status, 12, ' ')
|| rec_sum.err_msg);
END LOOP;
PRINT ('**** END OF THE REPORT *****');
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.output,
'Error while validating the data due to: ' || SQLERRM
);
END xx_tp_ar_cust_val_proc1; /* End of validation */
--
-- Processing the Valid data
/* =============================================
* Procedure : XXCSC_AR_CUST_VAL_PROC
* Purpose : This procedure processses the data into R12 base
Tables from staging table and updates the err_status
to 'S' once it is processed into hz base tables.
if any error occurs updates err_status and err_msg
with 'E'(Errored) with respective error message.
* =============================================*/
PROCEDURE xx_tp_ar_cust_process_proc1
IS
p_cust_account_rec hz_cust_account_v2pub.cust_account_rec_type;
p_person_rec hz_party_v2pub.person_rec_type;
p_customer_profile_rec hz_customer_profile_v2pub.customer_profile_rec_type;
p_organization_rec hz_party_v2pub.organization_rec_type;
p_location_rec hz_location_v2pub.location_rec_type;
p_cust_site_use_rec hz_cust_account_site_v2pub.cust_site_use_rec_type;
p_cust_acct_site_rec hz_cust_account_site_v2pub.cust_acct_site_rec_type;
p_party_site_rec hz_party_site_v2pub.party_site_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_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
x_party_site_id NUMBER;
x_party_site_number VARCHAR2 (2000);
x_location_id NUMBER;
x_site_use_id NUMBER;
x_return_status VARCHAR2 (2000);
x_cust_acct_site_id NUMBER;
x_success_rec NUMBER;
x_err_count NUMBER;
CURSOR cur_customer (x_status VARCHAR2)
IS
SELECT xcs.ROWID, xcs.*
FROM XXCUS.xx_tp_dmig_lead_profiles_stg1 xcs
WHERE err_status = x_status;
BEGIN
/* PRINT (' **********PROCESSING OF RECORDS********** ');
fnd_file.put_line(fnd_file.output,'SUMMARY OF PROCESSED RECORDS');
PRINT(RPAD(' Party ID',20,' ') || RPAD(' Cust Account Id',20,' ' )|| RPAD('Party Site Id',20,'
PRINT(RPAD('-',20,'-') || RPAD('-',20,'-' )|| RPAD('-',20,'-'));
PRINT( ' ');
*/
FOR rec_cust IN cur_customer ('V')
LOOP
error_message := NULL;
/* =============================================
*Purpose : This API take the customer info'n from the staging
table as input, and outputs the unique id's (party_id,cust_account_id,
account_number) and pushes the whole data into r12 hz tables.
* =============================================*/
p_cust_account_rec.account_name := rec_cust.original_name;
p_cust_account_rec.created_by_module := 'TCA_V2_API';
p_person_rec.person_first_name := rec_cust.first_name;
hz_cust_account_v2pub.create_cust_account ('T',
p_cust_account_rec,
p_person_rec,
p_customer_profile_rec,
'F' --'T'
,
x_cust_account_id,
x_account_number,
x_party_id,
x_party_number,
x_profile_id,
x_return_status,
x_msg_count,
x_msg_data);
IF x_return_status <> 'S'
THEN
error_message := x_msg_data;
END IF;
/* =============================================
*Purpose : This API take the Location info'n from the staging
table as input, and outputs the unique id's (location_id)
and pushes the whole data into r12 hz tables.
* =============================================*/
IF x_return_status = 'S'
THEN
-- Initialization of the default values to create the location
p_location_rec.country := rec_cust.country_code; ---'US'
p_location_rec.address1 := rec_cust.address1;
p_location_rec.city := rec_cust.city;
p_location_rec.county := rec_cust.country_code;
p_location_rec.postal_code := rec_cust.postal_code;
----TO_NUMBER (rec_cust.postal_code);
p_location_rec.state := rec_cust.state;
p_location_rec.created_by_module := 'TCA_V2_API';
hz_location_v2pub.create_location ('T',
p_location_rec,
x_location_id,
x_return_status,
x_msg_count,
x_msg_data);
IF x_return_status <> 'S'
THEN
error_message := x_msg_data;
END IF;
END IF;
/* =============================================
*Purpose : This API take the Party Site info'n from the staging
table as input, and outputs the party_site_id, party_site_number
and pushes the whole data into r12 hz tables.
* =============================================*/
--
IF x_return_status = 'S'
THEN
p_party_site_rec.party_id := x_party_id; --rec_cust.party_id;
p_party_site_rec.location_id := x_location_id;
--rec_cust.location_id;
p_party_site_rec.identifying_address_flag := 'Y';
p_party_site_rec.created_by_module := 'TCA_V2_API';
hz_party_site_v2pub.create_party_site ('T',
p_party_site_rec,
x_party_site_id,
x_party_site_number,
x_return_status,
x_msg_count,
x_msg_data);
COMMIT;
IF x_return_status <> 'S'
THEN
error_message := x_msg_data;
END IF;
END IF;
/* =============================================
*Purpose : This API take the Customer Account Site info'n
from the staging table as input, and outputs the cust_acct_site_id
and pushes the whole data into r12 hz tables.
* =============================================*/
IF x_return_status = 'S'
THEN
/* PRINT(RPAD(x_party_id,20, ' ')||RPAD(x_cust_account_id,20, ' ') || RPAD(x_party_site
*/
p_cust_acct_site_rec.cust_account_id := x_cust_account_id;
p_cust_acct_site_rec.party_site_id := x_party_site_id;
p_cust_acct_site_rec.LANGUAGE := rec_cust.LANGUAGE_code;
p_cust_acct_site_rec.created_by_module := 'TCA_V2_API';
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
);
IF x_return_status <> 'S'
THEN
error_message := x_msg_data;
END IF;
END IF;
/* =============================================
*Purpose : This API take the Customer Site use info'n
from the staging table as input, and outputs the site_use_id
and pushes the whole data into r12 hz tables.
* =============================================*/
IF x_return_status = 'S'
THEN
p_cust_site_use_rec.cust_acct_site_id := x_cust_acct_site_id;
p_cust_site_use_rec.site_use_code := rec_cust.site_use_code;
--rec_cust.SITE_USE_code;
p_cust_site_use_rec.created_by_module := 'TCA_V2_API';
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
);
IF x_return_status <> 'S'
THEN
error_message := x_msg_data;
END IF;
END IF;
/* =============================================
Purpose: Running Customer profile
* =============================================*/
/* IF x_return_status = 'S'
THEN
p_customer_profile_rec.cust_account_id := x_cust_account_id;
p_customer_profile_rec.statement_cycle_id := rec_cust.statment_cycle_id;
p_customer_profile_rec.created_by_module := 'TCA_V2_API';
hz_customer_profile_v2pub.create_customer_profile (
p_customer_profile_rec ,
p_create_profile_amt => FND_API.G_TRUE,
x_cust_account_profile_id => l_cust_account_profile_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
IF x_return_status <> 'S'
THEN
error_message := x_msg_data;
END IF;
END IF;
*/
/* =============================================
*Purpose : Updates the data into Staging table with
respective id's whcih are returned by the API's. Also
updates the ERR_STATUS and ERR_MSG respectively
* =============================================*/
UPDATE XXCUS.xx_tp_dmig_lead_profiles_stg1 xcst
SET err_status = x_return_status,
err_msg = error_message,
party_id = x_party_id,
location_id = x_location_id,
cust_account_id = x_cust_account_id,
party_site_id = x_party_site_id,
cust_acct_site_id = x_cust_acct_site_id,
site_use_id = x_site_use_id,
request_id = APPS.FND_GLOBAL.CONC_REQUEST_ID,
created_by = APPS.FND_GLOBAL.USER_ID,
last_updated_by = APPS.FND_GLOBAL.USER_ID
WHERE ROWID = rec_cust.ROWID;
COMMIT;
END LOOP;
-- ERROR REPORT
PRINT (' ');
PRINT (RPAD ('*', 60, '*'));
PRINT (
RPAD (' ', 25, ' ') || 'SUMMARY OF RECORDS ' || RPAD (' ', 25, ' ')
);
PRINT (RPAD ('*', 60, '*'));
PRINT (' ');
SELECT COUNT (ORIGINAL_name)
INTO x_err_count
FROM XXCUS.xx_tp_dmig_lead_profiles_stg1
WHERE err_status = 'E';
PRINT('TOTAL NUMBER OF RECORDS ERROERED OUT WHILE PROCESSING :'
|| X_ERR_COUNT);
SELECT COUNT (1)
INTO x_err_count
FROM XXCUS.xx_tp_dmig_lead_profiles_stg1
WHERE err_status = 'S';
PRINT('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES :'
|| X_ERR_COUNT);
PRINT (' ');
PRINT (RPAD ('*', 60, '*'));
PRINT (RPAD (' ', 28, ' ') || 'ERROR DATA' || RPAD (' ', 28, ' '));
PRINT (RPAD ('*', 60, '*'));
PRINT( RPAD (' PARTY NAME', 30, ' ')
|| RPAD (' ', 8, ' ')
|| RPAD ('ERROR MESSAGE', 100, ' '));
PRINT (
RPAD ('-', 30, '-') || RPAD (' ', 8, ' ') || RPAD ('-', 100, '-')
);
FOR rec_cust IN cur_customer ('E')
LOOP
PRINT(RPAD (SUBSTR (rec_cust.ORIGINAL_name, 1, 40), 40, ' ')
|| RPAD (rec_cust.err_msg, 100, ' '));
END LOOP;
PRINT (' ');
PRINT (RPAD ('*', 60, '*'));
PRINT( RPAD (' ', 28, ' ')
|| 'PROCESSED RECORD DETAILS'
|| RPAD (' ', 28, ' '));
PRINT (RPAD ('*', 60, '*'));
PRINT (' ');
PRINT( RPAD (' PARTY NAME', 40, ' ')
|| RPAD ('ACCOUNT NAME', 50, ' ')
|| RPAD ('PARTY SITE ID', 20, ' '));
PRINT (
RPAD ('-', 40, '-') || RPAD ('-', 50, '-') || RPAD ('-', 20, '-')
);
PRINT (' ');
FOR rec_cust IN cur_customer ('S')
LOOP
PRINT( RPAD (rec_cust.ORIGINAL_name, 40, ' ')
|| RPAD (SUBSTR (rec_cust.FIRST_name, 1, 50), 50, ' ')
|| RPAD (rec_cust.party_site_id, 20, ' '));
END LOOP;
PRINT (' ');
PRINT (RPAD ('-', 60, '-'));
PRINT (RPAD (' ', 28, ' ') || 'END REPORT' || RPAD (' ', 28, ' '));
PRINT (RPAD ('-', 60, '-'));
COMMIT;
/* Report to display the Record Status*/
--BEGIN
/* PRINT (' ');
PRINT (' ');
PRINT ('SUMMARY OF RECORDS ');
PRINT ('******************* ');
*/
/* PRINT (' ');
/* PRINT ('COUNT STATUS ');
PRINT ('----- ------- ');
FOR rec_sum IN cur_sum1
LOOP
if(rec_sum.err_status='S')
then
PRINT ('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES :'||X_ERR_COUNT);
else
PRINT ('TOTAL NUMBER OF RECORDS errored out :'||X_ERR_COUNT);
end if;
PRINT ( RPAD (TO_CHAR (rec_sum.num)
, 12
, ' '
)
|| RPAD (rec_sum.err_status
, 12
, ' '
));
-- || rec_sum.err_msg);*/
BEGIN
NULL;
/*select count(*) into x_err_count from xxcsc_ar_cust_conv_stg where err_status='E';
PRINT ('TOTAL NUMBER OF RECORDS ERROERED OUT WHILE PROCESSING :'||X_ERR_COUNT);
select count(*) into x_err_count from xxcsc_ar_cust_conv_stg where err_status='S';
PRINT ('TOTAL NUMBER OF RECORDS INSERTED INTO HZ BASE TABLES :'||X_ERR_COUNT);
*/
EXCEPTION
WHEN NO_DATA_FOUND
THEN
PRINT('NO DATA FOUND WHILE PRINTING THE COUNT AND STATUS OF RECORDS');
WHEN OTHERS
THEN
PRINT ('ERROR WHILE PRINTING THE COUNT AND STATUS OF RECORDS');
END;
-- END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
'Error while processing records due to :' || SQLERRM
);
END xx_tp_ar_cust_process_proc1;
END XX_TP_DMIG_LEAD_PROFILES_PKG1;
No comments:
Post a Comment