Oracle Apps Technical: Mass Additions Interface

Asset Interface

Data File:

CREATE TABLE APPS.XXORC_ASSETS_INSERT
(
STG_ID NUMBER,
ASSET_NUMBER VARCHAR2(50 BYTE),
TAG_NUMBER VARCHAR2(50 BYTE),
SERIAL_NUMBER VARCHAR2(50 BYTE),
IN_USE_FLAG VARCHAR2(50 BYTE),
NEW_USED VARCHAR2(50 BYTE),
OWNED_LEASED VARCHAR2(50 BYTE),
CURRENT_UNITS NUMBER,
ASSET_DESCRIPTION VARCHAR2(50 BYTE),
ASSET_KEY_CCID NUMBER,
CATEGORY_ID NUMBER,
ASSET_TYPE VARCHAR2(50 BYTE),
SET_OF_BOOKS_ID NUMBER,
DATE_PLACED_IN_SERVICE DATE,
DEPRN_START_DATE DATE,
DEPRN_METHOD_CODE VARCHAR2(50 BYTE),
PRORATE_CONVENTION_CODE VARCHAR2(50 BYTE),
LIFE_IN_MONTHS NUMBER,
ORIGINAL_COST NUMBER,
BOOK_TYPE_CODE VARCHAR2(50 BYTE),
UNITS_ASSIGNED NUMBER,
EXPENSE_CCID NUMBER,
LOCATION_CCID NUMBER,
INVOICE_LINE_NUMBER NUMBER,
AP_DISTRIBUTION_LINE_NUMBER NUMBER,
DESCRIPTION VARCHAR2(50 BYTE),
PO_VENDOR_ID NUMBER,
PO_NUMBER NUMBER,
EMPLOYEE_NUM NUMBER,
PAYABLES_BATCH_NAME VARCHAR2(50 BYTE),
FIXED_ASSETS_COST NUMBER,
DELETED_FLAG VARCHAR2(50 BYTE),
BATCH_ID NUMBER,
PAYABLES_CODE_COMBINATION_ID NUMBER,
CONCATENATED_SEG VARCHAR2(50 BYTE),
FEEDER_SYSTEM_NAME VARCHAR2(50 BYTE),
PAYABLES_COST NUMBER,
INV_INDICATOR NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2(50 BYTE),
INVOICE_ID NUMBER,
INVOICE_NUMBER VARCHAR2(50 BYTE),
ATTRIBUT1 NUMBER,
ATTRIBUT2 NUMBER,
ATTRIBUT3 NUMBER
)

Insert Data Into Custom Table :

Insert into APPS.XXORC_ASSETS_INSERT
(STG_ID, ASSET_NUMBER, TAG_NUMBER, SERIAL_NUMBER, IN_USE_FLAG, NEW_USED, OWNED_LEASED, CURRENT_UNITS, ASSET_DESCRIPTION, ASSET_KEY_CCID, CATEGORY_ID, ASSET_TYPE, SET_OF_BOOKS_ID, DATE_PLACED_IN_SERVICE, DEPRN_START_DATE, DEPRN_METHOD_CODE, PRORATE_CONVENTION_CODE, LIFE_IN_MONTHS, ORIGINAL_COST, BOOK_TYPE_CODE, UNITS_ASSIGNED, EXPENSE_CCID, LOCATION_CCID, INVOICE_LINE_NUMBER, AP_DISTRIBUTION_LINE_NUMBER, DESCRIPTION, PO_VENDOR_ID, PO_NUMBER, EMPLOYEE_NUM, PAYABLES_BATCH_NAME, FIXED_ASSETS_COST, DELETED_FLAG, BATCH_ID, PAYABLES_CODE_COMBINATION_ID, CONCATENATED_SEG, FEEDER_SYSTEM_NAME, PAYABLES_COST, INV_INDICATOR, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, VENDOR_ID, VENDOR_NAME, INVOICE_ID, INVOICE_NUMBER, ATTRIBUT1, ATTRIBUT2, ATTRIBUT3)
Values
(1, NULL, '6845412', '87542', 'YES',
'NEW', 'OWNED', 2, 'VIVEK CORP', 2,
33, 'CAPITALIZED', 1024, TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'STL', 'MID-MONTH', 240, 50000, 'OPS CORP',
1, 17021, 2, 1, 1,
'MRF CORP', NULL, 962, NULL, 'SRT CORP',
2000, 'NO', 1000, 13528, NULL,
'ACK', 9000, 1, TO_DATE('03/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1013497,
TO_DATE('03/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1013497, 1013497, 1014, 'Consolidated Supplies',
NULL, '1176223', NULL, NULL, NULL);
Insert into APPS.XXORC_ASSETS_INSERT
(STG_ID, ASSET_NUMBER, TAG_NUMBER, SERIAL_NUMBER, IN_USE_FLAG, NEW_USED, OWNED_LEASED, CURRENT_UNITS, ASSET_DESCRIPTION, ASSET_KEY_CCID, CATEGORY_ID, ASSET_TYPE, SET_OF_BOOKS_ID, DATE_PLACED_IN_SERVICE, DEPRN_START_DATE, DEPRN_METHOD_CODE, PRORATE_CONVENTION_CODE, LIFE_IN_MONTHS, ORIGINAL_COST, BOOK_TYPE_CODE, UNITS_ASSIGNED, EXPENSE_CCID, LOCATION_CCID, INVOICE_LINE_NUMBER, AP_DISTRIBUTION_LINE_NUMBER, DESCRIPTION, PO_VENDOR_ID, PO_NUMBER, EMPLOYEE_NUM, PAYABLES_BATCH_NAME, FIXED_ASSETS_COST, DELETED_FLAG, BATCH_ID, PAYABLES_CODE_COMBINATION_ID, CONCATENATED_SEG, FEEDER_SYSTEM_NAME, PAYABLES_COST, INV_INDICATOR, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, VENDOR_ID, VENDOR_NAME, INVOICE_ID, INVOICE_NUMBER, ATTRIBUT1, ATTRIBUT2, ATTRIBUT3)
Values
(1, NULL, '6845412', '87542', 'YES',
'NEW', 'OWNED', 2, 'VIVEK CORP', 2,
33, 'CAPITALIZED', 1024, TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/30/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
'STL', 'MID-MONTH', 240, 50000, 'OPS CORP',
1, 17021, 2, 1, 1,
'MRF CORP', NULL, 962, NULL, 'SRT CORP',
2000, 'NO', 1000, 13528, NULL,
'ACK', 9000, 1, TO_DATE('03/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1013497,
TO_DATE('03/01/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1013497, 1013497, 1014, 'Consolidated Supplies',

NULL, '1176223', NULL, NULL, NULL);

Like This U Can As Many Record :

Step 3:

CREATE OR REPLACE PACKAGE BODY APPS.xxHRS_assets_webadi_package
AS
PROCEDURE xxHRS_load_procedure (
p_stg_id NUMBER,
p_asset_number VARCHAR2,
p_tag_number VARCHAR2,
p_serial_number VARCHAR2,
p_in_use_flag VARCHAR2,
p_new_used VARCHAR2,
p_owned_leased VARCHAR2,
p_current_units NUMBER,
p_asset_description VARCHAR2,
p_asset_key_ccid NUMBER,
p_category_id NUMBER,
p_asset_type VARCHAR2,
p_set_of_books_id NUMBER,
p_date_placed_in_service DATE,
p_deprn_start_date DATE,
p_deprn_method_code VARCHAR2,
p_prorate_convention_code VARCHAR2,
p_life_in_months NUMBER,
p_original_cost NUMBER,
p_book_type_code VARCHAR2,
p_units_assigned NUMBER,
p_expense_ccid NUMBER,
p_location_ccid NUMBER,
p_invoice_line_number NUMBER,
p_ap_distribution_line_number NUMBER,
p_description VARCHAR2,
p_po_vendor_id NUMBER,
p_po_number NUMBER,
p_employee_num NUMBER,
p_payables_batch_name VARCHAR2,
p_fixed_assets_cost NUMBER,
p_deleted_flag VARCHAR2,
p_batch_id NUMBER,
p_payables_code_combination_id NUMBER,
p_concatenated_seg VARCHAR2,
p_feeder_system_name VARCHAR2,
p_payables_cost NUMBER,
p_inv_indicator NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_date DATE,
last_updated_by NUMBER,
last_update_login NUMBER,
p_vendor_id NUMBER,
p_vendor_name VARCHAR2,
p_invoice_id NUMBER,
p_invoice_number VARCHAR2,
p_attribut1 NUMBER,
p_attribut2 NUMBER,
p_attribut3 NUMBER

)

IS
l_error_message VARCHAR2 (3000);
l_error_code VARCHAR2 (2) DEFAULT 'V';
l_org_id NUMBER;
l_invoice_num VARCHAR2 (60);
l_vendor_id NUMBER;
l_code_combination_id NUMBER;
l_category_id NUMBER;
l_tag_number NUMBER;
l_serial_number NUMBER;
l_method_code VARCHAR2 (80);
l_prorate_convention VARCHAR2 (80);
l_life_in_months NUMBER;
l_payable_code_id NUMBER;
l_in_use_flag VARCHAR2 (30);
l_owned_leased VARCHAR2 (30);
l_person_id NUMBER;
PS_COUNT NUMBER;
l_raise_error EXCEPTION;
BEGIN
l_error_message := '';

--Invoice Number Validations
----------------------------invoice validation-------------------------------------------------
-- BEGIN
-- if p_invoice_number is null then
-- L_ERROR_CODE :='E';
-- l_error_message :=
-- l_error_message
-- || ','
-- || 'Invoice number Should Not Be Null ';
-- l_error_code := 'E';
-- end if;
-- begin
-- SELECT DISTINCT api.invoice_num
-- INTO l_invoice_num
-- FROM ap_invoices_all api
-- WHERE UPPER (api.invoice_num) = UPPER (p_invoice_number);
-- exception
-- when no_data_found then
-- NULL;
-- WHEN OTHERS
-- THEN
--l_error_code := 'E';
-- l_error_message :=
-- l_error_message
-- || ','
-- || 'Other Errors with invoice number '
-- || SQLCODE
-- || SQLERRM;
--
-- END;
-- IF l_invoice_num =p_invoice_number THEN
--- l_error_code := 'E';
-- l_error_message :=
-- l_error_message
-- || ','
-- || 'Invoice number already exist in oracle ';
--
-- END IF;
-- END;
----------------invoice validation-------------------------------------------------
l_invoice_num := NULL;

BEGIN
SELECT invoice_num
INTO l_invoice_num
FROM ap_invoices_all
WHERE invoice_num = p_invoice_number
AND
org_id = fnd_profile.VALUE ('ORG_ID');

IF l_invoice_num IS NULL
THEN
l_error_message :=
l_error_message || ','
|| ' Invoice number Should Not Be Null ';
l_error_code := 'E';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message || ','
|| ' Please enter valid invoice number ';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| ' other exception for given invoice number '
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;
-----------------------invoice validation error-------------------------
--Supplier Name Validation
l_vendor_id := NULL;

BEGIN
SELECT distinct vendor_id
INTO l_vendor_id
FROM ap_suppliers aps
WHERE ( UPPER (aps.vendor_name) = UPPER (p_vendor_name)
OR UPPER (aps.segment1) = UPPER (p_vendor_id)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message || ',' || 'Supplier not defined ';
l_error_code := 'E';
WHEN TOO_MANY_ROWS
THEN
l_error_message :=
l_error_message || ',' || 'Too Many Records for Supplier ';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| 'Other Errors with Supplier '
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

--Category ID Validations
l_category_id := NULL;

BEGIN
SELECT distinct category_id
INTO l_category_id
FROM fa_categories
where category_id=p_category_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message
|| ','
|| 'no data found for given Asset Category';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| ' others exception for given Asset Category '
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

--Tag Number Validations
-- BEGIN
-- SELECT tag_number
-- INTO l_tag_number
-- FROM fa_additions_b fab
-- WHERE UPPER (fab.tag_number) = UPPER (p_tag_number);

-- IF l_tag_number > 0
-- THEN
-- RAISE l_raise_error;
-- END IF;
-- EXCEPTION
-- WHEN l_raise_error
-- THEN
-- l_error_message :=
-- l_error_message || ','
-- || 'Tag Number Already Exist in Oracle ';
-- l_error_code := 'E';
-- END;
----------------------tag validation------------------
begin
if p_tag_number is null then
L_ERROR_CODE :='E';
l_error_message :=
l_error_message
|| ','
|| 'Tag number Should Not Be Null ';
l_error_code := 'E';
end if;
begin
SELECT distinct tag_number
INTO l_tag_number
FROM fa_additions_b
WHERE UPPER (tag_number) = UPPER (p_tag_number);
exception
when no_data_found then
NULL;
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| 'Other Errors with Tag number '
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;
IF l_tag_number =p_tag_number THEN
l_error_code := 'E';
l_error_message :=
l_error_message
|| ','
|| 'Tag number already exist in oracle ';
END IF;
END;

---------------------tag----------------

--Serial Number Validations
-- BEGIN
-- SELECT serial_number
-- INTO l_serial_number
-- FROM fa_additions_b fab
-- WHERE UPPER (serial_number) = UPPER (p_serial_number);

-- IF l_serial_number > 0
-- THEN
-- RAISE l_raise_error;
-- END IF;
-- EXCEPTION
-- WHEN l_raise_error
-- THEN
-- l_error_message :=
-- l_error_message
-- || ','
-- || 'Serial Number Already Exist in Oracle ';
-- l_error_code := 'E';
-- END;
BEGIN
if p_serial_number is null then
L_ERROR_CODE :='E';
l_error_message :=
l_error_message
|| ','
|| 'Serial number Should Not Be Null ';
l_error_code := 'E';
end if;
begin
SELECT distinct serial_number
INTO l_serial_number
FROM fa_additions_b
WHERE UPPER (serial_number) = UPPER (p_serial_number);
exception
when no_data_found then
NULL;
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| 'Other Errors with Serial number '
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;
IF l_serial_number =p_serial_number THEN
l_error_code := 'E';
l_error_message :=
l_error_message
|| ','
|| 'Serial number already exist in oracle ';
END IF;
END;

--validation of Depreciation Method
l_method_code := NULL;

BEGIN
SELECT DISTINCT method_code
INTO l_method_code
FROM fa_methods
WHERE method_code = p_deprn_method_code;

IF l_method_code IS NULL
THEN
l_error_message :=
l_error_message
|| ','
|| ' Please enter valid DEPRN_METHOD_CODE';
l_error_code := 'E';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message || ',' || ' Please enter DEPRN_METHOD_CODES';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| ' Please enter DEPRN_METHOD_CODE'
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

--validation of prorate_convention_code
l_prorate_convention := NULL;

BEGIN
SELECT DISTINCT prorate_convention_code
INTO l_prorate_convention
FROM fa_books
WHERE prorate_convention_code = p_prorate_convention_code;

IF l_prorate_convention IS NULL
THEN
l_error_message :=
l_error_message || ',' || ' Please PRORATE_CONVENTION_CODES';
l_error_code := 'E';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message || ',' || ' Please PRORATE_CONVENTION_CODE NO DATA';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| ' Please PRORATE_CONVENTION_CODE'
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

--Validation for LIFE_IN_MONTHS
l_life_in_months := NULL;

BEGIN
SELECT DISTINCT life_in_months
INTO l_life_in_months
FROM fa_books
WHERE life_in_months = p_life_in_months;

IF l_life_in_months IS NULL
THEN
l_error_message :=
l_error_message || ','
|| ' LIFE_IN_MONTHS not be null ';
l_error_code := 'E';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message || ',' || ' Please enter valid LIFE_IN_MONTHS';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| ' Please enter valid LIFE_IN_MONTHS'
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

--Validation of PAYABLES_CODE_COMBINATION_ID
l_payable_code_id := NULL;

BEGIN
SELECT code_combination_id
INTO l_payable_code_id
FROM gl_code_combinations_kfv
WHERE code_combination_id = p_payables_code_combination_id;
IF l_payable_code_id IS NULL
THEN
l_error_message :=
l_error_message || ','
|| 'payable code not be null ';
l_error_code := 'E';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message || ',' || 'Enter a Valid Segments ';
l_error_code := 'E';
WHEN TOO_MANY_ROWS
THEN
l_error_message := l_error_message || ',' || 'Too Many Segments';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| 'Other Errors with Segments'
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

--Validation of In Use Flag
l_in_use_flag := NULL;

BEGIN
SELECT in_use_flag
INTO l_in_use_flag
FROM fa_additions
WHERE UPPER (in_use_flag) = UPPER (p_in_use_flag) AND ROWNUM <= 1;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_error_message :=
l_error_message
|| ','
|| 'no data found for given FLAG';
l_error_code := 'E';
WHEN OTHERS
THEN
l_error_message :=
l_error_message
|| ','
|| ' others exception for given FLAG '
|| SQLCODE
|| SQLERRM;
l_error_code := 'E';
END;

-- --Validation of Own Leased
-- l_owned_leased := NULL;

-- BEGIN
-- SELECT owned_leased
-- INTO l_owned_leased
-- FROM fa_additions fa, fa_asset_invoices fai, ap_invoices_all aia
-- WHERE 1 = 1
-- AND fa.asset_id = fai.asset_id
-- AND fai.invoice_id = aia.invoice_id
-- AND aia.invoice_num = l_invoice_num
-- AND owned_leased = p_owned_leased
-- AND ROWNUM <= 1;
-- EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- l_error_message :=
-- l_error_message
-- || ','
-- || 'no data found for given owned_leased';
-- l_error_code := 'E';
-- WHEN OTHERS
-- THEN
-- l_error_message :=
-- l_error_message
-- || ','
-- || ' others exception for given owned_leased '
-- || SQLCODE
-- || SQLERRM;
-- l_error_code := 'E';
-- END;

l_person_id := NULL;

--Validations of Employee number
-- BEGIN
-- SELECT papf.person_id
-- INTO l_person_id
-- FROM per_all_people_f papf
-- WHERE papf.employee_number = p_employee_num;
-- EXCEPTION
-- WHEN NO_DATA_FOUND
-- THEN
-- l_error_message :=
-- l_error_message || ',' || 'Enter a Valid employee number ';
-- l_error_code := 'E';
-- WHEN OTHERS
-- THEN
-- l_error_message :=
-- l_error_message
-- || ','
-- || 'Other Errors with employee number '
-- || SQLCODE
-- || SQLERRM;
-- l_error_code := 'E';
-- DBMS_OUTPUT.put_line (l_person_id);
-- END;

--INSERTING RECORDS INTO STAGING TABLE
BEGIN
SELECT COUNT(STG_ID)
INTO PS_COUNT
FROM XXORC_ASSETS_INSERT;
if l_error_code != 'E'
AND PS_COUNT>0
then
INSERT INTO XXORC_ASSETS_INSERT
VALUES (p_stg_id
,p_asset_number
,p_tag_number
,p_serial_number
,p_in_use_flag
,p_new_used
,p_owned_leased
,p_current_units
,p_asset_description
,p_asset_key_ccid
,p_category_id
,p_asset_type
,p_set_of_books_id
,p_date_placed_in_service
,p_deprn_start_date
,p_deprn_method_code
,p_prorate_convention_code
,p_life_in_months
,p_original_cost
,p_book_type_code
,p_units_assigned
,p_expense_ccid
,p_location_ccid
,p_invoice_line_number
,p_ap_distribution_line_number
,p_description
,p_po_vendor_id
,p_po_number
,p_employee_num
,p_payables_batch_name
,p_fixed_assets_cost
,p_deleted_flag
,p_batch_id
,l_payable_code_id
,p_concatenated_seg
,p_feeder_system_name
,p_payables_cost
,p_inv_indicator
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,p_vendor_id
,p_vendor_name
,p_invoice_id
,l_invoice_num
,p_attribut1
,p_attribut2
,p_attribut3);
commit;
end if;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20002,
'Error -' || SQLCODE || '-' || SQLERRM
);
END;
IF (l_error_code = 'E')
THEN
raise_application_error (-20102, SUBSTR (l_error_message, 2));
end if;
-- IF(l_error_code !='E')
-- THEN
-- UPDATE XXORC_ASSETS_INSERT SET Upl=1;
-- ELSE
-- UPDATE XXORC_ASSETS_INSERT SET Upl=NULL;
--

END;

END;

Step 4: Transfer From Custom table To Base Table

CREATE OR REPLACE PACKAGE BODY XXHRS_assets_src_line_package

-- +================================================================================+

-- | +

-- +================================================================================+

-- | Project : FIXED ASSET |

-- | Created By : Vivek |

-- | $Id: xx_assets_src_line_package.pkb |

-- | |

-- | Description : Package for |

-- | Creating a Fixed Asset |

-- | |

-- |Version Date Author Remarks |

-- +======= =========== ============= ========================================+

-- |1.0 21-FEB-2018 Vivek Initial Version. |

-- | |

--+=================================================================================+

PROCEDURE LOG (p_message IN VARCHAR2)

BEGIN

fnd_file.put_line (fnd_file.LOG, p_message);

DBMS_OUTPUT.put_line (p_message);

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.LOG, 'Check Error In LOG:' || SQLERRM);

DBMS_OUTPUT.put_line ('Check Error In LOG:' || SQLERRM);

END;

PROCEDURE XXHRS_asset_src_line_procedure (

p_errbuf OUT VARCHAR2,

p_retcode OUT VARCHAR2,

p_stg_id IN NUMBER

)

CURSOR c1

SELECT *

FROM XXORC_ASSETS_INSERT

WHERE stg_id = p_stg_id;

l_trans_rec fa_api_types.trans_rec_type;

l_dist_trans_rec fa_api_types.trans_rec_type;

l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;

l_asset_desc_rec fa_api_types.asset_desc_rec_type;

l_asset_cat_rec fa_api_types.asset_cat_rec_type;

l_asset_type_rec fa_api_types.asset_type_rec_type;

l_asset_hierarchy_rec fa_api_types.asset_hierarchy_rec_type;

l_asset_fin_rec fa_api_types.asset_fin_rec_type;

l_asset_deprn_rec fa_api_types.asset_deprn_rec_type;

l_asset_dist_rec fa_api_types.asset_dist_rec_type;

l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;

l_inv_tbl fa_api_types.inv_tbl_type;

l_inv_rate_tbl fa_api_types.inv_rate_tbl_type;

l_inv_rec_type fa_api_types.inv_rec_type;

l_return_status VARCHAR2 (1);

l_mesg_count NUMBER;

l_mesg VARCHAR2 (4000);

l_user_id NUMBER;

n NUMBER := 1;

l_stg_id NUMBER;

BEGIN

fnd_file.put_line (fnd_file.LOG, 'setting the context');

LOG ('==========PROGRAM STARTS HERE===========');

-- mo_global.init ('FA');

fnd_global.apps_initialize (user_id => fnd_global.user_id,

--1013497 , --l_user_id

resp_id => fnd_global.resp_id,

--20563 --l_resp_id = Fixed Asset Manager

resp_appl_id => fnd_global.resp_appl_id

-- 140 --l_resp_app_id -- 222);

);

-- mo_global.set_policy_context ('S', 204);

mo_global.set_policy_context ('S', fnd_profile.VALUE ('ORG_ID'));

DBMS_OUTPUT.ENABLE (10000000);

fa_srvr_msg.init_server_message;

--Stage ID Validations

BEGIN

l_stg_id := NULL;

SELECT COUNT (stg_id)

INTO l_stg_id

FROM xx_fixed_assets_stg

WHERE stg_id = p_stg_id AND status = 'Y';

IF l_stg_id > 0

THEN

LOG (p_stg_id || ' STAGE ID already Processed');

END IF;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

LOG (l_stg_id || 'STAGE ID NOT FOUND');

WHEN OTHERS

THEN

LOG ('STAGE ID NOT FOUND' || SQLERRM);

END;

BEGIN

FOR r1 IN c1

LOOP

-- desc info

l_asset_desc_rec.asset_number := r1.asset_number;

l_asset_desc_rec.tag_number := r1.tag_number;

l_asset_desc_rec.serial_number := r1.serial_number;

l_asset_desc_rec.in_use_flag := r1.in_use_flag;

l_asset_desc_rec.new_used := r1.new_used;

l_asset_desc_rec.owned_leased := r1.owned_leased;

l_asset_desc_rec.current_units := r1.current_units;

l_asset_desc_rec.description := r1.asset_description;

l_asset_desc_rec.asset_key_ccid := r1.asset_key_ccid;

-- cat info

-- Valid Value in FA_CATEGORIES

l_asset_cat_rec.category_id := r1.category_id;

--type info

l_asset_type_rec.asset_type := r1.asset_type;

-- Asset Financial Information --

l_asset_fin_rec.date_placed_in_service :=

r1.date_placed_in_service;

l_asset_fin_rec.deprn_start_date := r1.deprn_start_date;

l_asset_fin_rec.life_in_months := r1.life_in_months;

l_asset_fin_rec.original_cost := r1.original_cost;

--l_asset_fin_rec.COST := r1.costs;

-- deprn info

l_asset_deprn_rec.set_of_books_id := r1.set_of_books_id;

-- book / trans info

-- Valid value in FA_BOOK_CONTROLS

l_asset_hdr_rec.book_type_code := r1.book_type_code;

-- distribution info

l_asset_dist_rec.units_assigned := r1.units_assigned;

-- Valid Record from GL Code cominations with record type = 'E' (Expense)

l_asset_dist_rec.expense_ccid := r1.expense_ccid;

-- Valid Value in FA Locations

l_asset_dist_rec.location_ccid := r1.location_ccid;

-- l_asset_dist_rec.assigned_to := r1.assigned_to;

l_asset_dist_tbl (1) := l_asset_dist_rec;

----Inv Information

-- l_inv_rec_type.invoice_number := r1.invoice_num;

-- l_inv_rec_type.attribute1 := r1.attribute1;

-- l_inv_rec_type.attribute2 := r1.attribute2;

-- l_inv_rec_type.attribute3 := r1.attribute3;

l_inv_rec_type.invoice_id := r1.invoice_id;

l_inv_rec_type.invoice_line_number := r1.invoice_line_number;

l_inv_rec_type.ap_distribution_line_number :=

r1.ap_distribution_line_number;

l_inv_rec_type.description := r1.description;

l_inv_rec_type.po_vendor_id := r1.po_vendor_id;

l_inv_rec_type.po_number := r1.po_number;

l_inv_rec_type.payables_batch_name := r1.payables_batch_name;

l_inv_rec_type.fixed_assets_cost := r1.fixed_assets_cost;

l_inv_rec_type.deleted_flag := r1.deleted_flag;

l_inv_rec_type.create_batch_id := r1.batch_id;

l_inv_rec_type.payables_code_combination_id :=

r1.payables_code_combination_id;

l_inv_rec_type.feeder_system_name := r1.feeder_system_name;

l_inv_rec_type.payables_cost := r1.payables_cost;

l_inv_rec_type.inv_indicator := r1.inv_indicator;

l_inv_tbl (n) := l_inv_rec_type;

n := n + 1;

LOG ('Invoice_ID: ' || l_inv_rec_type.invoice_id);

LOG ('Invoice_Number: ' || l_inv_rec_type.invoice_number);

END LOOP;

-- call the api

fa_addition_pub.do_addition

(

-- std parameters

p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_false,

p_validation_level => fnd_api.g_valid_level_full,

p_calling_fn => NULL,

x_return_status => l_return_status,

x_msg_count => l_mesg_count,

x_msg_data => l_mesg,

-- api parameters

px_trans_rec => l_trans_rec,

px_dist_trans_rec => l_dist_trans_rec,

px_asset_hdr_rec => l_asset_hdr_rec,

px_asset_desc_rec => l_asset_desc_rec,

px_asset_type_rec => l_asset_type_rec,

px_asset_cat_rec => l_asset_cat_rec,

px_asset_hierarchy_rec => l_asset_hierarchy_rec,

px_asset_fin_rec => l_asset_fin_rec,

px_asset_deprn_rec => l_asset_deprn_rec,

px_asset_dist_tbl => l_asset_dist_tbl,

px_inv_tbl => l_inv_tbl

);

DBMS_OUTPUT.put_line (l_return_status);

--dump messages

l_mesg_count := fnd_msg_pub.count_msg;

IF l_mesg_count > 0

THEN

l_mesg :=

CHR (10)

|| SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_first,

fnd_api.g_false

250

);

DBMS_OUTPUT.put_line (l_mesg);

FOR i IN 1 .. (l_mesg_count - 1)

LOOP

l_mesg :=

SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_next,

fnd_api.g_false),

250

);

DBMS_OUTPUT.put_line (l_mesg);

END LOOP;

fnd_msg_pub.delete_msg ();

END IF;

IF (l_return_status <> fnd_api.g_ret_sts_success)

THEN

LOG ('FAILURE');

ELSE

LOG ('SUCCESS');

LOG ('THID ' || TO_CHAR (l_trans_rec.transaction_header_id));

LOG ('STG_ID ' || p_stg_id);

LOG ('ASSET_ID ' || TO_CHAR (l_asset_hdr_rec.asset_id));

LOG ('ASSET_NUMBER ' || l_asset_desc_rec.asset_number);

UPDATE xx_fixed_assets_stg

SET status = 'Y'

WHERE stg_id = p_stg_id;

END IF;

COMMIT;

END xx_assets_src_line_procdure;

END;

Effected Tables:

fa_additions_b

fa_books

Note :(asset_number,tag_num,serial_num take sequences)

Any Queries Mail Me vivekexpertize@gmail.com

Oracle Apps Technical

Monday 19 March 2018

Mass Additions Interface

Step 4: Transfer From Custom table To Base Table

No comments:

Post a Comment

Calling Different Language Layout Based On Conditions :

site

Google Webmaster