Oracle Apps: Supplier Interface using UTL file

Error or success records Output Package
-------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.XXATFL_INT_UTILITY_PKG
IS
/********************************************************************************************************************************
*Name : Srini
*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
*--------------------------------------------------------------------------------------------------------------------------------
* 05-Jan-2011 Srini 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
)
IS
err_num NUMBER;
err_msg VARCHAR2 (255);
BEGIN
INSERT INTO XXATFL_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, apps.fnd_global.login_id,
SYSDATE, apps.fnd_global.user_id, apps.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
)
IS
err_num NUMBER;
err_msg VARCHAR2 (255);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO XXATFL_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, apps.fnd_global.login_id, SYSDATE,
apps.fnd_global.user_id, apps.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
)
IS
/*CURSOR lcur_file_name
IS
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 XXATFL_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
-- apps.Fnd_File.put_line(apps.Fnd_File.OUTPUT,'Input Parameters: ');
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Audit Summary Information:', 30, ' ')
);
apps.fnd_file.put_line (apps.fnd_file.output, '');
apps.fnd_file.put_line (apps.fnd_file.output, '');
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('File Name:', 30, ' ')
|| CHR (9)
|| lc_file_name
);
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Request Id:', 30, ' ')
|| CHR (9)
|| pn_conc_request_no
);
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Batch Number:', 30, ' ')
|| CHR (9)
|| pn_batch_no
);
apps.fnd_file.put_line (apps.fnd_file.output, '');
apps.fnd_file.put_line (apps.fnd_file.output, '');
-- Start Processing the Audit Header Information Details
BEGIN
FOR cauditdatarec IN (SELECT ROWID, cad.*
FROM XXATFL_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
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Entity Name:', 30, ' ')
|| CHR (9)
|| cauditdatarec.entity
);
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Table Name:', 30, ' ')
|| CHR (9)
|| cauditdatarec.table_name
);
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Total Records Processed:',
30,
' '
)
|| CHR (9)
|| cauditdatarec.count_received
);
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Total Records Success:',
30,
' '
)
|| CHR (9)
|| cauditdatarec.count_uploaded
);
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD ('Total Records Errored:',
30,
' '
)
|| CHR (9)
|| cauditdatarec.count_errored
);
apps.fnd_file.put_line (apps.fnd_file.output, '');
END LOOP; -- End of Loop 1
EXCEPTION
WHEN OTHERS
THEN
lc_error_message_server :=
lc_error_message_server || ' ' || SQLERRM;
apps.fnd_file.put_line
(apps.fnd_file.LOG,
'ERROR IN LOOP FOR AUDIT DATA TABLE: '
|| CHR (9)
|| lc_error_message_server
);
END;
apps.fnd_file.put_line
(apps.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;
apps.fnd_file.put_line (apps.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 XXATFL_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
--apps.Fnd_File.put_line(apps.Fnd_File.OUTPUT,'|'||RPAD(CEntityErrorLogRec.REC_NUMBER,15,' ')||'|'||RPAD(CEntityErrorLogRec.TABLE_NAME,40,' ')||'|'||CEntityErrorLogRec.ERROR_TEXT||'|'); -- CMB SAJI 07022009
apps.fnd_file.put_line
(apps.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;
apps.fnd_file.put_line
(apps.fnd_file.LOG,
'ERROR IN LOOP FOR ERROR LOG TABLE: '
|| CHR (9)
|| lc_error_message_server
);
END;
ELSE
apps.fnd_file.put_line
(apps.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;
apps.fnd_file.put_line (apps.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
)
IS
--
CURSOR c_locations
IS
SELECT location_id
FROM apps.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 apps.hz_party_sites hps,
apps.hz_cust_acct_sites_all hcas,
apps.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
)
IS
--
CURSOR c_locations
IS
SELECT location_id
FROM apps.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 apps.hz_party_sites hps, apps.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)
IS
BEGIN
--
SELECT account_number
INTO p_old_new_cust_number
FROM apps.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
IS
ln_user_id NUMBER;
BEGIN
--
SELECT user_id
INTO ln_user_id
FROM apps.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 XXATFL_INT_UTILITY_PKG;
/

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

Supplier Interface Specification

CREATE OR REPLACE PACKAGE APPS.XXATFL_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 XXATFL_INT_UTILITY_PKG;

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

Package Body

CREATE OR REPLACE PACKAGE BODY APPS.XXATFL_INT_UTILITY_PKG

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

*Name : Srini

*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

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

* 05-Jan-2011 Srini 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 XXATFL_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, apps.fnd_global.login_id,

SYSDATE, apps.fnd_global.user_id, apps.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 XXATFL_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, apps.fnd_global.login_id, SYSDATE,

apps.fnd_global.user_id, apps.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 XXATFL_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

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

apps.fnd_file.put_line (apps.fnd_file.output,

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

);

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

apps.fnd_file.put_line (apps.fnd_file.output,

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

|| CHR (9)

|| lc_file_name

);

apps.fnd_file.put_line (apps.fnd_file.output,

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

|| CHR (9)

|| pn_conc_request_no

);

apps.fnd_file.put_line (apps.fnd_file.output,

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

|| CHR (9)

|| pn_batch_no

);

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

-- Start Processing the Audit Header Information Details

BEGIN

FOR cauditdatarec IN (SELECT ROWID, cad.*

FROM XXATFL_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

apps.fnd_file.put_line (apps.fnd_file.output,

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

|| CHR (9)

|| cauditdatarec.entity

);

apps.fnd_file.put_line (apps.fnd_file.output,

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

|| CHR (9)

|| cauditdatarec.table_name

);

apps.fnd_file.put_line (apps.fnd_file.output,

RPAD ('Total Records Processed:',

30,

' '

)

|| CHR (9)

|| cauditdatarec.count_received

);

apps.fnd_file.put_line (apps.fnd_file.output,

RPAD ('Total Records Success:',

30,

' '

)

|| CHR (9)

|| cauditdatarec.count_uploaded

);

apps.fnd_file.put_line (apps.fnd_file.output,

RPAD ('Total Records Errored:',

30,

' '

)

|| CHR (9)

|| cauditdatarec.count_errored

);

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

END LOOP; -- End of Loop 1

EXCEPTION

WHEN OTHERS

THEN

lc_error_message_server :=

lc_error_message_server || ' ' || SQLERRM;

apps.fnd_file.put_line

(apps.fnd_file.LOG,

'ERROR IN LOOP FOR AUDIT DATA TABLE: '

|| CHR (9)

|| lc_error_message_server

);

END;

apps.fnd_file.put_line

(apps.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;

apps.fnd_file.put_line (apps.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 XXATFL_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

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

apps.fnd_file.put_line

(apps.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;

apps.fnd_file.put_line

(apps.fnd_file.LOG,

'ERROR IN LOOP FOR ERROR LOG TABLE: '

|| CHR (9)

|| lc_error_message_server

);

END;

ELSE

apps.fnd_file.put_line

(apps.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;

apps.fnd_file.put_line (apps.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 apps.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 apps.hz_party_sites hps,

apps.hz_cust_acct_sites_all hcas,

apps.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 apps.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 apps.hz_party_sites hps, apps.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 apps.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 apps.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 XXATFL_INT_UTILITY_PKG;

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

Oracle Apps

Friday, 27 January 2012

Supplier Interface using UTL file

No comments:

Post a Comment