CREATE OR REPLACE PACKAGE BODY XXCSC_AP_SUPPLIERS_CONV_PKG
AS
/* $Header: XXCSC_AP_SUPPLIERS_CONV_PKG.pkb version date time object_type module $
===========================================================================================
===========================================================================================
History:
Name Version Reason for Change Date
===========================================================================================
Madhu Dhare 1.0 Original 12-JUN-2014
===========================================================================================
*/
---------------------------------------------------------------------------------------------
-- Variable Declaration
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPPLIERS_MAPPING_PROC
-- DESCRIPTION : Supplier Mapping Activity in Staging Table
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_MAPPING_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS
x_query VARCHAR2(4000);
CURSOR CUR_MAP IS
SELECT table_name
,column_name
,legacy_code
,legacy_description
,target_code
,target_description
FROM XXHR.xxcsc_cmn_mapping_all mt
,XXHR.xxcsc_cmn_metadata_all st
WHERE mt.object_code = st.object_code
AND mt.object_dep_seq = st.object_dep_seq
AND st.object_name = x_object_name
AND mt.enable_flag = x_yes
AND st.enable_flag = x_yes;
BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;
--- Update the PROCESS FLAG AS M
XXCSC_AP_SUPP_STATUS_PROC (x_mapping_flag
,NULL -- x_status
);
FOR rec_map IN cur_map LOOP
x_query := 'UPDATE XXHR.' || rec_map.table_name ||' SET ' ||rec_map.column_name || ' = '''|| rec_map.target_code ||''' , status_flag = ''' ||x_mapped_flag || ''' WHERE EXISTS (SELECT 1 FROM XXHR.xxcsc_cmn_mapping_all b WHERE b.enable_flag = ''Y'' AND b.legacy_code = '|| rec_map.column_name || ' )' ;
EXECUTE IMMEDIATE (x_query);
COMMIT;
END LOOP;
--- Update the STATUS FLAG AS NM
XXCSC_AP_SUPP_STATUS_PROC (NULL
,x_notmapped_flag -- x_status
);
IF x_stage = x_mapping_stg THEN
XXCSC_AP_SUPP_ERROR_PROC (x_object_name
,x_stage
);
END IF;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR : '|| SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR DESCRIPTION - ' || SQLERRM);
ROLLBACK;
END XXCSC_AP_SUPP_MAPPING_PROC;
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPPLIERS_VALIDATION_PROC
-- DESCRIPTION : Supplier Validation Activity in Staging table
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_VALIDATION_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS
-- Local Variable Declaration
x_cnt NUMBER ;
x_query VARCHAR2(4000);
x_select VARCHAR2(2000);
x_str VARCHAR2(100);
x_arg1 VARCHAR2(100);
x_arg2 VARCHAR2(100);
x_arg3 VARCHAR2(100);
x_arg4 VARCHAR2(100);
x_pr_flag VARCHAR2(20);
x_st_flag VARCHAR2(20);
x_val NUMBER;
x_val1 NUMBER;
-- Ref Cursor Declaration
TYPE x_ref_cur IS REF CURSOR;
x_dyn_cur1 x_ref_cur;
x_dyn_cur2 x_ref_cur;
-- Cursor Declaration
CURSOR cur_tbl IS
SELECT DISTINCT
st.table_name
,st.key_column1 key_column1
,NVL(st.key_column2,'1') key_column2
,NVL(st.key_column3,'1') key_column3
,st.object_dep_seq
FROM XXHR.xxcsc_cmn_metadata_all st
WHERE st.object_name = x_object_name
AND st.enable_flag = x_yes
ORDER BY object_dep_seq;
CURSOR cur_valid IS
SELECT st.table_name
,st.key_column1
,NVL(st.key_column2,'1') key_column2
,NVL(st.key_column3, '1') key_column3
,cv1.valid_code
,cv1.valid_message
,cv1.sql_validation
,cv1.valid_type
FROM XXHR.xxcsc_cmn_validations_all cv1
,XXHR.xxcsc_cmn_metadata_all st
WHERE cv1.object_code = st.object_code
AND cv1.object_dep_seq = st.object_dep_seq
AND st.object_name = x_object_name
AND st.enable_flag = x_yes
AND cv1.enable_flag = x_yes
AND cv1.execution_seq <1000
ORDER BY st.object_dep_seq
,cv1.execution_seq;
CURSOR cur_valid1 IS
SELECT st.table_name
,st.key_column1
,NVL(st.key_column2,'1') key_column2
,NVL(st.key_column3, '1') key_column3
,cv1.valid_code
,cv1.valid_message
,cv1.sql_validation
,cv1.valid_type
FROM XXHR.xxcsc_cmn_validations_all cv1
,XXHR.xxcsc_cmn_metadata_all st
WHERE cv1.object_code = st.object_code
AND cv1.object_dep_seq = st.object_dep_seq
AND st.object_name = x_object_name
AND st.enable_flag = x_yes
AND cv1.enable_flag = x_yes
AND cv1.execution_seq >=1000
ORDER BY st.object_dep_seq
,cv1.execution_seq;
BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;
XXCSC_AP_SUPP_STATUS_PROC (x_validate_flag
,NULL -- x_status
);
FOR rec_tbl IN cur_tbl LOOP
x_select := 'SELECT RECORD_ID, '|| rec_tbl.key_column1 ||' ,'||rec_tbl.key_column2 || ' ,'|| rec_tbl.key_column3|| ' FROM XXHR.'|| TRIM(rec_tbl.table_name);
x_select := REPLACE(x_select,'1','DECODE(1,1,'' '')');
OPEN x_dyn_cur1 FOR x_select;
LOOP
FETCH x_dyn_cur1 INTO x_arg1,x_arg2,x_arg3,x_arg4 ;
EXIT WHEN x_dyn_cur1%NOTFOUND;
FOR rec_valid IN cur_valid LOOP
IF (rec_valid.table_name=rec_tbl.table_name) THEN
BEGIN
x_query := rec_valid.sql_validation;
x_str := ' AND MT.RECORD_ID='||x_arg1;
x_query := CONCAT(x_query,x_str);
EXECUTE IMMEDIATE (x_query) INTO x_cnt;
IF x_cnt >0 THEN
x_query := 'UPDATE XXHR.'|| TRIM(rec_valid.TABLE_NAME) ||' SET error_code = error_code ||'','|| rec_valid.VALID_CODE || ''', status_flag = ''' || rec_valid.valid_type || ''' WHERE record_id = '||x_arg1 ;
EXECUTE IMMEDIATE (x_query) ;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED: ' || SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED IN VALIDATION STAGE - ' || SQLERRM);
ROLLBACK;
END;
END IF;
END LOOP;
END LOOP;
END LOOP;
--Orphan Record Validate Start
FOR rec_tbl IN cur_tbl LOOP
x_select := 'SELECT RECORD_ID, '|| rec_tbl.key_column1 ||' ,'||rec_tbl.key_column2 || ' ,'|| rec_tbl.key_column3|| ' FROM XXHR.'|| TRIM(rec_tbl.table_name);
x_select := REPLACE(x_select,'1','DECODE(1,1,'' '')');
OPEN x_dyn_cur1 FOR x_select;
LOOP
FETCH x_dyn_cur1 INTO x_arg1,x_arg2,x_arg3,x_arg4 ;
EXIT WHEN x_dyn_cur1%NOTFOUND;
FOR rec_valid IN cur_valid1 LOOP
IF (rec_valid.table_name=rec_tbl.table_name) THEN
BEGIN
x_query := rec_valid.sql_validation;
x_str := ' AND MT.RECORD_ID='||x_arg1;
x_query := CONCAT(x_query,x_str);
EXECUTE IMMEDIATE (x_query) INTO x_cnt;
IF x_cnt >0 THEN
x_query := 'UPDATE XXHR.'|| TRIM(rec_valid.TABLE_NAME) ||' SET error_code = error_code ||'','|| rec_valid.VALID_CODE || ''', status_flag = ''' || rec_valid.valid_type || ''' WHERE record_id = '||x_arg1 ;
EXECUTE IMMEDIATE (x_query) ;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED: ' || SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED IN VALIDATION STAGE - ' || SQLERRM);
ROLLBACK;
END;
END IF;
END LOOP;
END LOOP;
END LOOP;
--Orphan Record Validate End
--- Update the STATUS FLAG AS V
XXCSC_AP_SUPP_STATUS_PROC (NULL
,x_validate_flag -- x_status
);
IF x_stage = x_validate_stg THEN
XXCSC_AP_SUPP_ERROR_PROC (x_object_name
,x_stage
);
END IF;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED: ' || SQLCODE);
XXCSC_AP_SUPP_PRINT_PROC('ERROR OCCURED IN VALIDATION STAGE - ' || SQLERRM);
ROLLBACK;
END XXCSC_AP_SUPP_VALIDATION_PROC;
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPPLIERS_TRANSFER_PROC
-- DESCRIPTION : Supplier Data Transfer from Staging Table into Interface Table
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_TRANSFER_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS
x_pr_flag VARCHAR2(20);
x_st_flag VARCHAR2(20);
BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;
FOR rec_hr IN cur_hr LOOP
UPDATE XXHR.xxcsc_ap_supplier_headers
SET process_flag = x_transfer_flag;
COMMIT;
END LOOP;
--- Update the PROCESS FLAG
FOR rec_st IN cur_st LOOP
UPDATE XXHR.xxcsc_ap_supplier_sites
SET process_flag = x_transfer_flag;
COMMIT;
END LOOP;
--- Update the PROCESS FLAG
FOR rec_cn IN cur_cn LOOP
UPDATE XXHR.xxcsc_ap_supplier_contacts
SET process_flag = x_transfer_flag;
COMMIT;
END LOOP;
-- Purge Supplier Interface Table
DELETE FROM APPS.ap_suppliers_int;
DELETE FROM APPS.ap_supplier_sites_int;
DELETE FROM APPS.ap_sup_site_contact_int;
COMMIT;
FOR rec_hdr IN cur_hdr (x_validate_flag) LOOP
BEGIN
-- SUPPLIER HEADER INSERT INTO INTERFACE TABLE
INSERT INTO APPS.ap_suppliers_int
(
vendor_interface_id
,last_update_date
,last_updated_by
,vendor_name
,vendor_name_alt
,segment1
,summary_flag
,enabled_flag
,last_update_login
,creation_date
,created_by
,employee_id
,vendor_type_lookup_code
,customer_num
,one_time_flag
,min_order_amount
,ship_to_location_id
,ship_to_location_code
,bill_to_location_id
,bill_to_location_code
,ship_via_lookup_code
,freight_terms_lookup_code
,fob_lookup_code
,terms_id
,terms_name
,set_of_books_id
,always_take_disc_flag
,pay_date_basis_lookup_code
,pay_group_lookup_code
,payment_priority
,invoice_currency_code
,payment_currency_code
,invoice_amount_limit
,hold_all_payments_flag
,hold_future_payments_flag
,hold_reason
,distribution_set_id
,distribution_set_name
,accts_pay_code_combination_id
,prepay_code_combination_id
,num_1099
,type_1099
,organization_type_lookup_code
,vat_code
,start_date_active
,end_date_active
,minority_group_lookup_code
,payment_method_lookup_code
,women_owned_flag
,small_business_flag
,standard_industry_class
,hold_flag
,purchasing_hold_reason
,hold_by
,hold_date
,terms_date_basis
,inspection_required_flag
,receipt_required_flag
,qty_rcv_tolerance
,qty_rcv_exception_code
,enforce_ship_to_location_code
,days_early_receipt_allowed
,days_late_receipt_allowed
,receipt_days_exception_code
,receiving_routing_id
,allow_substitute_receipts_flag
,allow_unordered_receipts_flag
,hold_unmatched_invoices_flag
,exclusive_payment_flag
,ap_tax_rounding_rule
,auto_tax_calc_flag
,auto_tax_calc_override
,amount_includes_tax_flag
,tax_verification_date
,name_control
,state_reportable_flag
,federal_reportable_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,request_id
,program_application_id
,program_id
,program_update_date
,vat_registration_num
,auto_calculate_interest_flag
,exclude_freight_from_discount
,tax_reporting_name
,allow_awt_flag
,awt_group_id
,awt_group_name
,global_attribute1
,global_attribute2
,global_attribute3
,global_attribute4
,global_attribute5
,global_attribute6
,global_attribute7
,global_attribute8
,global_attribute9
,global_attribute10
,global_attribute11
,global_attribute12
,global_attribute13
,global_attribute14
,global_attribute15
,global_attribute16
,global_attribute17
,global_attribute18
,global_attribute19
,global_attribute20
,global_attribute_category
,edi_transaction_handling
,edi_payment_method
,edi_payment_format
,edi_remittance_method
,edi_remittance_instruction
,bank_charge_bearer
,match_option
,future_dated_payment_ccid
,create_debit_memo_flag
,offset_tax_flag
,import_request_id
,status
,reject_code
,ece_tp_location_code
,iby_bank_charge_bearer
,bank_instruction1_code
,bank_instruction2_code
,bank_instruction_details
,payment_reason_code
,payment_reason_comments
,payment_text_message1
,payment_method_code
)
VALUES
(
rec_hdr.record_id
,SYSDATE --last_update_date
,APPS.FND_GLOBAL.USER_ID --last_updated_by
,rec_hdr.vendor_name
,rec_hdr.vendor_name_alt
,rec_hdr.vendor_number
,rec_hdr.summary_flag
,rec_hdr.enabled_flag
,APPS.FND_GLOBAL.USER_ID --last_update_login
,SYSDATE --creation_date
,APPS.FND_GLOBAL.USER_ID --created_by
,rec_hdr.employee_id
,rec_hdr.vendor_type_lookup_code
,rec_hdr.customer_num
,rec_hdr.one_time_flag
,rec_hdr.min_order_amount
,rec_hdr.ship_to_location_id
,rec_hdr.ship_to_location_code
,rec_hdr.bill_to_location_id
,rec_hdr.bill_to_location_code
,rec_hdr.ship_via_lookup_code
,rec_hdr.freight_terms_lookup_code
,rec_hdr.fob_lookup_code
,rec_hdr.terms_id
,rec_hdr.terms_name
,rec_hdr.set_of_books_id
,rec_hdr.always_take_disc_flag
,rec_hdr.pay_date_basis_lookup_code
,rec_hdr.pay_group_lookup_code
,rec_hdr.payment_priority
,rec_hdr.invoice_currency_code
,rec_hdr.payment_currency_code
,rec_hdr.invoice_amount_limit
,rec_hdr.hold_all_payments_flag
,rec_hdr.hold_future_payments_flag
,rec_hdr.hold_reason
,rec_hdr.distribution_set_id
,rec_hdr.distribution_set_name
,rec_hdr.accts_pay_code_combination_id
,rec_hdr.prepay_code_combination_id
,rec_hdr.num_1099
,rec_hdr.type_1099
,rec_hdr.organization_type_lookup_code
,rec_hdr.vat_code
,rec_hdr.start_date_active
,rec_hdr.end_date_active
,rec_hdr.minority_group_lookup_code
,rec_hdr.payment_method_lookup_code
,rec_hdr.women_owned_flag
,rec_hdr.small_business_flag
,rec_hdr.standard_industry_class
,rec_hdr.hold_flag
,rec_hdr.purchasing_hold_reason
,rec_hdr.hold_by
,rec_hdr.hold_date
,rec_hdr.terms_date_basis
,rec_hdr.inspection_required_flag
,rec_hdr.receipt_required_flag
,rec_hdr.qty_rcv_tolerance
,rec_hdr.qty_rcv_exception_code
,rec_hdr.enforce_ship_to_location_code
,rec_hdr.days_early_receipt_allowed
,rec_hdr.days_late_receipt_allowed
,rec_hdr.receipt_days_exception_code
,rec_hdr.receiving_routing_id
,rec_hdr.allow_substitute_receipts_flag
,rec_hdr.allow_unordered_receipts_flag
,rec_hdr.hold_unmatched_invoices_flag
,rec_hdr.exclusive_payment_flag
,rec_hdr.ap_tax_rounding_rule
,rec_hdr.auto_tax_calc_flag
,rec_hdr.auto_tax_calc_override
,rec_hdr.amount_includes_tax_flag
,rec_hdr.tax_verification_date
,rec_hdr.name_control
,rec_hdr.state_reportable_flag
,rec_hdr.federal_reportable_flag
,rec_hdr.attribute_category
,rec_hdr.attribute1
,rec_hdr.attribute2
,rec_hdr.attribute3
,rec_hdr.attribute4
,rec_hdr.attribute5
,rec_hdr.attribute6
,rec_hdr.attribute7
,rec_hdr.attribute8
,rec_hdr.attribute9
,rec_hdr.attribute10
,rec_hdr.attribute11
,rec_hdr.attribute12
,rec_hdr.attribute13
,rec_hdr.attribute14
,rec_hdr.attribute15
,APPS.FND_GLOBAL.CONC_REQUEST_ID -- request_id
,NULL -- program_application_id
,NULL -- program_id
,NULL -- program_update_date
,rec_hdr.vat_registration_num
,rec_hdr.auto_calculate_interest_flag
,rec_hdr.exclude_freight_from_discount
,rec_hdr.tax_reporting_name
,rec_hdr.allow_awt_flag
,rec_hdr.awt_group_id
,rec_hdr.awt_group_name
,rec_hdr.global_attribute1
,rec_hdr.global_attribute2
,rec_hdr.global_attribute3
,rec_hdr.global_attribute4
,rec_hdr.global_attribute5
,rec_hdr.global_attribute6
,rec_hdr.global_attribute7
,rec_hdr.global_attribute8
,rec_hdr.global_attribute9
,rec_hdr.global_attribute10
,rec_hdr.global_attribute11
,rec_hdr.global_attribute12
,rec_hdr.global_attribute13
,rec_hdr.global_attribute14
,rec_hdr.global_attribute15
,rec_hdr.global_attribute16
,rec_hdr.global_attribute17
,rec_hdr.global_attribute18
,rec_hdr.global_attribute19
,rec_hdr.global_attribute20
,rec_hdr.global_attribute_category
,rec_hdr.edi_transaction_handling
,rec_hdr.edi_payment_method
,rec_hdr.edi_payment_format
,rec_hdr.edi_remittance_method
,rec_hdr.edi_remittance_instruction
,rec_hdr.bank_charge_bearer
,rec_hdr.match_option
,rec_hdr.future_dated_payment_ccid
,rec_hdr.create_debit_memo_flag
,rec_hdr.offset_tax_flag
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL);
UPDATE XXHR.xxcsc_ap_supplier_headers hdr
SET hdr.status_flag = x_transfered_flag
WHERE hdr.vendor_number = rec_hdr.vendor_number
AND hdr.record_id = rec_hdr.record_id;
FOR rec_sit IN cur_sit (x_validate_flag,rec_hdr.vendor_number) LOOP
BEGIN
INSERT INTO APPS.ap_supplier_sites_int
(
vendor_interface_id
,last_update_date
,last_updated_by
,vendor_id
,vendor_site_code
,vendor_site_code_alt
,last_update_login
,creation_date
,created_by
,purchasing_site_flag
,rfq_only_site_flag
,pay_site_flag
,attention_ar_flag
,address_line1
,address_lines_alt
,address_line2
,address_line3
,city
,state
,zip
,province
,country
,area_code
,phone
,customer_num
,ship_to_location_id
,ship_to_location_code
,bill_to_location_id
,bill_to_location_code
,ship_via_lookup_code
,freight_terms_lookup_code
,fob_lookup_code
,inactive_date
,fax
,fax_area_code
,telex
,payment_method_lookup_code
,terms_date_basis
,vat_code
,distribution_set_id
,distribution_set_name
,accts_pay_code_combination_id
,prepay_code_combination_id
,pay_group_lookup_code
,payment_priority
,terms_id
,terms_name
,invoice_amount_limit
,pay_date_basis_lookup_code
,always_take_disc_flag
,invoice_currency_code
,payment_currency_code
,hold_all_payments_flag
,hold_future_payments_flag
,hold_reason
,hold_unmatched_invoices_flag
,ap_tax_rounding_rule
,auto_tax_calc_flag
,auto_tax_calc_override
,amount_includes_tax_flag
,exclusive_payment_flag
,tax_reporting_site_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,request_id
,program_application_id
,program_id
,program_update_date
,exclude_freight_from_discount
,vat_registration_num
,org_id
,operating_unit_name
,address_line4
,county
,address_style
,language
,allow_awt_flag
,awt_group_id
,awt_group_name
,global_attribute1
,global_attribute2
,global_attribute3
,global_attribute4
,global_attribute5
,global_attribute6
,global_attribute7
,global_attribute8
,global_attribute9
,global_attribute10
,global_attribute11
,global_attribute12
,global_attribute13
,global_attribute14
,global_attribute15
,global_attribute16
,global_attribute17
,global_attribute18
,global_attribute19
,global_attribute20
,global_attribute_category
,edi_transaction_handling
,edi_id_number
,edi_payment_method
,edi_payment_format
,edi_remittance_method
,bank_charge_bearer
,edi_remittance_instruction
,pay_on_code
,default_pay_site_id
,pay_on_receipt_summary_code
,tp_header_id
,ece_tp_location_code
,pcard_site_flag
,match_option
,country_of_origin_code
,future_dated_payment_ccid
,create_debit_memo_flag
,offset_tax_flag
,supplier_notif_method
,email_address
,remittance_email
,primary_pay_site_flag
,import_request_id
,status
,reject_code
,shipping_control
,duns_number
,tolerance_id
,tolerance_name
,iby_bank_charge_bearer
,bank_instruction1_code
,bank_instruction2_code
,bank_instruction_details
,payment_reason_code
,payment_reason_comments
,delivery_channel_code
,payment_format_code
,settlement_priority
,payment_text_message1
,payment_text_message2
,payment_text_message3
,vendor_site_interface_id
,payment_method_code
,retainage_rate
,gapless_inv_num_flag
,selling_company_identifier
)
VALUES
(
rec_hdr.record_id
,SYSDATE -- last_update_date
,APPS.FND_GLOBAL.USER_ID --last_updated_by
,NULL -- rec_sit.vendor_id
,rec_sit.vendor_site_code
,rec_sit.vendor_site_code_alt
,APPS.FND_GLOBAL.USER_ID -- last_update_login
,SYSDATE -- creation_date
,APPS.FND_GLOBAL.USER_ID -- created_by
,rec_sit.purchasing_site_flag
,rec_sit.rfq_only_site_flag
,rec_sit.pay_site_flag
,rec_sit.attention_ar_flag
,rec_sit.address_line1
,rec_sit.address_lines_alt
,rec_sit.address_line2
,rec_sit.address_line3
,rec_sit.city
,rec_sit.state
,rec_sit.zip
,rec_sit.province
,rec_sit.country
,rec_sit.area_code
,rec_sit.phone
,rec_sit.customer_num
,rec_sit.ship_to_location_id
,rec_sit.ship_to_location_code
,rec_sit.bill_to_location_id
,rec_sit.bill_to_location_code
,rec_sit.ship_via_lookup_code
,rec_sit.freight_terms_lookup_code
,rec_sit.fob_lookup_code
,rec_sit.inactive_date
,rec_sit.fax
,rec_sit.fax_area_code
,rec_sit.telex
,rec_sit.payment_method_lookup_code
,rec_sit.terms_date_basis
,rec_sit.vat_code
,rec_sit.distribution_set_id
,rec_sit.distribution_set_name
,rec_sit.accts_pay_code_combination_id
,rec_sit.prepay_code_combination_id
,rec_sit.pay_group_lookup_code
,rec_sit.payment_priority
,rec_sit.terms_id
,rec_sit.terms_name
,rec_sit.invoice_amount_limit
,rec_sit.pay_date_basis_lookup_code
,rec_sit.always_take_disc_flag
,rec_sit.invoice_currency_code
,rec_sit.payment_currency_code
,rec_sit.hold_all_payments_flag
,rec_sit.hold_future_payments_flag
,rec_sit.hold_reason
,rec_sit.hold_unmatched_invoices_flag
,rec_sit.ap_tax_rounding_rule
,rec_sit.auto_tax_calc_flag
,rec_sit.auto_tax_calc_override
,rec_sit.amount_includes_tax_flag
,rec_sit.exclusive_payment_flag
,rec_sit.tax_reporting_site_flag
,rec_sit.attribute_category
,rec_sit.attribute1
,rec_sit.attribute2
,rec_sit.attribute3
,rec_sit.attribute4
,rec_sit.attribute5
,rec_sit.attribute6
,rec_sit.attribute7
,rec_sit.attribute8
,rec_sit.attribute9
,rec_sit.attribute10
,rec_sit.attribute11
,rec_sit.attribute12
,rec_sit.attribute13
,rec_sit.attribute14
,rec_sit.attribute15
,rec_sit.request_id
,NULL -- program_application_id
,NULL -- program_id
,NULL -- program_update_date
,rec_sit.exclude_freight_from_discount
,rec_sit.vat_registration_num
,rec_sit.org_id
,rec_sit.org_name
,rec_sit.address_line4
,rec_sit.county
,rec_sit.address_style
,rec_sit.language
,rec_sit.allow_awt_flag
,rec_sit.awt_group_id
,rec_sit.awt_group_name
,rec_sit.global_attribute1
,rec_sit.global_attribute2
,rec_sit.global_attribute3
,rec_sit.global_attribute4
,rec_sit.global_attribute5
,rec_sit.global_attribute6
,rec_sit.global_attribute7
,rec_sit.global_attribute8
,rec_sit.global_attribute9
,rec_sit.global_attribute10
,rec_sit.global_attribute11
,rec_sit.global_attribute12
,rec_sit.global_attribute13
,rec_sit.global_attribute14
,rec_sit.global_attribute15
,rec_sit.global_attribute16
,rec_sit.global_attribute17
,rec_sit.global_attribute18
,rec_sit.global_attribute19
,rec_sit.global_attribute20
,rec_sit.global_attribute_category
,rec_sit.edi_transaction_handling
,rec_sit.edi_id_number
,rec_sit.edi_payment_method
,rec_sit.edi_payment_format
,rec_sit.edi_remittance_method
,rec_sit.bank_charge_bearer
,rec_sit.edi_remittance_instruction
,rec_sit.pay_on_code
,rec_sit.default_pay_site_id
,rec_sit.pay_on_receipt_summary_code
,rec_sit.tp_header_id
,rec_sit.ece_tp_location_code
,rec_sit.pcard_site_flag
,rec_sit.match_option
,rec_sit.country_of_origin_code
,rec_sit.future_dated_payment_ccid
,rec_sit.create_debit_memo_flag
,rec_sit.offset_tax_flag
,rec_sit.supplier_notif_method
,rec_sit.email_address
,rec_sit.remittance_email
,rec_sit.primary_pay_site_flag
,NULL -- import_request_id
,NULL -- status
,NULL -- reject_code
,rec_sit.shipping_control
,rec_sit.duns_number
,rec_sit.tolerance_id
,rec_sit.tolerance_name
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,rec_sit.record_id
,NULL
,rec_sit.retainage_rate
,rec_sit.gapless_inv_num_flag
,rec_sit.selling_company_identifier
);
UPDATE XXHR.xxcsc_ap_supplier_sites sit
SET sit.status_flag = x_transfered_flag
WHERE sit.vendor_number = rec_hdr.vendor_number
AND sit.vendor_site_code = rec_sit.vendor_site_code
AND sit.record_id = rec_sit.record_id;
FOR rec_con IN cur_con (x_validate_flag,rec_hdr.vendor_number,rec_sit.vendor_site_code) LOOP
BEGIN
INSERT INTO APPS.ap_sup_site_contact_int
(
last_update_date
,last_updated_by
,vendor_site_id
,vendor_site_code
,org_id
,operating_unit_name
,last_update_login
,creation_date
,created_by
,inactive_date
,first_name
,middle_name
,last_name
,prefix
,title
,mail_stop
,area_code
,phone
,program_application_id
,program_id
,program_update_date
,request_id
,contact_name_alt
,first_name_alt
,last_name_alt
,department
,import_request_id
,status
,reject_code
,email_address
,url
,alt_area_code
,alt_phone
,fax_area_code
,fax
,vendor_interface_id
,vendor_id
,vendor_contact_interface_id
)
VALUES
(
SYSDATE -- last_update_date
,APPS.FND_GLOBAL.USER_ID -- last_updated_by
,rec_sit.vendor_site_id
,rec_con.vendor_site_code
,rec_con.org_id -- org_id
,rec_con.org_name -- operating_unit_name
,APPS.FND_GLOBAL.USER_ID -- last_update_login
,SYSDATE -- creation_date
,APPS.FND_GLOBAL.USER_ID -- created_by
,rec_con.inactive_date
,rec_con.first_name
,rec_con.middle_name
,rec_con.last_name
,rec_con.prefix
,rec_con.title
,rec_con.mail_stop
,rec_con.area_code
,rec_con.phone
,NULL -- program_application_id
,NULL -- program_id
,NULL -- program_update_date
,rec_con.request_id
,rec_con.contact_name_alt
,rec_con.first_name_alt
,rec_con.last_name_alt
,rec_con.department
,NULL -- import_request_id
,NULL -- status
,NULL -- reject_code
,rec_con.email_address
,rec_con.url
,rec_con.alt_area_code
,rec_con.alt_phone
,rec_con.fax_area_code
,rec_con.fax
,rec_hdr.record_id
,NULL -- rec_hdr.record_id
,rec_con.record_id
);
UPDATE XXHR.xxcsc_ap_supplier_contacts con
SET con.status_flag = x_transfered_flag
WHERE con.vendor_number = rec_hdr.vendor_number
AND con.vendor_site_code = rec_sit.vendor_site_code
AND con.record_id = rec_con.record_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC ('Contact Error: Error occured while SUPPLIER CONATCS data transfer from Staging table into Interface Table ');
ROLLBACK;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC ('Site Error: Error occured while SUPPLIER SITES data transfer from Staging table into Interface Table ');
ROLLBACK;
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC ('Header Error: Error occured while SUPPLIER HEADERS data transfer from Staging table into Interface Table ');
ROLLBACK;
END;
END LOOP;
XXCSC_AP_SUPP_STATUS_PROC (NULL
,x_nottransfer_flag -- x_status
);
IF x_stage IN (x_transfer_stg, x_all_stg) THEN
XXCSC_AP_SUPP_ERROR_PROC (x_object_name
,x_stage
);
END IF;
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR OCCURED : '|| SQLCODE );
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR DESCRIPTION - ' || SQLERRM );
ROLLBACK;
END XXCSC_AP_SUPP_TRANSFER_PROC;
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_ERROR_PROC
-- DESCRIPTION : Error Report Generation
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_ERROR_PROC (x_object_name IN VARCHAR2
,x_stage IN VARCHAR2
)
IS
-- Variable Declaration
x_title VARCHAR2(100);
x_status VARCHAR2(100);
x_req_id NUMBER;
x_req_id_1 NUMBER;
x_start_dt VARCHAR2(100);
x_end_dt VARCHAR2(100);
x_exec_time VARCHAR2(100);
x_conc_prog_name VARCHAR2(100);
x_param1 VARCHAR2(100);
x_param2 VARCHAR2(100);
x_param3 VARCHAR2(100);
x_sqlqry VARCHAR2(2000);
x_sqlqry1 VARCHAR2(2000);
x_err_desc VARCHAR2(2000);
x_cnt NUMBER := 0;
x_key2 NUMBER;
x_key3 NUMBER;
x_arg1 VARCHAR2(300);
x_arg2 VARCHAR2(300);
x_arg3 VARCHAR2(300);
x_arg4 VARCHAR2(300);
x_arg5 VARCHAR2(300);
x_flag VARCHAR2(1):= 'Y';
x_str VARCHAR2(200):=' ';
-- Ref Cursor Delaration
TYPE x_ref_cur IS ref cursor;
x_dyn_cur x_ref_cur;
x_dyn_cur1 x_ref_cur;
CURSOR cur_tbl IS
SELECT st.table_name
,st.table_description
FROM XXHR.xxcsc_cmn_metadata_all st
WHERE st.object_name = TRIM(x_object_name)
AND st.enable_flag = x_yes
ORDER BY st.object_dep_seq;
CURSOR cur_det IS
SELECT st.table_name
,st.table_description
,st.key_column1
,NVL(st.key_column2,1) AS key_column2
,NVL(st.key_column3,1) AS key_column3
FROM XXHR.xxcsc_cmn_metadata_all st
WHERE st.object_name = TRIM(x_object_name)
AND st.enable_flag = x_yes
ORDER BY st.object_dep_seq;
BEGIN
XXCSC_AP_SUPPLIERS_CONV_PKG.x_object := x_object_name;
SELECT MAX(REQUEST_ID)
INTO x_req_id_1
FROM APPS.FND_CONCURRENT_REQUESTS CR
,APPS.FND_CONCURRENT_PROGRAMS_TL B
WHERE CR.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND ARGUMENT1 = x_object_name;
SELECT
' CONVERSION SUMMARY DETAILS' AS Title
,DECODE(mt.status_code,'C','COMPLETED','E','ERROR') AS status_code
,mt.request_id AS request_id
,TO_CHAR(mt.actual_start_date,'DD-MON-YYYY HH:MI:SS') AS actual_start_date
,TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') AS actual_completion_date
,FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)|| ' Hours ' ||
FLOOR((((SYSDATE - mt.actual_start_date)*24*60*60) -
FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)*3600)/60)|| ' Minutes ' ||
ROUND((((SYSDATE - mt.actual_start_date)*24*60*60) -
FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)*3600 -
(FLOOR((((SYSDATE - mt.actual_start_date)*24*60*60) -
FLOOR(((SYSDATE - mt.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) || ' Secs '
AS Duration
,b.user_concurrent_program_name AS user_concurrent_program_name
,mt.argument1 AS argument1
,mt.argument2 AS argument2
,mt.argument3 AS argument3
INTO x_title
,x_status
,x_req_id
,x_start_dt
,x_end_dt
,x_exec_time
,x_conc_prog_name
,x_param1
,x_param2
,x_param3
FROM APPS.fnd_concurrent_requests mt
,APPS.fnd_concurrent_programs a
,APPS.fnd_concurrent_programs_TL b
WHERE a.concurrent_program_id = b.concurrent_program_id
AND mt.concurrent_program_id = a.concurrent_program_id
AND mt.concurrent_program_id = b.concurrent_program_id
AND mt.argument1 = TRIM(x_object_name)
AND status_code != 'E'
AND ROWNUM < 2
AND mt. request_id = x_req_id_1
ORDER BY request_date DESC;
-- Print Heading
XXCSC_AP_SUPP_PRINT_PROC ( ' ');
XXCSC_AP_SUPP_PRINT_PROC ( ' ');
XXCSC_AP_SUPP_PRINT_PROC ('**************************************************************************');
XXCSC_AP_SUPP_PRINT_PROC ( UPPER(x_object_name) ||' CONVERSION SUMMARY AND ERROR REPORT ');
XXCSC_AP_SUPP_PRINT_PROC ('**************************************************************************');
XXCSC_AP_SUPP_PRINT_PROC ( ' ');
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Object Name',30,' ') || ':'||x_object_name);
--S XXCSC_AP_SUPP_PRINT_PROC (RPAD('Load Status',30,' ') || ':'||x_status);
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Concurrent Request ID',30,' ') || ':'||x_req_id );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Program Start Date',30,' ') || ':'||x_start_dt );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Program End Date',30,' ') || ':'||x_end_dt );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Execution time',30,' ') || ':'||x_exec_time );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Concurrent Program Name',30,' ') || ':'||x_conc_prog_name );
XXCSC_AP_SUPP_PRINT_PROC ('Parameters:' );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Object Name',30,' ') || ':'||x_param1 );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Stage',30,' ') || ':'||x_param2 );
XXCSC_AP_SUPP_PRINT_PROC (RPAD('Forcefully Submit',30,' ') || ':'||x_param3 );
XXCSC_AP_SUPP_PRINT_PROC (' ' );
XXCSC_AP_SUPP_PRINT_PROC ('**************************************************************************' );
XXCSC_AP_SUPP_PRINT_PROC (' ' );
-- Summary Report
FOR rec_tbl IN cur_tbl LOOP
XXCSC_AP_SUPP_PRINT_PROC(' ' );
XXCSC_AP_SUPP_PRINT_PROC( RPAD(rec_tbl.table_description ||' Conversion Summary Details',50,' ') );
XXCSC_AP_SUPP_PRINT_PROC( '**********************************************' );
XXCSC_AP_SUPP_PRINT_PROC(' ' );
x_sqlqry := 'SELECT COUNT(1) FROM XXHR.'||rec_tbl.table_name;
EXECUTE IMMEDIATE x_sqlqry INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
IF x_stage IN (x_mapping_stg, x_all_stg) THEN
x_sqlqry1 := x_sqlqry || ' WHERE status_flag = ''' || x_mapped_flag||'''';
EXECUTE IMMEDIATE x_sqlqry1 INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records Mapped in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
END IF;
x_sqlqry1 := ' ';
IF x_stage IN (x_validate_stg, x_all_stg) THEN
x_sqlqry1 := x_sqlqry || ' WHERE (status_flag IN ('''|| x_error_flag ||''' , '''|| x_warning_flag || ''') OR error_code IS NOT NULL)';
EXECUTE IMMEDIATE x_sqlqry1 INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records Error in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
END IF;
x_sqlqry1 := ' ';
IF x_stage IN (x_transfer_stg, x_all_stg) THEN
x_sqlqry1 := x_sqlqry || ' WHERE status_flag = '''|| x_transfered_flag||'''';
EXECUTE IMMEDIATE x_sqlqry1 INTO x_cnt;
XXCSC_AP_SUPP_PRINT_PROC( RPAD('Total Records Transfered in '|| rec_tbl.table_description ,50,' ') || ': ' ||x_cnt );
END IF;
END LOOP;
IF x_stage IN (x_all_stg, x_validate_stg) THEN
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC( 'SUMMARY ERROR DETAILS' );
XXCSC_AP_SUPP_PRINT_PROC( '*********************' );
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
FOR REC_DET IN CUR_DET LOOP
x_sqlqry1 := 'SELECT count(mt.record_id),st.valid_code,st.valid_message
FROM XXHR.' ||REC_DET.TABLE_NAME || ' MT
,XXHR.xxcsc_cmn_validations_all st
WHERE INSTR(mt.error_code,st.valid_code)>0
AND mt.error_code IS NOT NULL
GROUP BY st.valid_code,st.valid_message
ORDER BY COUNT(valid_code) DESC';
XXCSC_AP_SUPP_PRINT_PROC(' ');
XXCSC_AP_SUPP_PRINT_PROC('Summary of Error for '||REC_DET.TABLE_DESCRIPTION );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',50,'*') );
x_flag:='Y';
OPEN x_dyn_cur1 for x_sqlqry1;
LOOP
FETCH x_dyn_cur1 INTO x_arg1,x_arg2,x_arg3 ;
EXIT WHEN x_dyn_cur1%NOTFOUND;
IF (x_flag='Y') THEN
x_flag:='N';
XXCSC_AP_SUPP_PRINT_PROC(RPAD('COUNT',10,' ')||RPAD('ERROR_CODE',30,' ')||RPAD('ERROR_MESSAGE',100,' ') );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('-',10,'-')||RPAD('-',30,'-')||RPAD('-',100,'-') );
END IF;
XXCSC_AP_SUPP_PRINT_PROC(RPAD(x_arg1,10,' ')||RPAD(x_arg2,30,' ')||RPAD(x_arg3,100,' ') );
END LOOP;
CLOSE x_dyn_cur1;
END LOOP;
-- Detail Report
XXCSC_AP_SUPP_PRINT_PROC (' ' );
XXCSC_AP_SUPP_PRINT_PROC (' ' );
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR WITH DETAILS' );
XXCSC_AP_SUPP_PRINT_PROC( '******************' );
XXCSC_AP_SUPP_PRINT_PROC (' ' );
FOR REC_DET IN CUR_DET LOOP
x_sqlqry1 := 'SELECT MT.'||rec_det.key_column1 ||'
,' ||rec_det.key_column2 ||'
,' ||rec_det.key_column3 ||'
,st.valid_code
,' || 'st.valid_message
FROM XXHR.' ||rec_det.table_name || ' mt
,XXHR.xxcsc_cmn_validations_all st
WHERE INSTR(mt.error_code,st.valid_code)>0
AND mt.error_code IS NOT NULL';
IF TRIM(rec_det.key_column2) = '1' THEN
x_key2 := 0;
ELSE
x_key2 := 20;
END IF;
IF TRIM(rec_det.key_column3) = '1' THEN
x_key3 := 0;
ELSE
x_key3 := 20;
END IF;
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('Error Details For '||rec_det.table_description,50,' ') );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',50,'*') );
XXCSC_AP_SUPP_PRINT_PROC( ' ' );
XXCSC_AP_SUPP_PRINT_PROC(RPAD(REPLACE(TO_CHAR(rec_det.key_column1),'1',' '),20,' ')||RPAD(REPLACE(TO_CHAR(rec_det.key_column2),'1',' '),x_key2,' ')||RPAD(REPLACE(TO_CHAR(rec_det.key_column3),'1',' '),x_key3,' ')||RPAD('VALIDATION CODE ',25,' ')||RPAD('VALIDATION DESCRIPTION',75,' ') );
XXCSC_AP_SUPP_PRINT_PROC(RPAD('-',20,'-')||RPAD('-',20,'-')||RPAD('-',20,'-')||RPAD('-',25,'-')||RPAD('-',75,'-') );
x_sqlqry1 := REPLACE(x_sqlqry1,'1','DECODE(1,1,NULL)' );
x_flag:= 'Y';
OPEN x_dyn_cur FOR x_sqlqry1;
LOOP
FETCH x_dyn_cur INTO x_arg1,x_arg2,x_arg3,x_arg4,x_arg5 ;
EXIT WHEN x_dyn_cur%NOTFOUND;
XXCSC_AP_SUPP_PRINT_PROC(RPAD(x_arg1,20,' ')||RPAD(x_arg2,x_key2,' ')||RPAD(x_arg3,x_key3,' ')||RPAD(x_arg4,25,' ')||RPAD(x_arg5,75,' ') );
END LOOP;
CLOSE x_dyn_cur;
END LOOP;
END IF;
XXCSC_AP_SUPP_PRINT_PROC (' ');
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',140,'*') );
XXCSC_AP_SUPP_PRINT_PROC (RPAD(' ',66,' ')||'END OF REPORT' || RPAD(' ',66,' '));
XXCSC_AP_SUPP_PRINT_PROC(RPAD('*',140,'*') );
EXCEPTION
WHEN OTHERS THEN
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR CODE : ' || SQLCODE );
XXCSC_AP_SUPP_PRINT_PROC( 'ERROR DESCRIPTION : ' || SQLERRM );
ROLLBACK;
END XXCSC_AP_SUPP_ERROR_PROC;
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_PRINT_PROC
-- DESCRIPTION : Error Report Design
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_PRINT_PROC (x_print IN VARCHAR2
)
IS
file_handle UTL_FILE.FILE_TYPE;
x_directory VARCHAR2(50) := '/usr/tmp';
x_reid NUMBER ;
x_file_name VARCHAR2(100);
x_flag VARCHAR2(1);
x_accept VARCHAR2(1);
BEGIN
x_file_name := 'XXCSC_AP_SUPPLIER_ERROR.dat';
IF TRIM(XXCSC_AP_SUPPLIERS_CONV_PKG.x_flag) = 'Y' THEN
x_accept := 'W';
XXCSC_AP_SUPPLIERS_CONV_PKG.x_flag := 'N';
ELSE
x_accept := 'A' ;
END IF;
file_handle := UTL_FILE.FOPEN(x_directory, x_file_name ,x_accept);
UTL_FILE.PUT_LINE(file_handle,x_print);
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN Utl_File.INVALID_OPERATION THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file could not be opened or operated on as requested. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file could not be opened or operated on as requested. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.INVALID_PATH THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'File location or filename was invalid.');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'File location or filename was invalid.');
utl_file.fclose_all;
ROLLBACK;
WHEN Utl_File.INVALID_MODE THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The open_mode parameter in FOPEN was invalid. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The open_mode parameter in FOPEN was invalid. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.INVALID_FILEHANDLE THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file handle was invalid. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'The file handle was invalid. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.READ_ERROR THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the read operation.');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the read operation.');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.WRITE_ERROR THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the write operation. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An operating system error occurred during the write operation. ');
Utl_File.FClose_All;
ROLLBACK;
WHEN Utl_File.INTERNAL_ERROR THEN
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An unspecified error in PL/SQL. ');
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'An unspecified error in PL/SQL. ');
utl_file.fclose_all;
ROLLBACK;
WHEN OTHERS THEN
-- fnd_file.new_line(APPS.FND_FILE.LOG,1);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'error in generating output file '||'$'||x_directory||'$'||'/'||'$'||x_file_name);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,SQLERRM);
APPS.FND_FILE.PUT_LINE(APPS.FND_FILE.LOG,'*----*----**----*----**----*----**----*----**----*');
END XXCSC_AP_SUPP_PRINT_PROC;
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_STATUS_PROC
-- DESCRIPTION : Update the PROCESS_FLAG and STATUS_FLAG
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_STATUS_PROC (x_process IN VARCHAR2
,x_status IN VARCHAR2
)
IS
CURSOR cur_hr IS
SELECT hdr.*
FROM XXHR.xxcsc_ap_supplier_headers hdr;
CURSOR cur_st IS
SELECT sit.*
FROM XXHR.xxcsc_ap_supplier_sites sit;
CURSOR cur_cn IS
SELECT con.*
FROM XXHR.xxcsc_ap_supplier_contacts con;
BEGIN
---------------------------------------------------------------------------------------------
-- UPDATE PROCESS FLAG
---------------------------------------------------------------------------------------------
IF NVL(x_process,'$') != '$' THEN
--- Update the PROCESS FLAG
FOR rec_hr IN cur_hr LOOP
UPDATE XXHR.xxcsc_ap_supplier_headers
SET process_flag = x_process
,status_flag = NULL
,error_code = NULL;
COMMIT;
END LOOP;
--- Update the PROCESS FLAG
FOR rec_st IN cur_st LOOP
UPDATE XXHR.xxcsc_ap_supplier_sites
SET process_flag = x_process
,status_flag = NULL
,error_code = NULL;
COMMIT;
END LOOP;
--- Update the PROCESS FLAG
FOR rec_cn IN cur_cn LOOP
UPDATE XXHR.xxcsc_ap_supplier_contacts
SET process_flag = x_process
,status_flag = NULL
,error_code = NULL;
COMMIT;
END LOOP;
END IF;
---------------------------------------------------------------------------------------------
-- UPDATE STATUS FLAG
---------------------------------------------------------------------------------------------
IF NVL(x_status,'$') != '$' THEN
--- Update the STATUS FLAG
FOR rec_hr IN cur_hr LOOP
UPDATE XXHR.xxcsc_ap_supplier_headers
SET status_flag = x_status
WHERE status_flag IS NULL;
COMMIT;
END LOOP;
--- Update the STATUS FLAG
FOR rec_st IN cur_st LOOP
UPDATE XXHR.xxcsc_ap_supplier_sites
SET status_flag = x_status
WHERE status_flag IS NULL;
COMMIT;
END LOOP;
--- Update the STATUS FLAG
FOR rec_cn IN cur_cn LOOP
UPDATE XXHR.xxcsc_ap_supplier_contacts
SET status_flag = x_status
WHERE status_flag IS NULL;
COMMIT;
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'ERROR CODE : ' || SQLCODE );
DBMS_OUTPUT.PUT_LINE( 'ERROR DESCRIPTION : ' || SQLERRM );
ROLLBACK;
END XXCSC_AP_SUPP_STATUS_PROC;
---------------------------------------------------------------------------------------------
-- PROCEDURE NAME : XXCSC_AP_SUPP_RECON_PROC
-- DESCRIPTION : RECON Report Design
---------------------------------------------------------------------------------------------
PROCEDURE XXCSC_AP_SUPP_RECON_PROC (x_object IN VARCHAR2
)
IS
file_handle UTL_FILE.FILE_TYPE;
x_directory VARCHAR2(50) := '/usr/tmp';
x_file_name VARCHAR2(100) := 'XXCSC_AP_SUPPLIER_ERROR.dat';
CURSOR cur_recon IS
SELECT 'SUPPLIER HEADERS RECONCILIATION REPORT' data FROM DUAL
UNION ALL
SELECT '*************************************' FROM DUAL
UNION ALL
SELECT RPAD('Total Record Count in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_headers
UNION ALL
SELECT RPAD('Total Record Errored in staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_headers WHERE error_code IS NOT NULL
UNION ALL
SELECT RPAD('Total Record transfered from staging into Interface Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_headers WHERE status_flag = 'T' AND process_flag = 'T'
UNION ALL
SELECT RPAD('Total Record in Interface Table',80,' ') || COUNT(1) FROM APPS.ap_suppliers_int
UNION ALL
SELECT RPAD('Total Record Processed from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_suppliers_int WHERE status ='PROCESSED'
UNION ALL
SELECT RPAD('Total Record Rejected while transfering from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_suppliers_int WHERE status ='REJECTED'
UNION ALL
SELECT ' ' FROM DUAL
UNION ALL
SELECT 'SUPPLIER SITES RECONCILIATION REPORT' FROM DUAL
UNION ALL
SELECT '*************************************' FROM DUAL
UNION ALL
SELECT RPAD('Total Record Count in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_sites
UNION ALL
SELECT RPAD('Total Record Errored in staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_sites WHERE error_code IS NOT NULL
UNION ALL
SELECT RPAD('Total Record transfered from staging into Interface Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_sites WHERE status_flag = 'T' AND process_flag = 'T'
UNION ALL
SELECT RPAD('Total Record in Interface Table',80,' ') || COUNT(1) FROM APPS.ap_supplier_sites_int
UNION ALL
SELECT RPAD('Total Record Processed from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_supplier_sites_int WHERE status ='PROCESSED'
UNION ALL
SELECT RPAD('Total Record Rejected while transfering from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_supplier_sites_int WHERE status ='REJECTED'
UNION ALL
SELECT ' ' FROM DUAL
UNION ALL
SELECT 'SUPPLIER CONTACTS RECONCILIATION REPORT' FROM DUAL
UNION ALL
SELECT '**************************************' FROM DUAL
UNION ALL
SELECT RPAD('Total Record Count in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_contacts
UNION ALL
SELECT RPAD('Total Record Errored in Staging Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_contacts WHERE error_code IS NOT NULL
UNION ALL
SELECT RPAD('Total Record transfered from staging into Interface Table',80,' ') || COUNT(1) FROM XXHR.xxcsc_ap_supplier_contacts WHERE status_flag = 'T' AND process_flag = 'T'
UNION ALL
SELECT RPAD('Total Record in Interface Table:',80,' ') || COUNT(1) FROM APPS.ap_sup_site_contact_int
UNION ALL
SELECT RPAD('Total Record Processed from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_sup_site_contact_int WHERE status ='PROCESSED'
UNION ALL
SELECT RPAD('Total Record Rejected while transfering from Interface into Oracle Base Table',80,' ') || COUNT(1) FROM APPS.ap_sup_site_contact_int WHERE status ='REJECTED'
UNION ALL
SELECT ' ' FROM DUAL;
BEGIN
file_handle := UTL_FILE.FOPEN(x_directory, x_file_name ,'W');
file_handle := UTL_FILE.FOPEN(x_directory, x_file_name ,'A');
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, RPAD(' ',35,' ')|| x_object ||' CONVERSION RECON REPORT' || RPAD(' ',35, ' '));
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, ' ');
FOR rec_recon IN cur_recon LOOP
UTL_FILE.PUT_LINE(file_handle, rec_recon.data);
END LOOP;
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, RPAD(' ',35,' ')|| 'END OF THE REPORT' || RPAD(' ',35, ' '));
UTL_FILE.PUT_LINE(file_handle, RPAD('*',85,'*'));
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE( 'ERROR CODE : ' || SQLCODE );
DBMS_OUTPUT.PUT_LINE( 'ERROR DESCRIPTION : ' || SQLERRM );
DBMS_OUTPUT.PUT_LINE( 'ERROR OCCURED WHILE PRINTING THE RECON REPORT');
ROLLBACK;
END XXCSC_AP_SUPP_RECON_PROC;
END XXCSC_AP_SUPPLIERS_CONV_PKG;
/
