AR Invoice Interface Package
CREATE OR REPLACE PACKAGE xx_ar_inv_pkg
IS
PROCEDURE
xx_ar_inv_main (
v_error_buf OUT
VARCHAR2,
v_ret_code OUT
NUMBER,
p_load_option IN
VARCHAR2
);
PROCEDURE
xx_ar_inv_val_prc;
PROCEDURE xx_ar_inv_std_pgm_prc;
PROCEDURE
xx_ar_inv_load_pgm_prc;
FUNCTION xx_org_id_fun
(p_operating_unit VARCHAR2)
RETURN NUMBER;
FUNCTION
xx_batch_src_fun (p_batch_source_name VARCHAR2, p_org_id NUMBER)
RETURN VARCHAR2;
FUNCTION xx_line_type_fun
(p_line_type VARCHAR2)
RETURN VARCHAR2;
FUNCTION
xx_cust_trx_type_fun (p_cust_trx_type VARCHAR2, p_org_id NUMBER)
RETURN NUMBER;
FUNCTION
xx_currency_fun (p_currency_code VARCHAR2)
RETURN VARCHAR2;
FUNCTION xx_term_fun
(p_term_name VARCHAR2)
RETURN NUMBER;
FUNCTION
xx_bill_cust_fun (p_bill_cust VARCHAR2)
RETURN NUMBER;
FUNCTION
xx_bill_addr_fun (p_bill_addr VARCHAR2)
RETURN NUMBER;
FUNCTION
xx_ship_cust_fun (p_ship_cust VARCHAR2)
RETURN NUMBER;
FUNCTION
xx_ship_addr_fun (
p_ship_addr VARCHAR2,
p_org_id NUMBER,
p_ship_cust_id NUMBER
)
RETURN NUMBER;
FUNCTION
xx_sold_cust_fun (p_sold_cust VARCHAR2)
RETURN NUMBER;
FUNCTION xx_uom_fun
(p_unit_of_measure VARCHAR2)
RETURN VARCHAR2;
FUNCTION xx_item_fun
(p_item VARCHAR2, p_org_id NUMBER)
RETURN NUMBER;
FUNCTION
xx_conv_type_fun (p_conversion_type VARCHAR2)
RETURN VARCHAR2;
FUNCTION xx_fob_fun (p_fob
VARCHAR2)
RETURN VARCHAR2;
FUNCTION
xx_code_combination_fun (p_code_combination VARCHAR2)
RETURN NUMBER;
v_process_flag VARCHAR2 (1) := 'N';
v_error_msg VARCHAR2 (2000);
v_org_id
hr_operating_units.organization_id%TYPE;
v_batch_source_name
ra_batch_sources_all.NAME%TYPE;
v_line_type ar_lookups.lookup_code%TYPE;
v_cust_trx_type_id
ra_cust_trx_types_all.cust_trx_type_id%TYPE;
v_currency_code fnd_currencies.currency_code%TYPE;
v_term_id ra_terms_tl.term_id%TYPE;
v_bill_cust_id hz_parties.party_id%TYPE;
v_bill_addr_id
hz_cust_site_uses_all.cust_acct_site_id%TYPE;
v_ship_cust_id hz_parties.party_id%TYPE;
v_ship_addr_id
hz_cust_site_uses_all.cust_acct_site_id%TYPE;
v_sold_cust_id hz_parties.party_id%TYPE;
v_uom_code
mtl_units_of_measure_tl.uom_code%TYPE;
v_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
v_conversion_type
gl_daily_conversion_types.conversion_type%TYPE;
v_fob
fnd_lookup_values.lookup_code%TYPE;
v_code_combination_id
gl_code_combinations_kfv.code_combination_id%TYPE;
v_request_id NUMBER;
v_phase VARCHAR2 (50);
v_status VARCHAR2 (50);
v_dev_phase VARCHAR2 (50);
v_dev_status VARCHAR2 (50);
v_message VARCHAR2 (50);
v_conc_prgm_id NUMBER;
v_conc_request_id NUMBER;
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_boolean BOOLEAN;
END xx_ar_inv_pkg;
/
/*************************************************************
----------------------Package
Body--------------------------
*************************************************************/
CREATE OR REPLACE PACKAGE BODY xx_ar_inv_pkg
IS
PROCEDURE
xx_ar_inv_main (
v_error_buf OUT
VARCHAR2,
v_ret_code OUT
NUMBER,
p_load_option IN
VARCHAR2
)
IS
BEGIN
IF p_load_option =
'LOAD RECORDS'
THEN
/**** Procedure Call to
Load Data in Staging Tables ***/
xx_ar_inv_load_pgm_prc;
ELSIF p_load_option
= 'VALIDATE RECORDS'
THEN
/**** Procedure Call to
Validate Data ***/
xx_ar_inv_val_prc;
ELSIF p_load_option
= 'STANDARD PROGRAM'
THEN
/**** Procedure Call to Submit Standard Program ***/
xx_ar_inv_std_pgm_prc;
ELSIF p_load_option
= 'ALL'
THEN
/**** Procedure Calls to Load Data, Validate Data and Submit
Standar Program ***/
xx_ar_inv_load_pgm_prc;
xx_ar_inv_val_prc;
xx_ar_inv_std_pgm_prc;
END IF;
END;
/*************************************************************
--------------Data
Validation Procedure --------------
*************************************************************/
PROCEDURE
xx_ar_inv_val_prc
IS
CURSOR
xx_ra_inv_line_cur
IS
SELECT
xril.ROWID, xril.*
FROM
xx_ra_inv_lines_stg xril
WHERE
xril.process_flag = 'N';
CURSOR
xx_ra_inv_dist_cur (p_inv_num NUMBER)
IS
SELECT
xrid.ROWID, xrid.*
FROM
xx_ra_inv_dist_stg xrid
WHERE
xrid.process_flag = 'N' AND xrid.invoice_num = p_inv_num;
BEGIN
v_conc_prgm_id :=
fnd_global.conc_program_id;
v_conc_request_id :=
fnd_global.conc_request_id;
v_user_id := fnd_global.user_id;
v_resp_id :=
fnd_global.resp_id;
v_resp_appl_id :=
fnd_global.resp_appl_id;
fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
FOR xx_ra_inv_line
IN xx_ra_inv_line_cur
LOOP
fnd_file.put_line
(fnd_file.LOG, 'Enter Into
xx_ra_inv_line Loop');
--==========================================================--
--
Validation Starts
============================================================--
--------------- Operating Unit
Validation Starts -----------------------------
v_org_id :=
xx_org_id_fun (xx_ra_inv_line.operating_unit);
--------------- Operating Unit
Validation Ends -----------------------------
--------------- Batch Source Name Validation Starts
-----------------------------
v_batch_source_name :=
xx_batch_src_fun (xx_ra_inv_line.batch_source_name, v_org_id);
--------------- Batch Source Name
Validation Ends -----------------------------
--------------- Line Type
Validation Starts -----------------------------
v_line_type :=
xx_line_type_fun (xx_ra_inv_line.line_type);
--------------- Line Type Validation
Ends -----------------------------
--------------- Cust Trx Type
Validation Starts -----------------------------
v_cust_trx_type_id :=
xx_cust_trx_type_fun (xx_ra_inv_line.cust_trx_type_name,
v_org_id);
--------------- Cust Trx Type
Validation Ends -----------------------------
--------------- Currency
Validation Starts -----------------------------
v_currency_code
:= xx_currency_fun (xx_ra_inv_line.currency_code);
--------------- Currency
Validation Ends -----------------------------
--------------- Term Validation
Starts -----------------------------
v_term_id :=
xx_term_fun (xx_ra_inv_line.term_name);
--------------- Term Validation
Ends -----------------------------
--------------- Bill Customer
Validation Starts -----------------------------
v_bill_cust_id :=
xx_bill_cust_fun (xx_ra_inv_line.orig_system_bill_customer_name);
--------------- Bill Customer
Validation Ends -----------------------------
--------------- Bill Address
Validation Starts -----------------------------
v_bill_addr_id :=
xx_bill_addr_fun (xx_ra_inv_line.orig_system_bill_address);
--------------- Bill Address
Validation Ends -----------------------------
--------------- Ship Customer
Validation Starts -----------------------------
v_ship_cust_id :=
xx_ship_cust_fun
(xx_ra_inv_line.orig_system_ship_customer_name);
--------------- Ship Customer
Validation Ends -----------------------------
--------------- Ship Address
Validation Starts -----------------------------
v_ship_addr_id :=
xx_ship_addr_fun (xx_ra_inv_line.orig_system_ship_address,
v_org_id,
v_ship_cust_id
);
--------------- Ship Address
Validation Ends -----------------------------
--------------- Unit of Measure
Validation Starts -----------------------------
v_uom_code :=
xx_uom_fun (xx_ra_inv_line.unit_of_measure);
--------------- Unit of Measure
Validation Ends -----------------------------
--------------- Ship Customer
Validation Starts -----------------------------
v_sold_cust_id :=
xx_sold_cust_fun (xx_ra_inv_line.orig_system_sold_customer);
--------------- Ship Customer Validation
Ends -----------------------------
--------------- Item Validation
Starts -----------------------------
v_inventory_item_id :=
xx_item_fun (xx_ra_inv_line.inventory_item, v_org_id);
--------------- Item Validation Ends
-----------------------------
--------------- Conversion Type
Validation Starts -----------------------------
v_conversion_type
:=
xx_conv_type_fun (xx_ra_inv_line.conversion_type);
--------------- Conversion Type
Validation Ends -----------------------------
--------------- FOB Validation
Starts -----------------------------
v_fob :=
xx_fob_fun (xx_ra_inv_line.fob_point);
--------------- FOB Validation Ends
-----------------------------
INSERT INTO
ra_interface_lines_all
(interface_line_id, org_id,
batch_source_name, line_number,
line_type, cust_trx_type_id,
trx_date, gl_date,
currency_code, term_id, orig_system_bill_customer_id,
orig_system_bill_address_id,
orig_system_ship_customer_id,
orig_system_ship_address_id,
orig_system_sold_customer_id, sales_order,
inventory_item_id, uom_code,
quantity,
unit_selling_price,
amount, description,
conversion_type, conversion_rate,
interface_line_context,
interface_line_attribute1,
interface_line_attribute2, fob_point,
last_update_date, last_updated_by, creation_date,
created_by
)
VALUES
(ra_customer_trx_lines_s.NEXTVAL, v_org_id,
v_batch_source_name, xx_ra_inv_line.line_number,
v_line_type, v_cust_trx_type_id,
xx_ra_inv_line.trx_date, xx_ra_inv_line.gl_date,
v_currency_code, v_term_id, v_bill_cust_id,
v_bill_addr_id,
v_ship_cust_id,
v_ship_addr_id,
v_sold_cust_id, xx_ra_inv_line.sales_order,
v_inventory_item_id, v_uom_code,
xx_ra_inv_line.quantity,
xx_ra_inv_line.unit_selling_price,
xx_ra_inv_line.amount,
xx_ra_inv_line.description,
v_conversion_type, xx_ra_inv_line.conversion_rate,
v_batch_source_name,
xx_ra_inv_line.interface_line_attribute1,
xx_ra_inv_line.interface_line_attribute2,
v_fob,
SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.user_id
);
FOR
xx_ra_inv_dist IN xx_ra_inv_dist_cur (xx_ra_inv_line.invoice_num)
LOOP
fnd_file.put_line (fnd_file.LOG,
'Enter Into xx_ra_inv_dist Loop'
);
--==============VALIDATION STARTS
=========================================
--------------- FOB Validation
Starts -----------------------------
v_code_combination_id :=
xx_code_combination_fun (xx_ra_inv_dist.code_combination);
--------------- FOB Validation
Ends -----------------------------
INSERT INTO
ra_interface_distributions_all
(interface_line_id,
account_class, amount,
code_combination_id, PERCENT,
interface_line_context,
interface_line_attribute1,
interface_line_attribute2, org_id,
last_update_date, last_updated_by, creation_date,
created_by
)
VALUES
(ra_customer_trx_lines_s.CURRVAL,
xx_ra_inv_dist.account_class, xx_ra_inv_dist.amount,
v_code_combination_id, xx_ra_inv_dist.PERCENT,
v_batch_source_name,
xx_ra_inv_dist.interface_line_attribute1,
xx_ra_inv_dist.interface_line_attribute2, v_org_id,
SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.user_id
);
COMMIT;
END LOOP;
END LOOP;
END;
--=============================================================
-- AUTO INVOICE
MASTER PROGRAM--STANDARD PROGRAM
--============================================================
PROCEDURE
xx_ar_inv_std_pgm_prc
IS
BEGIN
--------------SUBMIT
STANDARD CONCURRENT Program--------------
fnd_file.put_line
(fnd_file.LOG,
'submit the standard cocurrent program'
);
v_request_id :=
fnd_request.submit_request
(application => 'AR',
program => 'RAXMTR',
description => 'Interface
your transactions to Oracle Receivables',
start_time => SYSDATE,
sub_request => FALSE,
argument1 => 1,
argument2 => 204,
argument3 => 1714,
argument4 => 'VISION
BUILD',
argument5 => SYSDATE,
argument6 => NULL,
argument7 => NULL,
argument8 => NULL,
argument9 => NULL,
argument10 => NULL,
argument11 => NULL,
argument12 => NULL,
argument13 => NULL,
argument14 => NULL,
argument15 => NULL,
argument16 => NULL,
argument17 => NULL,
argument18 => NULL,
argument19 => NULL,
argument20 => NULL,
argument21 => NULL,
argument22 => NULL,
argument23 => NULL,
argument24 => NULL,
argument25 => NULL,
argument26 => 'Y',
argument27 => NULL
);
COMMIT;
v_boolean :=
fnd_concurrent.wait_for_request (request_id => v_request_id,
INTERVAL => 60,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status =>
v_dev_status,
MESSAGE => v_message
);
fnd_file.put_line
(fnd_file.LOG, 'PHASE :' ||
v_phase);
fnd_file.put_line
(fnd_file.LOG, 'STATUS :' ||
v_status);
fnd_file.put_line
(fnd_file.LOG, 'DEV_PHASE :' ||
v_dev_phase);
fnd_file.put_line
(fnd_file.LOG, 'DEV STATUS :' ||
v_dev_status);
fnd_file.put_line
(fnd_file.LOG, 'MESSAGE :' ||
v_message);
fnd_file.put_line
(fnd_file.LOG,
'Request ID from Standard AR Auto Invoice
Master Program : '
||
v_request_id
);
IF v_request_id >
0
THEN
fnd_file.put_line
(fnd_file.LOG, v_request_id);
fnd_file.put_line
(fnd_file.LOG,
'In Submitting the
Standard Concurrent Program'
);
ELSE
fnd_file.put_line
(fnd_file.LOG,
'In
Submitting the Standard Concurrent Program not successfully'
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In
others of submitting the Standard Concurrent Program'
);
END;
/*************************************************************
---- Procedure for Data
Loader through Control file ----
*************************************************************/
PROCEDURE
xx_ar_inv_load_pgm_prc
IS
BEGIN
--------------SUBMIT
STANDARD CONCURRENT Program--------------
fnd_file.put_line
(fnd_file.LOG,
'submit the standard cocurrent program'
);
fnd_global.apps_initialize (v_user_id, v_resp_id, v_resp_appl_id);
BEGIN
v_request_id :=
fnd_request.submit_request
(application => 'AR',
program =>
'XXARINVLINE',
description => 'XX AR
Invoice Line Loader Program',
start_time => SYSDATE,
sub_request => FALSE
);
COMMIT;
v_boolean :=
fnd_concurrent.wait_for_request (request_id => v_request_id,
INTERVAL => 60,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status =>
v_dev_status,
MESSAGE => v_message
);
fnd_file.put_line
(fnd_file.LOG, 'PHASE :' ||
v_phase);
fnd_file.put_line
(fnd_file.LOG, 'STATUS :' ||
v_status);
fnd_file.put_line
(fnd_file.LOG, 'DEV_PHASE :' ||
v_dev_phase);
fnd_file.put_line
(fnd_file.LOG, 'DEV STATUS :' ||
v_dev_status);
fnd_file.put_line
(fnd_file.LOG, 'MESSAGE :' ||
v_message);
fnd_file.put_line
(fnd_file.LOG,
'Request
ID from Standard AR XX AR Invoice Line Loader Program : '
||
v_request_id
);
IF v_request_id
> 0
THEN
fnd_file.put_line (fnd_file.LOG, v_request_id);
fnd_file.put_line
(fnd_file.LOG,
'In Submitting the XX AR Invoice Line Loader Program'
);
ELSE
fnd_file.put_line
(fnd_file.LOG,
'In Submitting
the XX AR Invoice Line Loader Program not successfully'
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In
others of submitting the Standard Concurrent Program'
);
END;
BEGIN
v_request_id :=
fnd_request.submit_request
(application => 'AR',
program => 'XXARINVDIST',
description => 'XX AR
Invoice Dist Loader Program',
start_time => SYSDATE,
sub_request => FALSE
);
COMMIT;
v_boolean :=
fnd_concurrent.wait_for_request (request_id => v_request_id,
INTERVAL => 60,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
MESSAGE => v_message
);
fnd_file.put_line
(fnd_file.LOG, 'PHASE :' ||
v_phase);
fnd_file.put_line
(fnd_file.LOG, 'STATUS :' ||
v_status);
fnd_file.put_line
(fnd_file.LOG, 'DEV_PHASE :' ||
v_dev_phase);
fnd_file.put_line
(fnd_file.LOG, 'DEV STATUS :' ||
v_dev_status);
fnd_file.put_line
(fnd_file.LOG, 'MESSAGE :' ||
v_message);
fnd_file.put_line
(fnd_file.LOG,
'Request
ID from Standard AR XX AR Invoice Dist Loader Program : '
||
v_request_id
);
IF v_request_id
> 0
THEN
fnd_file.put_line
(fnd_file.LOG, v_request_id);
fnd_file.put_line
(fnd_file.LOG,
'In Submitting the XX AR Invoice Dist Loader Program'
);
ELSE
fnd_file.put_line
(fnd_file.LOG,
'In
Submitting the XX AR Invoice Dist Loader Program not successfully'
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In
others of submitting the Standard Concurrent Program'
);
END;
END;
--=============================================================================
--
LINES VALIDATION FUNCTIONS
--==============================================================================
--====================
Operating unit Function Starts===========================
FUNCTION xx_org_id_fun
(p_operating_unit VARCHAR2)
RETURN NUMBER
IS
v_organization_id NUMBER;
BEGIN
SELECT
organization_id
INTO
v_organization_id
FROM
hr_operating_units
WHERE NAME =
p_operating_unit;
RETURN
v_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for Operating Unit:' || p_operating_unit
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'NO DATA for Operating Unit :'
||
p_operating_unit;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for Operating Unit :'
|| p_operating_unit
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS found for Operating Unit :'
||
p_operating_unit;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for Operating Unit:'
|| p_operating_unit
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'In Others
for Operating Unit :'
||
p_operating_unit;
END xx_org_id_fun;
--==================== Operating unit Function
Ends===========================
--====================
Batch Source Name Function Starts========================
FUNCTION
xx_batch_src_fun (p_batch_source_name VARCHAR2, p_org_id NUMBER)
RETURN VARCHAR2
IS
BEGIN
SELECT NAME
INTO
v_batch_source_name
FROM
ra_batch_sources_all
WHERE NAME =
p_batch_source_name AND org_id = p_org_id;
RETURN
v_batch_source_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for Batch Source Name :'
|| p_batch_source_name
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'NO DATA
for Batch Source Name :'
|| p_batch_source_name;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for Batch Source Name :'
|| p_batch_source_name
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for Batch Source Name :'
||
p_batch_source_name;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for Batch Source Name :'
|| SQLERRM
|| p_batch_source_name
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'In Others
for Batch Source Name :'
||
p_batch_source_name;
END xx_batch_src_fun;
--==================== Batch Source Name Function
Ends=========================
--====================
Line Type Function Starts========================
FUNCTION
xx_line_type_fun (p_line_type VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
SELECT lookup_code
INTO v_line_type
FROM ar_lookups
WHERE meaning =
p_line_type AND lookup_type = 'AR_LINE_INVOICE';
RETURN v_line_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for Line Type :' || p_line_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for Line Type :' ||
p_line_type;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for Line Type :' || p_line_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'TOO MANY ROWS for Line Type :'
||
p_line_type;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for Line Type :' || p_line_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'In Others for Line Type :' ||
p_line_type;
END xx_line_type_fun;
--==================== Line Type Function
Ends=========================
--====================
CUST_TRX_TYPE Function
Starts========================
FUNCTION
xx_cust_trx_type_fun (p_cust_trx_type VARCHAR2, p_org_id NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT
cust_trx_type_id
INTO
v_cust_trx_type_id
FROM
ra_cust_trx_types_all
WHERE NAME =
p_cust_trx_type AND org_id = p_org_id;
RETURN
v_cust_trx_type_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for p_CUST_TRX_TYPE :' || p_cust_trx_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'NO DATA
for p_CUST_TRX_TYPE :'
||
p_cust_trx_type;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_CUST_TRX_TYPE :'
|| p_cust_trx_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for p_CUST_TRX_TYPE :'
|| p_cust_trx_type;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for p_CUST_TRX_TYPE :'
|| p_cust_trx_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'In Others
for p_CUST_TRX_TYPE :'
||
p_cust_trx_type;
END
xx_cust_trx_type_fun;
--====================CUST_TRX_TYPE Function
Ends=========================
--====================
Currency Code Function Starts===========================
FUNCTION
xx_currency_fun (p_currency_code VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
SELECT currency_code
INTO
v_currency_code
FROM fnd_currencies
WHERE currency_code
= p_currency_code;
RETURN
v_currency_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for Line Type :' || p_currency_code
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'NO DATA for Line Type :'
||
p_currency_code;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for Line Type :' || p_currency_code
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for Line Type :'
||
p_currency_code;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for Line Type :' || p_currency_code
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for Line Type :'
||
p_currency_code;
END xx_currency_fun;
--==================== Currency Code Function
Ends===========================
--====================
BILL CUSTOMER Function Starts===========================
FUNCTION
xx_bill_cust_fun (p_bill_cust VARCHAR2)
RETURN NUMBER
IS
BEGIN
SELECT party_id
INTO
v_bill_cust_id
FROM hz_parties
WHERE party_name =
p_bill_cust;
RETURN v_bill_cust_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for p_bill_cust :' || p_bill_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for p_bill_cust :' ||
p_bill_cust;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_bill_cust:' || p_bill_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for p_bill_cust :'
||
p_bill_cust;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for p_bill_cust :' || p_bill_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for p_bill_cust :'
|| p_bill_cust;
END xx_bill_cust_fun;
--==================== BILL CUSTOMER Function
Ends===========================
--====================
BILL ADDRESS Function Starts===========================
FUNCTION
xx_bill_addr_fun (p_bill_addr VARCHAR2)
RETURN NUMBER
IS
BEGIN
SELECT
cust_acct_site_id
INTO
v_bill_addr_id
FROM
hz_cust_site_uses_all
WHERE site_use_code
= 'BILL_TO' AND LOCATION = p_bill_addr;
RETURN
v_bill_addr_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for P_BILL_ADDR :' || p_bill_addr
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for P_BILL_ADDR :' ||
p_bill_addr;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for P_BILL_ADDR:' || p_bill_addr
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for P_BILL_ADDR :'
||
p_bill_addr;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for P_BILL_ADDR :' || p_bill_addr
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for P_BILL_ADDR :'
||
p_bill_addr;
END xx_bill_addr_fun;
--==================== BILL ADDRES Function
Ends===========================
--====================
SHIP CUSTOMER Function Starts===========================
FUNCTION
xx_ship_cust_fun (p_ship_cust VARCHAR2)
RETURN NUMBER
IS
BEGIN
SELECT party_id
INTO
v_ship_cust_id
FROM hz_parties
WHERE party_name =
p_ship_cust;
RETURN
v_ship_cust_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for p_ship_cust :' || p_ship_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for p_ship_cust :' ||
p_ship_cust;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_ship_cust:' || p_ship_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| '
'
|| 'TOO MANY
ROWS for p_ship_cust :'
||
p_ship_cust;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for p_ship_cust :' || p_ship_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for p_ship_cust :'
||
p_ship_cust;
END xx_ship_cust_fun;
--==================== SHIP
CUSTOMER Function Ends===========================
--====================
SHIP ADDRESS Function Starts===========================
FUNCTION
xx_ship_addr_fun (
p_ship_addr VARCHAR2,
p_org_id NUMBER,
p_ship_cust_id NUMBER
)
RETURN NUMBER
IS
BEGIN
SELECT
hcas.cust_acct_site_id
INTO
v_ship_addr_id
FROM hz_parties
hp,
hz_party_sites hps,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all
hcsu
WHERE hp.party_id =
hps.party_id
AND hca.party_id
= hp.party_id
AND
hca.cust_account_id = hcas.cust_account_id
AND
hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND
hps.party_site_id = hcas.party_site_id
AND
hcsu.site_use_code = 'SHIP_TO'
AND hp.party_id =
p_ship_cust_id
AND hcas.org_id =
p_org_id
AND LOCATION =
p_ship_addr;
RETURN
v_ship_addr_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for P_ship_ADDR :' || p_ship_addr
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for P_BILL_ADDR :' ||
p_ship_addr;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for P_ship_ADDR:' || p_ship_addr
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for P_ship_ADDR :'
||
p_ship_addr;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for P_ship_ADDR :' || p_ship_addr
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for P_ship_ADDR :'
||
p_ship_addr;
END xx_ship_addr_fun;
--==================== SHIP ADDRES Function
Ends===========================
--====================
SOLD CUSTOMER Function Starts===========================
FUNCTION
xx_sold_cust_fun (p_sold_cust VARCHAR2)
RETURN NUMBER
IS
BEGIN
SELECT party_id
INTO v_sold_cust_id
FROM hz_parties
WHERE party_name =
p_sold_cust;
RETURN
v_sold_cust_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for p_sold_cust :' || p_sold_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for p_sold_cust :' ||
p_sold_cust;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_bill_cust:' || p_sold_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for p_sold_cust :'
||
p_sold_cust;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for p_bill_cust :' || p_sold_cust
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for p_sold_cust :'
||
p_sold_cust;
END xx_sold_cust_fun;
--==================== SOLD CUSTOMER Function
Ends===========================
--====================
UNIT_OF_MEASURE Function Starts===========================
FUNCTION xx_uom_fun
(p_unit_of_measure VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
SELECT uom_code
INTO v_uom_code
FROM
mtl_units_of_measure_tl
WHERE
unit_of_measure = p_unit_of_measure;
RETURN v_uom_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for UNIT_OF_MEASURE :'
|| p_unit_of_measure
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'NO DATA
for UNIT_OF_MEASURE :'
||
p_unit_of_measure;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for UNIT_OF_MEASURE :'
|| p_unit_of_measure
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for UNIT_OF_MEASURE :'
||
p_unit_of_measure;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for UNIT_OF_MEASURE :'
|| p_unit_of_measure
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'In Others
for UNIT_OF_MEASURE :'
||
p_unit_of_measure;
END xx_uom_fun;
--==================== UNIT_OF_MEASURE Function
Ends===========================
--====================
Term Function Starts===========================
FUNCTION xx_term_fun
(p_term_name VARCHAR2)
RETURN NUMBER
IS
BEGIN
SELECT term_id
INTO v_term_id
FROM ra_terms_tl
WHERE NAME =
p_term_name;
RETURN v_term_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for p_term_name:' || p_term_name
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' ' || 'NO DATA for p_term_name :' ||
p_term_name;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_term_name :' || p_term_name
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for p_term_name :'
||
p_term_name;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for p_term_name:' || p_term_name
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg ||
' ' || 'In Others for p_term_name :'
||
p_term_name;
END xx_term_fun;
--==================== Term Function
Ends===========================
--====================
Item Function Starts===========================
FUNCTION xx_item_fun
(p_item VARCHAR2, p_org_id NUMBER)
RETURN NUMBER
IS
BEGIN
SELECT
inventory_item_id
INTO
v_inventory_item_id
FROM
mtl_system_items_b
WHERE segment1 =
p_item AND organization_id = p_org_id;
RETURN
v_inventory_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG, 'NO DATA for p_item:' || p_item);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg || ' ' || 'NO DATA for p_item :' || p_item;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_item :' || p_item
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg || ' ' || 'TOO MANY
ROWS for p_item :' || p_item;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG, 'In Others for p_item:' || p_item);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg || ' ' || 'In Others
for p_item :' || p_item;
END xx_item_fun;
--==================== Item Function
Ends===========================
--====================
Conversion Type Function Starts===========================
FUNCTION
xx_conv_type_fun (p_conversion_type VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
SELECT
conversion_type
INTO
v_conversion_type
FROM
gl_daily_conversion_types
WHERE
conversion_type = p_conversion_type;
RETURN
v_conversion_type;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for p_conversion_type:'
|| p_conversion_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'NO DATA
for p_conversion_type :'
||
p_conversion_type;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line (fnd_file.LOG,
'TOO MANY ROWS for p_conversion_type :'
|| p_conversion_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for p_conversion_type :'
||
p_conversion_type;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for p_conversion_type:'
|| p_conversion_type
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'In Others
for p_conversion_type :'
|| p_conversion_type;
END xx_conv_type_fun;
--==================== Conversion Type Function
Ends===========================
--====================
Fob Function
Starts===========================
FUNCTION xx_fob_fun
(p_fob VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
SELECT lookup_code
INTO v_fob
FROM
fnd_lookup_values
WHERE lookup_type =
'FOB'
AND meaning =
p_fob
AND
view_application_id = 222;
RETURN v_fob;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG, 'NO DATA for p_fob:' || p_fob);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg || ' ' || 'NO DATA for p_fob
:' || p_fob;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for p_fob :' || p_fob
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg || ' ' || 'TOO MANY
ROWS for p_fob :' || p_fob;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG, 'In Others for p_fob:' || p_fob);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg || ' ' || 'In Others
for p_fob :' || p_fob;
END xx_fob_fun;
--==================== Fob
Function Ends===========================
--==============================================================================
--
DISTRIBUTIONS VALIDATION FUNCTIONS
--==============================================================================
--====================
CODE_COMBINATION Function
Starts===========================
FUNCTION
xx_code_combination_fun (p_code_combination VARCHAR2)
RETURN NUMBER
IS
BEGIN
SELECT code_combination_id
INTO
v_code_combination_id
FROM
gl_code_combinations_kfv
WHERE
concatenated_segments = p_code_combination;
RETURN
v_code_combination_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'NO DATA for P_CODE_COMBINATION:'
|| p_code_combination
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| '
'
|| 'NO DATA
for P_CODE_COMBINATION :'
||
p_code_combination;
WHEN TOO_MANY_ROWS
THEN
fnd_file.put_line
(fnd_file.LOG,
'TOO MANY ROWS for P_CODE_COMBINATION :'
|| p_code_combination
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'TOO MANY
ROWS for P_CODE_COMBINATION :'
|| p_code_combination;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'In Others for P_CODE_COMBINATION:'
|| p_code_combination
);
v_process_flag :=
'E';
v_error_msg :=
v_error_msg
|| ' '
|| 'In Others
for P_CODE_COMBINATION :'
||
p_code_combination;
END
xx_code_combination_fun;
--==================== CODE_COMBINATION Function Ends===========================
END xx_ar_inv_pkg;
No comments:
Post a Comment