Create Mass Additions Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Create Mass Additions Report: 

• Lists all invoice and discount lines processed by the Create Mass Additions process. 
• The report is sorted by and groups totals for each foreign currency, balancing segment, asset clearing account, and cost center.
• This report is usually run from Payables.

Asset Impairment Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Impairment Report: 

• Lists the impact of impairment transactions. 
• Displays the cost, new net book value, net selling price, value in use, and impairment loss amount for  all of the impaired assets in the given book and depreciation period. 
• The report is sorted by the asset number.
• The report shows totals for the current net book value, net selling price, values in use, impairment loss, and new net book value.

 

Asset Category Change Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Category Change Report: 

• Lists all of the assets for which the asset category is changed during the specified period. 
• The report is sorted by and groups totals for each balancing segment, asset account, and asset number.

Cost Adjustments by Source Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Adjustments by Source Report:

• Lists all the asset cost adjustments made during the specified periods. 
• The report shows the details of associated invoice lines. 
• The report sorts and totals by source, balancing segment, asset type, asset account, cost center, and asset number.

Cost Adjustment Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Adjustment Report:

• Lists all of the asset cost adjustments made during the specified periods. 
• The report is sorted by and groups totals for each balancing segment, asset type, asset cost account, and cost center.

CIP Capitalization Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

CIP Capitalization Report:

• Lists all of the construction-in-process (CIP) assets capitalized during the specified periods. 
• The report is sorted by and groups totals for each balancing segment, CIP cost account, cost center, and asset cost account.

Asset Transfers Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Transfers Report:

• Lists all asset transfers performed during the specified period. 
• The report is sorted by asset number.

Asset Transaction History Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Transaction History Report:

• Lists all transactions performed on selected assets. 
• The report is sorted by asset number.

Asset Retirements Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Retirements Report:

• Lists all of the asset retirements performed during the specified periods. 
• The report is sorted by and groups totals for each balancing segment, asset type, asset account, and cost center.

Additions by Source Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Additions by Source Report:

• Lists all the assets added or capitalized during the specified periods. 
• The report shows the details of associated invoice lines. 
• The report sorts and totals by source, balancing segment, asset type, asset account, cost center, and asset number

Asset Additions Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Additions Report:

• Lists all of the assets added or capitalized during the specified periods. 
• The report is sorted by and groups totals for each balancing segment, asset type, asset account, cost center, and reserve account.

Group Asset Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Group Asset Summary Report 

• Lists the group asset balances for the specified fiscal year and is used to comply with Indian income tax requirements.

• Sorted by and prints totals for each fiscal year and asset category.

Group Asset Listing in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Group Asset Listing 

• Lists the group asset balances for the specified fiscal year and is used to comply with Canadian capital cost allowance requirements.

• Sorted by and groups totals for each fiscal year and asset category.

Group Asset Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Group Asset Detail Report 

• Lists the group and its member asset balances for the specified fiscal year and is used to comply with Indian income tax requirements.

• Sorted by and groups totals for each fiscal year and asset category.

Revaluation Reserve Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Revaluation Reserve Detail Report 

• Lists asset revaluation reserve account balances for a specified period for an asset level.

• Sorted by and groups totals for each balancing segment, asset cost account, and cost center.

Revaluation Reserve Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Revaluation Reserve Summary Report 

• Lists asset level revaluation reserve account balances for the specified periods.

• Sorted by and groups totals for each balancing segment, reserve account, and cost center.

Reserve Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Reserve Summary Report 

• Lists reserve account summary balances for the specified periods.

• Sorted by and groups totals for each balancing segment and reserve account.

Reserve Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Reserve Detail Report 

• Lists asset level asset reserve account balances for the specified periods.

• Sorted by and groups totals for each balancing segment, reserve account, and cost center.

CIP Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

CIP Summary Report 

• Lists CIP cost account summary balances for the specified periods.

• Sorted by and groups totals for each balancing segment and CIP cost account.

CIP Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

CIP Detail Report 

• Lists asset level CIP cost account balances for the specified periods.

• Sorted by and groups totals for each balancing segment, CIP cost account, and cost center.

Journal Entry Reserve Ledger in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Journal Entry Reserve Ledger 

• Lists asset depreciation amounts for the specified period.

• Sorted by and groups totals for each balancing segment, expense account, reserve account, and cost center.

Cost Summary Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Summary Report 

• Lists asset cost account summary balances for the specified periods.

• Sorted by and groups totals for each balancing segment and asset cost account.

Cost Detail Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Detail Report 

• Lists asset level asset cost account balances for the specified periods.

• Sorted by and groups totals for each balancing segment, asset cost account, and cost center.

Cost Clearing Reconciliation Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Clearing Reconciliation Report 

• Lists all asset additions and cost adjustments performed during the specified period for which accounting entries are created for cost clearing accounts.

• Sorted by and groups totals for each transaction type, balancing segment, and clearing account.

Post Mass Additions Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Post Mass Additions Report 

• Lists all the asset additions and cost adjustments processed by the Post Mass Additions process.

• Sorted by transaction type.

• Generated every time the Post Mass Additions process is run.

Delete Mass Additions Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Delete Mass Additions Report 

This report lists all the mass addition lines in the Delete queue that are ready to be processed by the Delete Mass Additions process.

Create Mass Additions Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Create Mass Additions Report:

• Lists all invoice and discount lines processed by the last run of the Create Mass Additions process.

• Sorted by and groups totals for each foreign currency, balancing segment, asset clearing account, and cost center.

• Generated every time the Create Mass Additions process is run.

Asset Impairment Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Impairment Report 

• Lists the impact of impairment transactions.

• Displays cost, new net book value, net selling price, value in use, and impairment loss amount for all the impaired assets in the given book and depreciation period.

• Sorted by asset number.

Asset Category Change Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Category Change Report 

• Lists all the assets for which the asset category is changed during the specified period.

• Sorted by and groups totals for each balancing segment, asset account, and asset number.

Cost Adjustments by Source Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Adjustments by Source Report 

• Lists all the asset cost adjustments made during the specified periods.

• Shows the details of associated invoice lines.

• Sorts and totals by source, balancing segment, asset type, asset account, cost center, and asset number.

Cost Adjustment Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Cost Adjustment Report 

• Lists all the asset cost adjustments made during the specified periods.

• Sorted by and groups totals for each balancing segment, asset type, asset cost account, and cost center.

CIP Capitalization Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

CIP Capitalization Report 

• Lists all the CIP assets capitalized during the specified periods.

• Sorted by and groups totals for each balancing segment, CIP cost account, cost center, and asset cost account.

Asset Transfers Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Transfers Report 

• Lists all asset transfers performed during the specified period.

• Sorted by asset number.

Asset Transaction History Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Transaction History Report 

• Lists all transactions performed on selected assets.

• Sorted by asset number.

Asset Retirements Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Retirements Report 

• Lists all the asset retirements performed during the specified periods.

• Sorted by and groups totals for each balancing segment, asset type, asset account, and cost center.

Additions by Source Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Additions by Source Report 

• Lists all the assets added or capitalized during the specified periods.

• Shows the details of associated invoice lines.

• Sorts and totals by source, balancing segment, asset type, asset account, cost center and asset number.

Asset Additions Report in Oracle Fusion

Oracle Fusion Assets provides predefined reports that are used in the close process and to verify asset transactions. The Assets reports run from the Scheduled Processes work area found on the Navigator.

Asset Additions Report

 • Lists all the assets added or capitalized during the specified periods.

• Sorted by and groups totals for each balancing segment, asset type, asset account, cost center, and reserve account.

Asset Retirements in Oracle APPS

• Retire an asset when it's no longer in service, for example, if it was stolen, lost, damaged, sold, or returned.
• When retiring assets you can retire an asset completely if the entire asset is no longer in service. You can retire part of an asset if only a portion of the asset is no longer in service.

Sample code to attach file at Invoice level in Oracle APPS

DECLARE l_rowid rowid; l_attached_document_id NUMBER; l_document_id NUMBER; l_media_id NUMBER; l_description fnd_documents_tl.description%TYPE; l_seq_num NUMBER; l_datatype_id NUMBER; CURSOR c_invoice_attachment IS SELECT fad_l.seq_num ,fl_l.file_id media_id ,fl_l.file_data media_data_blob ,fad_l.category_id ,fl_l.file_id ,fl_l.file_name ,fl_l.file_content_type ,fl_l.file_data ,fl_l.file_format ,fdd.datatype_id ,fdd.name document_type ,fdd.user_name ,aia.invoice_id pk1_value ,fdt_l.description FROM fnd_attached_documents fad_l ,fnd_documents fd_l ,fnd_documents_tl fdt_l ,fnd_document_categories_tl fdct_l ,fnd_lobs fl_l ,fnd_document_datatypes fdd ,xx_hdr_tab gen ,ap_invoices_all aia WHERE 1 = 1 AND fad_l.document_id = fd_l.document_id AND fad_l.document_id = fdt_l.document_id AND fdct_l.category_id = fd_l.category_id AND fd_l.media_id = fl_l.file_id AND fad_l.entity_name = 'XX_HDR_TAB' AND fdt_l.language = USERENV('LANG') AND fdct_l.language = USERENV('LANG') AND fd_l.datatype_id = fdd.datatype_id AND fdd.language = USERENV('LANG') AND fad_l.pk1_value = '90' AND fad_l.pk1_value = gen.xx_hdr_id AND gen.invoice_num = aia.invoice_num ; BEGIN FOR i IN c_invoice_attachment LOOP insert into xx_test1 values(xx_test1_s.nextval, 'i.document_type: '||i.document_type); IF i.document_type = 'WEB_PAGE' THEN l_media_id := null; ELSE SELECT fnd_lobs_s.nextval INTO l_media_id FROM dual; END IF; insert into xx_test1 values(xx_test1_s.nextval, 'l_media_id: '||l_media_id); If i.document_type IN ('WEB_PAGE', 'FILE') THEN SELECT fnd_documents_s.NEXTVAL INTO l_document_id FROM dual; SELECT fnd_attached_documents_s.NEXTVAL INTO l_attached_document_id FROM dual; SELECT nvl(max(seq_num),0) + 10 INTO l_seq_num FROM fnd_attached_documents WHERE pk1_value = i.pk1_value --UNIQUE ID AND entity_name = 'AP_INVOICES'; insert into xx_test1 values(xx_test1_s.nextval, 'l_seq_num: '||l_seq_num); fnd_documents_pkg.insert_row (X_ROWID => l_rowid ,X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => SYSDATE ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => SYSDATE ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => 0 ,X_DATATYPE_ID => i.datatype_id --5 -- Web Page ,X_CATEGORY_ID => 291--l_category_id -- Invoice Internal ,X_SECURITY_TYPE => 2 ,X_PUBLISH_FLAG => 'Y' ,X_USAGE_TYPE => 'O' ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_FILE_NAME => i.file_name ,X_MEDIA_ID => l_media_id -- R12 ,X_URL => i.user_name ); fnd_documents_pkg.insert_tl_row (X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => sysdate ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => sysdate ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => fnd_global.login_id ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_TITLE => i.file_name ); fnd_attached_documents_pkg.insert_row (X_ROWID => l_rowid ,X_ATTACHED_DOCUMENT_ID => l_attached_document_id ,X_DOCUMENT_ID => l_document_id ,X_CREATION_DATE => sysdate ,X_CREATED_BY => fnd_global.user_id ,X_LAST_UPDATE_DATE => sysdate ,X_LAST_UPDATED_BY => fnd_global.user_id ,X_LAST_UPDATE_LOGIN => fnd_global.login_id ,X_SEQ_NUM => l_seq_num ,X_ENTITY_NAME => 'AP_INVOICES' ,X_COLUMN1 => null ,X_PK1_VALUE => i.pk1_value ,X_PK2_VALUE => null ,X_PK3_VALUE => null ,X_PK4_VALUE => null ,X_PK5_VALUE => null ,X_AUTOMATICALLY_ADDED_FLAG => 'N' ,X_DATATYPE_ID => i.datatype_id ,X_CATEGORY_ID => 291--l_category_id -- Invoice Internal ,X_SECURITY_TYPE => 2 ,X_PUBLISH_FLAG => 'Y' ,X_LANGUAGE => 'US' ,X_DESCRIPTION => i.description ,X_FILE_NAME => i.file_name ,X_MEDIA_ID => l_media_id -- R12 ,X_URL => i.user_name ,X_TITLE => i.description ); IF l_media_id IS NOT NULL THEN INSERT INTO FND_LOBS (file_id ,file_name ,file_content_type ,file_data ,upload_date ,expiration_date ,program_name ,program_tag ,language ,oracle_charset ,file_format ) VALUES (l_media_id ,i.file_name ,i.file_content_type ,i.file_data ,sysdate ,null ,null ,null ,'US' ,'UTF8' ,'binary' ); END IF; commit; END IF; END LOOP; END;

oracle.apps.ap.payment Business Event in Oracle APPS

This Business Event get triggered when Invoice Payment is done or Void the Payment. It has below parameter names.

USER_ID

RESP_ID

RESP_APPL_ID

SECURITY_GROUP_ID

ORG_ID

CUSTOMER_TRX_ID

PAYMENT_SCHEDULE_ID

HISTORY_ID

oracle.apps.ap.invoice.import Business Event in Oracle APPS

This Business Event get triggered when Payables Open Interface Import is submitted. It has REQUEST_ID and #CURRENT_PHASE as parameter names. When  Payables Open Interface Import program completes we can find REQUEST_ID in AP_INVOICE_LINES_ALL table.

ALR_ALERTS.ALERT_CONDITION_TYPE(Event, Periodic) in Oracle Alerts

SELECT * FROM fnd_lookups WHERE lookup_type = 'ALERT_CONDITION_TYPE'

Query to get list of Custom Alerts in Oracle APPS

SELECT aa.alert_name, aa.creation_date, aa.last_update_date, aa.alert_condition_type, fl.meaning alert_type, aa.enabled_flag, aa.start_date_active, aa.end_date_active, aa.description alert_desc, aa.date_last_checked, aa.maintain_history_days, aa.table_name, aa.sql_statement_text, fat.application_name FROM alr_alerts aa ,fnd_lookups fl ,fnd_application_tl fat WHERE aa.alert_name LIKE 'XX%' AND aa.alert_condition_type = fl.lookup_code AND fl.lookup_type = 'ALERT_CONDITION_TYPE' AND aa.application_id = fat.application_id AND fat.language = USERENV('LANG')

Query to get Concurrent Request Set Details in Oracle APPS

 SELECT rs.user_request_set_name "Request Set Name", rs.request_set_name "Set code", rs.description "Description", rss.display_sequence Seq, cp.user_concurrent_program_name "Concurrent Program Name", ap.application_name "Application Name", e.executable_name "Executable", e.execution_file_name "Executable File", lv.meaning "Executable Type" FROM apps.fnd_request_sets_vl rs, apps.fnd_req_set_stages_form_v rss, applsys.fnd_request_set_programs rsp, apps.fnd_concurrent_programs_vl cp, apps.fnd_executables e, apps.fnd_lookup_values lv, apps.fnd_application_vl ap WHERE rs.application_id = rss.set_application_id AND rs.request_set_id = rss.request_set_id AND rss.set_application_id = rsp.set_application_id AND rss.request_set_id = rsp.request_set_id AND rss.request_set_stage_id = rsp.request_set_stage_id AND rsp.program_application_id = cp.application_id AND rsp.concurrent_program_id = cp.concurrent_program_id AND cp.executable_id = e.executable_id AND cp.executable_application_id = e.application_id AND e.application_id = ap.application_id AND lv.lookup_type = 'CP_EXECUTION_METHOD_CODE' AND lv.lookup_code = e.execution_method_code AND rs.user_request_set_name LIKE 'XX REQUEST SET%' AND lv.language=USERENV('LANG') ORDER BY rss.display_sequence

Query to extract Fixed Assets in Oracle APPS

SELECT DISTINCT fab.asset_id, fab.asset_number, FAT.DESCRIPTION, fab.current_units, SUM (fdh.units_assigned) units_assigned_count, COUNT (fdh.distribution_id) distribution_count, ROUND ( (fb.life_in_months / 12), 1) lifeyear, TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY') date_placed_in_service, fb.deprn_method_code, fb.cost, fb.original_cost, (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3) CATEGORY_COMBINATION, NVL ( (SELECT SUM (deprn_reserve) FROM fa_deprn_detail FDD, fa_distribution_history fdhi WHERE fdd.asset_id = fab.asset_id AND PERIOD_COUNTER = (SELECT MAX (PERIOD_COUNTER) FROM fa_deprn_detail fddi WHERE ASSET_ID = FDD.ASSET_ID AND fddi.distribution_id = fdd.distribution_id AND fddi.distribution_id = fdhi.distribution_id) AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID), 0) depreciation, ( fb.cost - NVL ( (SELECT SUM (deprn_reserve) FROM fa_deprn_detail FDD, fa_distribution_history fdhi WHERE fdd.asset_id = fab.asset_id AND PERIOD_COUNTER = (SELECT MAX (PERIOD_COUNTER) FROM fa_deprn_detail fddi WHERE ASSET_ID = FDD.ASSET_ID AND fddi.distribution_id = fdd.distribution_id AND fddi.distribution_id = fdhi.distribution_id) AND FDD.DISTRIBUTION_ID = FDHI.DISTRIBUTION_ID), 0)) nbv, FACB.ASSET_COST_ACCT , facb.deprn_reserve_acct , fb.book_type_code, TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY') CREATION_DATE, TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY') purchased_date, fab.creation_date, fcb.category_id, (SELECT DISTINCT pol.category_id || ';' || mc.concatenated_segments || ';' || poh.segment1 po_number FROM po_headers_all poh, po_lines_all pol, po_distributions_all pda, gl_code_combinations gcc, mtl_categories_kfv mc, FA_INVOICE_DETAILS_V fidv WHERE poh.po_header_id = pol.po_header_id AND pol.po_line_id = pda.po_line_id AND pol.po_header_id = pda.po_header_id AND gcc.code_combination_id = pda.code_combination_id AND pol.category_id = mc.category_id AND fidv.asset_id = fab.asset_id AND fidv.po_number = poh.segment1 AND ROWNUM = 1) po_details FROM fa_additions_b fab, fa_additions_tl fat, fa_books fb, fa_categories_b fcb, fa_distribution_history fdh, fa_locations fl, fa_category_books facb, fa_asset_keywords fak, fa_add_warranties fad, fa_warranties fw, gl_code_combinations_kfv gcc WHERE 1 = 1 AND fab.asset_id = fat.asset_id AND fab.asset_category_id = fcb.category_id AND fab.asset_id = fb.asset_id AND fab.asset_id = fdh.asset_id AND fdh.location_id = fl.location_id AND fak.code_combination_id(+) = fab.asset_key_ccid AND fad.asset_id(+) = fab.asset_id AND fb.date_ineffective IS NULL AND facb.category_id = fcb.category_id AND fb.book_type_code LIKE 'XX%CORP%BOOK' AND facb.book_type_code = fb.book_type_code AND fat.LANGUAGE = USERENV('LANG') AND FW.WARRANTY_ID(+) = FAD.WARRANTY_ID AND FDH.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID GROUP BY fab.asset_id, fab.asset_number, FAT.DESCRIPTION, fab.current_units, ROUND ( (fb.life_in_months / 12), 1), TO_CHAR (fb.date_placed_in_service, 'DD-MON-YYYY'), fb.deprn_method_code, fb.original_cost, (fcb.segment1 || '-' || fcb.segment2 || '-' || fcb.segment3), FACB.ASSET_COST_ACCT, facb.deprn_reserve_acct, fb.book_type_code, TO_CHAR (FAB.CREATION_DATE, 'DD-MON-YYYY'), TO_CHAR (FB.DATE_PLACED_IN_SERVICE, 'DD-MON-YYYY'), fab.creation_date, fb.cost, fcb.category_id order by 1,2

How to get First Day of the Month in SQL

SELECT TRUNC(SYSDATE, 'Month') FROM dual

Query to get Employee Contract Duration in Oracle Apps

Nav: Global HRMS Manager--> People-->Enter and Maintain--> Query Employee--> Click on Others Button-->Select Contract from List

select papf.person_id ,papf.employee_number ,papf.full_name ,pcf.duration ,hl.meaning Units from per_all_people_f papf ,per_contracts_f pcf ,hr_lookups hl where papf.person_id = pcf.person_id AND papf.person_id = :p_person_id AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND NVL (TRUNC (pcf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND pcf.duration_units = hl.lookup_code AND hl.lookup_type(+) = 'QUALIFYING_UNITS'

Query to get Employee Dependent Date Of Birth(Contact Information) in Oracle APPS

Nav: Global HRMS Manager--> People-->Enter and Maintain--> Query Employee--> Click on Others Button-->Select Contact from List

SELECT papf.person_id ,papf.employee_number ,papf.full_name employee_name ,papf_cont.full_name contact_name ,TO_CHAR(papf_cont.date_of_birth, 'DD-MON-RRRR') contact_date_of_birth ,hl.meaning contact_type FROM per_contact_relationships pcr ,per_all_people_f papf ,hr_lookups hl ,per_all_people_f papf_cont WHERE 1 = 1 AND papf.person_id = pcr.person_id AND pcr.contact_person_id = papf_cont.person_id AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE) AND hl.lookup_type(+) = 'CONTACT' AND hl.lookup_code(+) = pcr.contact_type AND papf.person_id = :p_person_id

Why there is no entry in AR_PAYMENT_SCHEDULES_ALL even after completing the AR Transaction in Oracle APPS

• After completing AR Transaction there is no data or entry in AR_PAYMENT_SCHEDULES_ALL table. 
• Possible reason is "Open Receivables" check box is not checked for the Transaction Type while creating it. 
• If Open Receivable is set to Yes then Receivables updates Customer Balances each time you create a complete Credit Memo, Debit Memo, Chargeback, or On-Account Credit with this Transaction Type. 
• This also includes these Transactions in the Standard Aging and Collection Processes in Receivables.

API to validate AP Invoice using AP_APPROVAL_PKG.BATCH_APPROVAL(To Change Invoice Status to Validated) in Oracle APPS

CREATE OR REPLACE PROCEDURE xx_apinv_wht_validate(errbuf   OUT VARCHAR2

                                                 ,retcode  OUT VARCHAR2

         ,p_inv_date_from IN VARCHAR2

         ,p_inv_date_to   IN VARCHAR2

                                                 )

AS

CURSOR cur_wht_inv(p_org_id    IN NUMBER

                  ,v_inv_date_from IN DATE

  ,v_inv_date_to   IN DATE

  )

IS

SELECT DISTINCT aia.invoice_id

              ,aia.org_id

              ,aia.invoice_num

              ,aia.invoice_type_lookup_code

          FROM ap_invoices_all aia

              ,ap_invoice_lines_all aila

         WHERE 1 = 1

           AND aia.invoice_type_lookup_code = 'AWT'

   AND aia.invoice_id = aila.invoice_id

           AND aia.org_id = aila.org_id

   AND NVL(aila.cancelled_flag,'Y') <> 'Y'

           AND ap_invoices_pkg.get_approval_status(aia.invoice_id

                                                  ,aia.invoice_amount

                                                  ,aia.payment_status_flag

                                                  ,aia.invoice_type_lookup_code

                )NOT IN ('APPROVED', 'UNPAID')

           AND aia.org_id = p_org_id

   AND aia.invoice_date BETWEEN NVL(v_inv_date_from, aia.invoice_date) AND NVL(v_inv_date_to, aia.invoice_date)

;

l_org_id hr_operating_units.organization_id%TYPE;

l_hold_cnt NUMBER;

l_inv_from_date DATE;

l_inv_to_date   DATE;

 

BEGIN

fnd_file.put_line (fnd_file.log , 'XX WHT Invoice Validation Start');

l_org_id := fnd_profile.value('ORG_ID');

l_inv_from_date := fnd_date.canonical_to_date(p_inv_date_from);

l_inv_to_date := fnd_date.canonical_to_date(p_inv_date_to);

mo_global.set_policy_context('S', l_org_id);

fnd_global.apps_initialize(user_id      => fnd_global.user_id

                          ,resp_id      => fnd_global.resp_id

  ,resp_appl_id => fnd_global.resp_appl_id

  );

FOR rec_wht_inv IN cur_wht_inv(l_org_id

                              ,l_inv_from_date

  ,l_inv_to_date

  )

LOOP

fnd_file.put_line (fnd_file.log ,'WHT invoice_num: '||rec_wht_inv.invoice_num);

  

BEGIN

l_hold_cnt := NULL;

IF (ap_approval_pkg.batch_approval(p_run_option => NULL

  ,p_sob_id => fnd_profile.VALUE('GL_SET_OF_BKS_ID')

  ,p_inv_start_date => NULL

  ,p_inv_end_date => NULL

  ,p_inv_batch_id => NULL

  ,p_vendor_id => NULL

  ,p_pay_group => NULL

  ,p_invoice_id => rec_wht_inv.invoice_id

  ,p_entered_by => NULL

  ,p_debug_switch => 'N'

  ,p_conc_request_id => fnd_profile.VALUE('CONC_REQUEST_ID')

  ,p_commit_size => NULL

  ,p_org_id => rec_wht_inv.org_id

  ,p_report_holds_count => l_hold_cnt

  ,p_transaction_num => NULL

  )

)

THEN   

fnd_file.put_line (fnd_file.log , 'Invoice Validated: '||rec_wht_inv.invoice_num);

ELSE

fnd_file.put_line (fnd_file.log , 'Invoice Validation Failed: '||rec_wht_inv.invoice_num);

END IF;

COMMIT;

  

EXCEPTION 

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log , rec_wht_inv.invoice_num ||' Invoice Validation failed with unhandled exception.Error: '||SQLERRM);

END;

END LOOP;

fnd_file.put_line (fnd_file.log , 'XX WHT Invoice Validation End');

EXCEPTION 

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.log ,' XX_APINV_WHT_VALIDATE failed with Unhandled Exception.Error: '||SQLERRM);

END;