Report Triggers Details

Following are the Report Triggers fired at the time of Report Execution

1. BEFORE PARAMETER FORM
2. AFTER PARAMETER FORM
3. BEFORE REPORT
4. BETWEEN PAGES
5. AFTER REPORT

The order of events when a report is executed is as follows:

1 Before Parameter Form trigger is fired.
2 Runtime Parameter Form appears (if not suppressed).
3 After Parameter Form trigger is fired (unless the user cancels from the Runtime
Parameter Form).
4 Report is "compiled."
5 Queries are parsed.
6 Before Report trigger is fired.
7 SET TRANSACTION READONLY is executed (if specified via the READONLY
argument or setting).
8 The report is executed and the Between Pages trigger fires for each page except
the last one. (Note that data can be fetched at any time while the report is being
formatted.) COMMITs can occur during this time due to any of the following--
user exit with DDL, SRW.DO_SQL with DDL, or if ONFAILURE=COMMIT, and the
report fails.
9 COMMIT is executed (if READONLY is specified) to end the transaction.
10 After Report trigger is fired.
11 COMMIT/ROLLBACK/NOACTION is executed based on what was specified via the
ONSUCCESS argument or setting.

From Registration Steps

1. Create the form using the form builder.
2. Generate the executable code (.fmx)
3. Place the .fmb file in AU_TOP forms directory
4. Place the .fmx file in Module specific top forms directory.
5. Register the form with AOL
6. Associate a form function for the form
7. Attach the form function to a menu

Report Registration Steps

A. Report Developed Using Report Builder

     1. Create Report using Report Builder.
     2. Place Report Definition file in the module specific directory.
     3. Create an Executable for report definition file.
     4. Create Concurrent program to that Executable.
     5. Add Report Parameters if any in Parameter window.
     6. Attach the concurrent program to request group.

B. Registration of PL/SQL with Parameter

     1. Create the procedure in the module specific schema.
     2. Create a public synonym for that procedure in the Apps schema.
     3. Create the executable for that procedure.
     4. Create a concurrent program.
     5. Add Report Parameters if any in Parameter window.
     6. Attach the concurrent program to that procedure.

Note: There are two mandatory parameters 1. Errbuf 2. Retcode. Any parameter
which are to be passed should be succeeded with these two parameters and have to be
registered. When calling the procedure, these two parameters are not mentioned.

API For GRN Inspection

This script will help you to do Inspection of GRN. I have developed it as per my requirement you can do the changes in code as per your requirement.

DECLARE
   CURSOR grn_dt
   IS
      SELECT grn_number, item_code, accept_qty, reject_qty
        FROM xx_grn_detail_tl;   ---- Replace this custom table by your table

   v_header_id       NUMBER;
   v_line_id         NUMBER;
   v_item_id         NUMBER;
   v_p_line_id       NUMBER;
   v_p_header_id     NUMBER;
   v_p_line_loc_id   NUMBER;
   v_to_org_id       NUMBER;
   v_uom             VARCHAR2 (250);
   v_emp_id          NUMBER;
   v_vendor_id       NUMBER;
   v_org_id          NUMBER;
   v_trn_id          NUMBER;
BEGIN
   FOR i IN grn_dt
   LOOP
------ Query to get GRN Header Id and Vendor Id -----------------------
      SELECT shipment_header_id, vendor_id
        INTO v_header_id, v_vendor_id
        FROM rcv_shipment_headers
       WHERE TRIM (receipt_num) = TRIM (i.grn_number);

----------- Query to get GRN Line ID,PO Line ID, PO header Id,PO Line Location Id,Ship to Organization Id,UOM -------------------
      SELECT shipment_line_id, item_id, po_line_id, po_header_id,
             po_line_location_id, to_organization_id, rsl.unit_of_measure,
             employee_id
        INTO v_line_id, v_item_id, v_p_line_id, v_p_header_id,
             v_p_line_loc_id, v_to_org_id, v_uom,
             v_emp_id
        FROM rcv_shipment_lines rsl, mtl_system_items_b msib
       WHERE msib.inventory_item_id = rsl.item_id
         AND TRIM (msib.segment1) = TRIM (i.item_code)
         AND shipment_header_id = v_header_id
         AND msib.organization_id = 88;

--- Query to get Operating Unit ID ----------------
      SELECT org_id
        INTO v_org_id
        FROM po_headers_all
       WHERE po_header_id = v_p_header_id;

----- Query to get Transaction Id ---------------
      SELECT transaction_id
        INTO v_trn_id
        FROM rcv_transactions
       WHERE shipment_line_id = v_line_id AND transaction_type = 'RECEIVE';

-------- If condition to insert Accepted Qty Data into Interface Table -----------------------
      IF i.accept_qty IS NOT NULL
      THEN
         INSERT INTO rcv_transactions_interface
                     (interface_transaction_id,
                      GROUP_ID, last_update_date, last_updated_by,
                      creation_date, created_by, last_update_login,
                      transaction_type, transaction_date,
                      processing_status_code, processing_mode_code,
                      transaction_status_code, quantity, unit_of_measure,
                      item_id, employee_id, shipment_header_id,
                      shipment_line_id, receipt_source_code, vendor_id,
                      source_document_code, to_organization_id,
                      parent_transaction_id, po_header_id, po_line_id,
                      po_line_location_id, inspection_status_code,
                      inspection_quality_code, org_id, validation_flag
                     )
              VALUES (rcv_transactions_interface_s.NEXTVAL,--INTERFACE_TRANSACTION_ID
                      rcv_interface_groups_s.NEXTVAL,               --GROUP_ID
                      SYSDATE,                          --LAST_UPDATE_DATE
                      0,                                     --LAST_UPDATE_BY
                      SYSDATE,                                 --CREATION_DATE
                      0,                                  --CREATED_BY
                      0,                         --LAST_UPDATE_LOGIN
                      'ACCEPT',                             --TRANSACTION_TYPE
                      SYSDATE,                     --TRANSACTION_DATE
                      'PENDING',                      --PROCESSING_STATUS_CODE
                      'BATCH',                --PROCESSING_MODE_CODE
                      'PENDING',                     --TRANSACTION_STATUS_CODE
                      i.accept_qty,                       --QUANTITY
                      v_uom,          --UNIT_OF_MEASURE
                      v_item_id,                                     --ITEM_ID
                      v_emp_id,                        --EMPLOYEE_ID
                      v_header_id,     --SHIPMENT_HEADER_ID
                      v_line_id,                            --SHIPMENT_LINE_ID
                      'VENDOR',                --RECEIPT_SOURCE_CODE
                      v_vendor_id,              --VENDOR_ID
                      'PO',                             --SOURCE_DOCUMENT_CODE
                      v_to_org_id,                   --TO_ORGANIZATION_ID
                      v_trn_id,                        --PARENT_TRANSACTION_ID
                      v_p_header_id,                   --PO_HEADER_ID
                      v_p_line_id,         --PO_LINE_ID
                      v_p_line_loc_id,                   --PO_LINE_LOCATION_ID
                      'ACCEPTED',     --INSPECTION_STATUS_CODE
                      'Excellent',                   --INSPECTION_QUALITY_CODE
                      v_org_id,                           --ORG_ID
                     'Y'               --Validation_flag
                     );
      END IF;

----- If Condition to insert Rejected Qty Data into Interface Table -----------------
      IF i.reject_qty IS NOT NULL
      THEN
         INSERT INTO rcv_transactions_interface
                     (interface_transaction_id,
                      GROUP_ID, last_update_date, last_updated_by,
                      creation_date, created_by, last_update_login,
                      transaction_type, transaction_date,
                      processing_status_code, processing_mode_code,
                      transaction_status_code, quantity, unit_of_measure,
                      item_id, employee_id, shipment_header_id,
                      shipment_line_id, receipt_source_code, vendor_id,
                      source_document_code, to_organization_id,
                      parent_transaction_id, po_header_id, po_line_id,
                      po_line_location_id, inspection_status_code,
                      inspection_quality_code, org_id, validation_flag
                     )
              VALUES (rcv_transactions_interface_s.NEXTVAL, --INTERFACE_TRANSACTION_ID
                      rcv_interface_groups_s.NEXTVAL,               --GROUP_ID
                      SYSDATE,   --LAST_UPDATE_DATE
                      0,    --LAST_UPDATE_BY
                      SYSDATE,                                 --CREATION_DATE
                      0,                                  --CREATED_BY
                      0,                         --LAST_UPDATE_LOGIN
                      'REJECT',                             --TRANSACTION_TYPE
                      SYSDATE,                     --TRANSACTION_DATE
                      'PENDING',                      --PROCESSING_STATUS_CODE
                      'BATCH',                --PROCESSING_MODE_CODE
                      'PENDING',                     --TRANSACTION_STATUS_CODE
                      i.accept_qty,                       --QUANTITY
                      v_uom,          --UNIT_OF_MEASURE
                      v_item_id,                                     --ITEM_ID
                      v_emp_id,                        --EMPLOYEE_ID
                      v_header_id,     --SHIPMENT_HEADER_ID
                      v_line_id,                            --SHIPMENT_LINE_ID
                      'VENDOR',                --RECEIPT_SOURCE_CODE
                      v_vendor_id,              --VENDOR_ID
                      'PO',                             --SOURCE_DOCUMENT_CODE
                      v_to_org_id,                   --TO_ORGANIZATION_ID
                      v_trn_id,                        --PARENT_TRANSACTION_ID
                      v_p_header_id,                   --PO_HEADER_ID
                      v_p_line_id,         --PO_LINE_ID
                      v_p_line_loc_id,                   --PO_LINE_LOCATION_ID
                      'REJECTED',     --INSPECTION_STATUS_CODE
                      'Damaged',                     --INSPECTION_QUALITY_CODE
                      v_org_id,                             --ORG_ID
                      'Y'                 --Validation_flag
                     );
      END IF;


UPDATE xx_grn_detail_tl
   SET flag = 'S'
 WHERE grn_number = i.grn_number AND item_code = i.item_code;

fnd_file.put_line (fnd_file.output,'===========================================================================');
fnd_file.put_line (fnd_file.output,
                            'Inspection done successfully for GRN :-'||' '||i.grn_number||' '||i.item_code
                           );
fnd_file.put_line (fnd_file.output,'===========================================================================');

EXCEPTION
   WHEN OTHERS
   THEN
  
fnd_file.put_line (fnd_file.output,'===========================================================================');
      fnd_file.put_line (fnd_file.output,
                            'Error Occured while processing GRN :-'||' '||i.grn_number||' '||i.item_code
                           );
fnd_file.put_line (fnd_file.output,'===========================================================================');
     
UPDATE xx_grn_detail_tl
   SET flag = 'E'
 WHERE grn_number = i.grn_number AND item_code = i.item_code;

END LOOP;
   COMMIT;
END;

1.First Made the changes in code as per requirement and create procedure. 
2.Execute the code.
3.Run Receiving Transaction Processor in Purchasing

Query to get the Concurrent Programs Details

SELECT   DISTINCT fcp.user_concurrent_program_name "Concurrent Program Name",fcp.description "Concurrent Program Description",
fef.executable_name"Executable Name",fef.description "Executable Description",fef.execution_file_name "Procedure Name"
    FROM fnd_executables_form_v fef,fnd_concurrent_programs_vl fcp
   WHERE fcp.APPLICATION_ID=fef.APPLICATION_ID
   AND fef.EXECUTABLE_ID=fcp.EXECUTABLE_ID
   AND fef.executable_name='XX_EXECUTABLE'---Youer Executable Name
  AND fcp.user_concurrent_program_name='Program Name'--Your Consurrent Program name

Oracle Apps Important Tables Module Wise

1)ONT- Order Management


OE_ORDER_HEADERS_ALL  stores header information for orders in Order Management.
OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management.
OE_ORDER_SOURCES Feeder System Names that create orders in Order Management tables.
OE_ORDER_HOLDS_ALL This table stores information of all the orders and lines that are on hold and the link to hold sources and hold releases.
OE_SALES_CREDITS This table stores information about sales credits.
OE_TRANSACTION_TYPES_ALL This table stores information about the order and line transaction types 
WSH_DELIVERY_ASSIGNMENTS Delivery Assignments
WSH_DELIVERY_DETAILS Delivery Details
WSH_NEW_DELIVERIES Deliveries
WSH_TRIPS Trips
WSH_TRIP_STOPS Trip Stops 

2)PO - Purchasing

PO_ACTION_HISTORY Document approval and control action history table
PO_AGENTS Buyers table
PO_DISTRIBUTIONS_ALL Purchase order distributions
PO_HEADERS_ALL Document headers (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINES_ALL Purchase document lines (for purchase orders, purchase agreements, quotations, RFQs)
PO_LINE_LOCATIONS_ALL Document shipment schedules (for purchase orders, purchase agreements, quotations, RFQs)
PO_RELEASES_ALL Purchase order releases
PO_LINES_ARCHIVE_ALL Archived purchase order lines
PO_LINE_LOCATIONS_ARCHIVE_ALL Archived purchase order shipments
PO_HEADERS_ARCHIVE_ALL Archived purchase orders
PO_LINE_TYPES_B Line types
PO_RELEASES_ARCHIVE_ALL Archived releases
PO_REQUISITION_HEADERS_ALL Requisition headers
PO_REQUISITION_LINES_ALL Requisition lines
PO_REQ_DISTRIBUTIONS_ALL Requisition distributions
RCV_TRANSACTIONS Receiving transactions
RCV_SHIPMENT_HEADERS Shipment and receipt header information
RCV_SHIPMENT_LINES Receiving shipment line information  

3)INV – Inventory
 
MTL_CATEGORIES_B Code combinations table for Item Category
MTL_CATEGORY_SETS_B Category Sets
MTL_CUSTOMER_ITEMS Customer item Information
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items
MTL_DEMAND Sales order demand and reservations
MTL_DEMAND_HISTORIES Sales order demand and reservations
MTL_ITEM_LOCATIONS Definitions for stock locators
MTL_ITEM_REVISIONS_B Item revisions
MTL_ITEM_TEMPLATES_B Item template definitions
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template
MTL_LOT_NUMBERS Lot number definitions
MTL_MATERIAL_TRANSACTIONS Material transaction table
MTL_MATERIAL_TRANSACTIONS_TEMP Temporary table for processing material transactions
MTL_ONHAND_QUANTITIES_DETAIL FIFO quantities by control level and receipt
MTL_PARAMETERS Inventory control options and defaults
MTL_RESERVATIONS Reservations
MTL_SECONDARY_INVENTORIES Subinventory definitions
MTL_SECONDARY_LOCATORS Item-subinventory-locator assignments
MTL_SERIAL_NUMBERS Serial number definitions
MTL_SYSTEM_ITEMS_B Inventory item definitions
MTL_TRANSACTION_ACCOUNTS Material transaction distributions
MTL_TRANSACTION_TYPES Inventory Transaction Types Table
MTL_TXN_REQUEST_HEADERS Move Order headers table
MTL_TXN_REQUEST_LINES Move order lines table
MTL_UNIT_TRANSACTIONS Serial number transactions   

4)GL- General Ledger
 
GL_CODE_COMBINATIONS Stores valid account combinations
GL_SETS_OF_BOOKS Stores information about the sets of books
GL_IMPORT_REFERENCES Stores individual transactions from subledgers
GL_DAILY_RATES Stores the daily conversion rates for foreign currency 
Transactions
GL_PERIODS Stores information about the accounting periods
GL_JE_HEADERS Stores journal entries
GL_JE_LINES Stores the journal entry lines that you enter in the Enter Journals form
GL_JE_BATCHES Stores journal entry batches
GL_BALANCES Stores actual, budget, and encumbrance balances for detail and summary accounts
GL_BUDGETS Stores Budget definitions
GL_INTERFACE Import journal entry batches
GL_BUDGET_INTERFACE Upload budget data from external sources
GL_DAILY_RATES_INTERFACE Import daily conversion rates

5)AR- Accounts Receivables
 
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments
RA_BATCHES_ALL
AR_CASH_RECEIPTS_ALL Detailed receipt information
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction
HZ_CUST_ACCOUNTS Stores information about customer accounts.
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts
HZ_CUST_CONTACT_POINTS This table is no longer used
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites.
HZ_LOCATIONS Physical addresses
HZ_ORG_CONTACTS People as contacts for parties
HZ_ORG_CONTACT_ROLES Roles played by organization contacts
HZ_PARTIES Information about parties such as organizations, people, and groups
HZ_PARTY_SITES Links party to physical locations
HZ_PARTY_SITE_USES The way that a party uses a particular site or address
HZ_RELATIONSHIPS Relationships between entities
HZ_RELATIONSHIP_TYPES Relationship types 

6)CE- Cash Management
 
CE_BANK_ACCOUNTS This table contains bank account information. Each bank account must be affiliated with one bank branch.
CE_BANK_ACCT_BALANCES This table stores the internal bank account balances
CE_BANK_ACCT_USES_ALL This table stores information about your bank account uses.
CE_STATEMENT_HEADERS Bank statements
CE_STATEMENT_LINES Bank statement lines
CE_STATEMENT_HEADERS_INT Open interface for bank statements
CE_STATEMENT_LINES_INTERFACE Open interface for bank statement lines
CE_TRANSACTION_CODES Bank transaction codes

7)AP- Accounts Payables
 
AP_ACCOUNTING_EVENTS_ALL Accounting events table
AP_AE_HEADERS_ALL Accounting entry headers table
AP_AE_LINES_ALL Accounting entry lines table
AP_BANK_ACCOUNTS_ALL Bank Account Details
AP_BANK_ACCOUNT_USES_ALL Bank Account Uses Information
AP_BANK_BRANCHES Bank Branches
AP_BATCHES_ALL Summary invoice batch information
AP_CHECKS_ALL Supplier payment data
AP_HOLDS_ALL Invoice hold information
AP_INVOICES_ALL Detailed invoice records
AP_INVOICE_LINES_ALL  contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice distribution line information
AP_INVOICE_PAYMENTS_ALL Invoice payment records
AP_PAYMENT_DISTRIBUTIONS_ALL Payment distribution information
AP_PAYMENT_HISTORY_ALL Maturity and reconciliation history for payments
AP_PAYMENT_SCHEDULES_ALL Scheduled payment information on invoices
AP_INTERFACE_REJECTIONS Information about data that could not be loaded by Payables Open Interface Import
AP_INVOICES_INTERFACE Information used to create an invoice using Payables Open Interface Import
AP_INVOICE_LINES_INTERFACE Information used to create one or more invoice distributions
AP_SUPPLIERS AP_SUPPLIERS stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL stores information about your supplier site level attributes.
AP_SUPPLIER_CONTACTS Stores Supplier Contacts

8)XLA - Subledger Accounting

XLA_EVENTS The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE.
XLA_TRANSACTION_ENTITIES The table XLA_ENTITIES contains information about sub-ledger document or transactions.
XLA_AE_HEADERS The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers.
XLA_AE_LINES The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines.
XLA_DISTRIBUTION_LINKS The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines.
XLA_ACCOUNTING_ERRORS The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program.
XLA_ACCTG_METHODS_B The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers.
XLA_EVENT_TYPES_B The XLA_EVENT_TYPES_B table stores all event types that belong to an event class.
XLA_GL_LEDGERS This table contains ledger information used by subledger accounting.

9)FND – Appication Object Library

FND_APPLICATION Applications registered with Oracle Application Object Library
FND_CONCURRENT_PROGRAMS Concurrent programs
FND_CONCURRENT_REQUESTS Concurrent requests information
FND_CURRENCIES Currencies enabled for use at your site
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library
FND_FLEX_VALUES Valid values for flexfield segments
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields
FND_FORM Application forms registered with Oracle Application Object Library
FND_FORM_FUNCTIONS Functionality groupings
FND_ID_FLEXS Registration information about key flexfields
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments
FND_ID_FLEX_STRUCTURES Key flexfield structure information
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes
FND_LOOKUP_VALUES QuickCode values
FND_MENUS New menu tabl for Release 10SC
FND_PROFILE_OPTIONS User profile options
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels
FND_REQUEST_SETS Reports sets
FND_REQUEST_SET_PROGRAMS Reports within report sets
FND_REQUEST_SET_STAGES Stores request set stages
FND_RESPONSIBILITY Responsibilities
FND_RESP_FUNCTIONS Function Security
FND_USER Application users


10)JA - Asia/Pacific Localizations

JAI_CMN_BOE_HDRS Stores BOE header info when a BOE Invoice is created through IL
JAI_CMN_BOE_DTLS Detail table for BOE Invoices
JAI_CMN_TAXES_ALL Master table for Localization Taxes
JAI_CMN_TAX_CTGS_ALL Stores tax categories and their link to excise ITEM classes.
JAI_CMN_TAX_CTG_LINES Stores the tax lines for defined tax categories
JAI_CMN_VENDOR_SITES Stores excise account related information about vendors.
JAI_RGM_DEFINITIONS Stores regime information.
JAI_RGM_TAXES This table stores tax details for transactions having TCS tax type.
JAI_CMN_RG_23AC_I_TRXS Stores Information of RG23A/C records and known as Quantity Register.
JAI_CMN_RG_23AC_II_TRXS Stores Information of RG23A/C Part II Details. Also known as Amount Register
JAI_CMN_RG_23D_TRXS Quantity register for Trading Organizations
JAI_CMN_RG_BALANCES Store the current balances of RG23A, RG23C and PLA Registers
JAI_CMN_RG_PLA_TRXS Stores the Transaction Information of PLA Register.
JAI_CMN_RG_PLA_HDRS Stores PLA header Infomation when a PLA invoice is created in AP module
JAI_CMN_RG_PLA_DTLS Stores PLA Detail Information when a PLA Invoice is created in AP Module