Employee Category(PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY) in Employee Assignment Form in Oracle APPS

PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY values coming from HR_LOOKUPS.LOOKUP_TYPE = 'EMP_CAT'

SELECT * FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'EMP_CAT' AND enabled_flag = 'Y' AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE) AND NVL (end_date_active, SYSDATE)

Revenue Analyst in Oracle Fusion

Revenue Analyst manages and implements revenue adjustments, revenue recognition, revenue accounting, creation and processing of revenue documents, and the inquiry of revenue documents and adjustments. 

It also manages the defining of open and closed periods and transferring entries to the GL(General Ledger).

Accounts Receivable Specialist in Oracle Fusion

Accounts Receivable Specialist manages and implements all customer related payment activities, including receiving customer payments and electronic remittance advice, performing cash handling activities, processing customer payments, and applying payments to customer accounts.

Accounts Receivable Manager in Oracle Fusion

Account Receivable Manager manages all accounts receivable activities. It also includes below activities:

• Defining policy and controls
• Establishing processes and procedures, resolving issues
• Monitoring and analyzing accounts receivable balances 
• Creating reports 
• Business Intelligence.

Query to get Next Month First Day for a given Date in Oracle

SELECT TRUNC (ADD_MONTHS (SYSDATE, 1), 'MON'), TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, 1), 'MON'), 'DD-MON-RRRR')  FROM DUAL

Assets by Cash Generating Unit 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.

Assets by Cash Generating Unit Report:

• Lists all assets by cash generating unit. 
• The report extracts the details of all of the asset impairment transactions, such as asset number, cash generating unit, cost, net book value, accumulated impairment reserve, and year-to-date impairments from Oracle Fusion Assets.

Hypothetical Depreciation Analysis 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.

Hypothetical Depreciation Analysis Report:

• Lists depreciation projections for hypothetical assets based on the specified depreciation parameters.

Property Tax 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.

Property Tax Report:

• Lists the assets at a particular location as of the specified cutoff date. 
• This information is used for property tax forms. 
• The report is sorted by and groups totals for each balancing segment, location, asset account, and year acquired.

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. 
• The report is sorted by and groups totals for each balancing segment, reserve account, and cost center.

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. 
• The report is sorted by and groups totals for each balancing segment, asset cost account, and cost center.

Reserve Summary 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. 
• The report is 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. 
• The report is 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. 
• The report is 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 construction-in-process (CIP) cost account balances for the specified periods.
• The report is 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. 
• The report is 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. 
• The report is 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. 
• The report is sorted by and groups totals for each balancing segment, asset cost account, and cost 
•  center.

Cost Clearing Reconciliation 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. 
• The report is 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.
• The report is sorted by transaction type.

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:

• 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 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