Oracle Apps: XXST_INT_UTILITY

CREATE OR REPLACE PACKAGE XXST_INT_UTILITY_PKG

/*PROCEDURE MAINTAIN_AUDIT_DATA_PRC(pn_batch_id IN NUMBER,

pn_conc_request_id IN NUMBER,

pc_entity IN VARCHAR2,

pc_table_name IN VARCHAR2,

pn_count_recd IN NUMBER,

pn_count_errd IN NUMBER,

pn_count_uploaded IN NUMBER);*/

PROCEDURE GENERATE_ERROR_LOG_PRC (pn_batch_id IN NUMBER,

pn_conc_request_id IN NUMBER,

pc_table_name IN VARCHAR2,

pc_column_name IN VARCHAR2,

pc_key_column IN VARCHAR2,

pc_key_column_id IN VARCHAR2 DEFAULT NULL,

pc_key_column_value IN VARCHAR2 DEFAULT NULL,

pc_error_text IN VARCHAR2,

pn_rec_number IN NUMBER);

PROCEDURE PRT_AUDIT_ERROR_LOG_DATA( pn_batch_no IN NUMBER

, pn_conc_request_no IN NUMBER);

PROCEDURE GET_NEW_CUST_SITE_DTLS

( p_old_org_id IN NUMBER

, p_new_org_id IN NUMBER

, p_orig_sys_ref IN VARCHAR2

, p_site_use_code IN VARCHAR2

, p_customer_id OUT NUMBER

, p_site_use_id OUT NUMBER

, p_address_id OUT NUMBER

);

PROCEDURE GET_NEW_CUST_SITE_DTLS

( p_old_org_id IN NUMBER

, p_new_org_id IN NUMBER

, p_orig_sys_ref IN VARCHAR2

, p_customer_id OUT NUMBER

, p_address_id OUT NUMBER

);

PROCEDURE GET_NEW_CUST_NUMBER

( p_old_New_cust_number IN OUT VARCHAR2 );

FUNCTION GET_DATALOAD_USER_ID RETURN NUMBER;

END XXST_INT_UTILITY_PKG;

CREATE OR REPLACE PACKAGE BODY XXST_INT_UTILITY_PKG

/********************************************************************************************************************************

*Name : Rajashekar

*Purpose : The Package is Used for Finding Mapping Values and Recording Error and Audit Information

*C and I Number : 1

*Arguments :

*Script Location :

* Arg1 - Describe arg1

* Arg2 - Describe arg2

*Notes : This Package Specification defines the Procedure Defined in the Package

* : Calls 1 Packaged Procedure TKM_CONV_UTILITY_PKG

* 1. Special usage notes

* 2. Special usage notes

*Change History

* Date Name Ver Description

*--------------------------------------------------------------------------------------------------------------------------------

* 27-Mar-2013 Rajashekar 1.0 Used for Finding Mapping Values and Recording Error and Audit Information

*********************************************************************************************************************************/

/*PROCEDURE maintain_audit_data_prc (

pn_batch_id IN NUMBER,

pn_conc_request_id IN NUMBER,

pc_entity IN VARCHAR2,

pc_table_name IN VARCHAR2,

pn_count_recd IN NUMBER,

pn_count_errd IN NUMBER,

pn_count_uploaded IN NUMBER

)

err_num NUMBER;

err_msg VARCHAR2 (255);

BEGIN

INSERT INTO XXFTAL_INT_AUDIT_DATA

(batch_id, conc_request_id, date_run, entity,

table_name, count_received, count_errored,

count_uploaded, last_update_date, last_updated_by,

creation_date, created_by, last_update_login

)

VALUES (pn_batch_id, pn_conc_request_id, SYSDATE, pc_entity,

pc_table_name, pn_count_recd, pn_count_errd,

pn_count_uploaded, SYSDATE, fnd_global.login_id,

SYSDATE, fnd_global.user_id, fnd_global.login_id

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

err_num := SQLCODE;

err_msg := SUBSTR (SQLERRM (err_num), 1, 100);

DBMS_OUTPUT.put_line ( 'Error Number: '

|| err_num

|| ' Error Msg: '

|| err_msg

);

END maintain_audit_data_prc;*/

-----------------------------

PROCEDURE generate_error_log_prc (

pn_batch_id IN NUMBER,

pn_conc_request_id IN NUMBER,

pc_table_name IN VARCHAR2,

pc_column_name IN VARCHAR2,

pc_key_column IN VARCHAR2,

pc_key_column_id IN VARCHAR2 DEFAULT NULL,

pc_key_column_value IN VARCHAR2 DEFAULT NULL,

pc_error_text IN VARCHAR2,

pn_rec_number IN NUMBER

)

err_num NUMBER;

err_msg VARCHAR2 (255);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO XXST_INT_ERROR_LOG

(batch_id, conc_request_id, date_run, table_name,

column_name, key_column, key_column_id,

key_column_value, ERROR_TEXT, rec_number,

last_update_date, last_updated_by, creation_date,

created_by, last_update_login

)

VALUES (pn_batch_id, pn_conc_request_id, SYSDATE, pc_table_name,

pc_column_name, pc_key_column, pc_key_column_id,

pc_key_column_value, pc_error_text, pn_rec_number,

SYSDATE, fnd_global.login_id, SYSDATE,

fnd_global.user_id, fnd_global.login_id

);

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

err_num := SQLCODE;

err_msg := SUBSTR (SQLERRM (err_num), 1, 100);

DBMS_OUTPUT.put_line ( 'Error Number: '

|| err_num

|| ' Error Msg: '

|| err_msg

);

ROLLBACK;

END generate_error_log_prc;

-----------------------------------

PROCEDURE prt_audit_error_log_data (

pn_batch_no IN NUMBER,

pn_conc_request_no IN NUMBER

)

/*CURSOR lcur_file_name

SELECT file_name

FROM xxtk_file_process

WHERE file_process_id = pn_batch_no;*/

cursor cur_max_lenth(pn_batch_id number,pn_conc_request_id number) is

select max(length(key_column))+3 key_column,max(length(key_column_id))+3 key_column_id

from XXST_INT_ERROR_LOG

where batch_id = pn_batch_id

AND conc_request_id = pn_conc_request_id;

ln_key_column_max number(12);

ln_key_column_id_max number(12);

lc_file_name VARCHAR2 (500);

-- Audit Data Variables

lc_error_message_server VARCHAR2 (1000);

BEGIN

IF pn_batch_no IS NOT NULL AND pn_conc_request_no IS NOT NULL

THEN

--OPEN lcur_file_name;

--FETCH lcur_file_name

--INTO lc_file_name;

--CLOSE lcur_file_name;

--SExxtk_file_process

-- Input Parameters Given

-- Fnd_File.put_line(Fnd_File.OUTPUT,'Input Parameters: ');

fnd_file.put_line (fnd_file.output,

RPAD ('Audit Summary Information:', 30, ' ')

);

fnd_file.put_line (fnd_file.output, '');

fnd_file.put_line (fnd_file.output,

RPAD ('File Name:', 30, ' ')

|| CHR (9)

|| lc_file_name

);

fnd_file.put_line (fnd_file.output,

RPAD ('Request Id:', 30, ' ')

|| CHR (9)

|| pn_conc_request_no

);

fnd_file.put_line (fnd_file.output,

RPAD ('Batch Number:', 30, ' ')

|| CHR (9)

|| pn_batch_no

);

fnd_file.put_line (fnd_file.output, '');

-- Start Processing the Audit Header Information Details

/* BEGIN

FOR cauditdatarec IN (SELECT ROWID, cad.*

FROM XXFTAL_INT_AUDIT_DATA cad

WHERE batch_id = pn_batch_no

AND conc_request_id =

pn_conc_request_no

ORDER BY table_name)

LOOP

-- Start Populating the Header Information For the Report

fnd_file.put_line (fnd_file.output,

RPAD ('Entity Name:', 30, ' ')

|| CHR (9)

|| cauditdatarec.entity

);

fnd_file.put_line (fnd_file.output,

RPAD ('Table Name:', 30, ' ')

|| CHR (9)

|| cauditdatarec.table_name

);

fnd_file.put_line (fnd_file.output,

RPAD ('Total Records Processed:',

30,

' '

)

|| CHR (9)

|| cauditdatarec.count_received

);

fnd_file.put_line (fnd_file.output,

RPAD ('Total Records Success:',

30,

' '

)

|| CHR (9)

|| cauditdatarec.count_uploaded

);

fnd_file.put_line (fnd_file.output,

RPAD ('Total Records Errored:',

30,

' '

)

|| CHR (9)

|| cauditdatarec.count_errored

);

fnd_file.put_line (fnd_file.output, '');

END LOOP; -- End of Loop 1

EXCEPTION

WHEN OTHERS

THEN

lc_error_message_server :=

lc_error_message_server || ' ' || SQLERRM;

fnd_file.put_line

(fnd_file.LOG,

'ERROR IN LOOP FOR AUDIT DATA TABLE: '

|| CHR (9)

|| lc_error_message_server

);

END;*/

fnd_file.put_line

(fnd_file.output,

'---------------------------------------------------------------------------------------------------------------------------------------------------------------------'

);

-- Start Processing the Error Log Details

open cur_max_lenth(pn_batch_no,pn_conc_request_no);

fetch cur_max_lenth

into ln_key_column_max,ln_key_column_id_max;

close cur_max_lenth;

if nvl(ln_key_column_max,0) = 0 then

ln_key_column_max := 30;

end if;

if nvl(ln_key_column_id_max,0) = 0 then

ln_key_column_id_max := 30;

end if;

fnd_file.put_line (fnd_file.output,

'|'

|| RPAD ('Key Columns:', ln_key_column_max, ' ')

|| '|'

|| RPAD ('Key Column Values:', ln_key_column_id_max, ' ')

|| '|'

|| 'Error Text:|'

);

BEGIN

FOR centityerrorlogrec IN (SELECT ROWID, cel.*

FROM XXST_INT_ERROR_LOG cel

WHERE batch_id = pn_batch_no

AND conc_request_id =

pn_conc_request_no

ORDER BY table_name)

LOOP

-- Star Populating the Detail Error Log Information for all the Records

--Fnd_File.put_line(Fnd_File.OUTPUT,'|'||RPAD(CEntityErrorLogRec.REC_NUMBER,15,' ')||'|'||RPAD(CEntityErrorLogRec.TABLE_NAME,40,' ')||'|'||CEntityErrorLogRec.ERROR_TEXT||'|'); -- CMB SAJI 07022009

fnd_file.put_line

(fnd_file.output,

'|'

|| RPAD (centityerrorlogrec.key_column,

ln_key_column_max,

' '

)

|| '|'

|| RPAD (centityerrorlogrec.key_column_id,

ln_key_column_id_max,

' '

)

|| '|'

|| centityerrorlogrec.ERROR_TEXT

|| '|'

); -- ADDB SAJI 07022009

END LOOP; -- End of Loop 2

EXCEPTION

WHEN OTHERS

THEN

lc_error_message_server :=

lc_error_message_server || ' ' || SQLERRM;

fnd_file.put_line

(fnd_file.LOG,

'ERROR IN LOOP FOR ERROR LOG TABLE: '

|| CHR (9)

|| lc_error_message_server

);

END;

ELSE

fnd_file.put_line

(fnd_file.LOG,

'Value Missing for Batch_No and Conc_Request_No '

);

END IF;

EXCEPTION

WHEN OTHERS

THEN

lc_error_message_server := lc_error_message_server || ' ' || SQLERRM;

fnd_file.put_line (fnd_file.LOG,

'ERROR IN CURSOR SELECTS -> CHECK '

|| CHR (9)

|| lc_error_message_server

);

END prt_audit_error_log_data;

-----------------------------------

PROCEDURE get_new_cust_site_dtls

/***********************************************************************

Name : GET_NEW_CUST_SITE_DTLS

Purpose : Get Customer Bill To Sites details

C and I Number : < >

Arguments : NA

p_old_org_id IN NUMBER -- Old Operating Unit Id

p_new_org_id IN NUMBER -- New Operating Unit Id

p_site_use_code IN VARCHAR2 -- site use code

p_orig_sys_ref IN VARCHAR2 -- ORIG_SYSTEM_REFERENCE

p_customer_id OUT NUMBER -- CUST_ACCOUNT_ID(HZ_CUST_ACCOUNTS)

p_site_use_id OUT NUMBER -- SITE_USE_ID (HZ_CUST_SITE_USES_ALL)

p_address_id OUT NUMBER -- CUST_ACCT_SITE_ID (HZ_CUST_SITE_USES_ALL)

Script Location : This file is stored in $XXTKCOINV_TOP/sql

Notes : Script to create Common utility package

------------------------------------------------------------------------

Change History

Date Name Ver Description

--------------------------------------------------------------------------

**************************************************************************/

(

p_old_org_id IN NUMBER,

p_new_org_id IN NUMBER,

p_orig_sys_ref IN VARCHAR2,

p_site_use_code IN VARCHAR2,

p_customer_id OUT NUMBER,

p_site_use_id OUT NUMBER,

p_address_id OUT NUMBER

)

CURSOR c_locations

SELECT location_id

FROM hz_locations hl

WHERE INSTR (hl.attribute1,

'<' || p_orig_sys_ref || ':' || p_old_org_id || '>'

) > 0;

BEGIN

FOR r_locations IN c_locations

LOOP

BEGIN

SELECT hcas.cust_account_id, hcas.cust_acct_site_id,

hcsu.site_use_id

INTO p_customer_id, p_address_id,

p_site_use_id

FROM hz_party_sites hps,

hz_cust_acct_sites_all hcas,

hz_cust_site_uses_all hcsu

WHERE hps.location_id = r_locations.location_id

AND hcas.party_site_id = hps.party_site_id

AND hcas.org_id = p_new_org_id

AND hcsu.org_id = hcas.org_id

AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id

AND hcsu.site_use_code = p_site_use_code;

EXIT;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL;

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END get_new_cust_site_dtls;

---------------------------------------------------

PROCEDURE get_new_cust_site_dtls

/***********************************************************************

Name : GET_NEW_CUST_SITE_DTLS

Purpose : Get Customer Bill To Sites details

C and I Number : < >

Arguments : NA

p_old_org_id IN NUMBER -- Old Operating Unit Id

p_new_org_id IN NUMBER -- New Operating Unit Id

p_orig_sys_ref IN VARCHAR2 -- ORIG_SYSTEM_REFERENCE

p_customer_id OUT NUMBER -- CUST_ACCOUNT_ID(HZ_CUST_ACCOUNTS)

p_address_id OUT NUMBER -- CUST_ACCT_SITE_ID (HZ_CUST_SITE_USES_ALL)

Script Location : This file is stored in $XXTKINV_TOP/sql

Notes : Script to create Common utility package

------------------------------------------------------------------------

Change History

Date Name Ver Description

--------------------------------------------------------------------------

**************************************************************************/

(

p_old_org_id IN NUMBER,

p_new_org_id IN NUMBER,

p_orig_sys_ref IN VARCHAR2,

p_customer_id OUT NUMBER,

p_address_id OUT NUMBER

)

CURSOR c_locations

SELECT location_id

FROM hz_locations hl

WHERE INSTR (hl.attribute1,

'<' || p_orig_sys_ref || ':' || p_old_org_id || '>'

) > 0;

BEGIN

FOR r_locations IN c_locations

LOOP

BEGIN

SELECT hcas.cust_account_id, hcas.cust_acct_site_id

INTO p_customer_id, p_address_id

FROM hz_party_sites hps, hz_cust_acct_sites_all hcas

WHERE hps.location_id = r_locations.location_id

AND hcas.party_site_id = hps.party_site_id

AND hcas.org_id = p_new_org_id;

EXIT;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

NULL;

END;

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

NULL;

END get_new_cust_site_dtls;

PROCEDURE get_new_cust_number (p_old_new_cust_number IN OUT VARCHAR2)

BEGIN

SELECT account_number

INTO p_old_new_cust_number

FROM hz_cust_accounts

WHERE attribute2 = p_old_new_cust_number;

EXCEPTION

WHEN OTHERS

THEN

p_old_new_cust_number := NULL;

END get_new_cust_number;

---------------------------

FUNCTION get_dataload_user_id

RETURN NUMBER

ln_user_id NUMBER;

BEGIN

SELECT user_id

INTO ln_user_id

FROM fnd_user

WHERE user_name = 'DATALOAD';

RETURN ln_user_id;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

ln_user_id := 0;

RETURN ln_user_id;

WHEN OTHERS

THEN

ln_user_id := 0;

RETURN ln_user_id;

END get_dataload_user_id;

---------------------------

END XXST_INT_UTILITY_PKG;

select * from all_errors where name ='XXST_INT_UTILITY_PKG';

Oracle Apps

Pages

Search This Blog

Thursday, June 20, 2013

XXST_INT_UTILITY_PKG sample script

No comments:

Post a Comment