P2P Cycle in Oracle EBS

P2P Cycle involve following steps
1. Requisition creation
     Requisition is nothing but a formal request to buy something needed for the enterprise.
There are two types of Requisition
     1.Internal Requisition
     2.Purchase Requisition
2.Purchase Order Creation
      There are four types of Purchase Order
     1.Standard PO
     2.Blanket PO
     3.Contract PO
     4.Planned PO
3.GRN (Good Receipt Note) Creation
4.Invoice Creation
5.Payment of Invoice

Following are the tables get affected when user perform P2P Transaction on Application

1. At the time of requisition following tables are get populated

PO_REQUISITION_HEADERS_ALL   holds the Requisition Header Details.
Important Columns of this table are REQUISITION_HEADER_ID and  SEGMENT1 Segment1 holds the requisition number generated by system.

PO_REQUISITION_LINES_ALL  holds the Line Details of Requisition
Important columns of this table are REQUISITION_HEADER_ID and REQUISITION_LINE_ID

PO_REQ_DISTRIBUTIONS_ALL  holds the line distribution details of Requisition Line
Important Columns of this tables are REQUISITION_LINE_ID and DISTRIBUTION_ID

Joins between above three tables

PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID=PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID
PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID=PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID

2. At the time of  Purchase order following tables are get populated

PO_HEADERS_ALL      This table holds the PO Header Information like PO Number,Vendor, Buyer, Ship to and Bill to Location etc. details.
Important columns of this tables are PO_HEADER_ID and SEGMENT1 segment1 holds the PO Number.

PO_LINES_ALL    This table holds the PO Line details like Item Code, Unit of Measure, Quantity,Price etc.
Important columns of this tables are PO_HEADER_ID and PO_LINE_ID

PO_LINE_LOCATIONS_ALL   This table holds the Shipment details of the PO Line.
Important Columns of this tables are LINE_LOCATION_ID,PO_RELEASE_ID,PO_LINE_ID and PO_HEADER_ID

PO_DISTRIBUTIONS_ALL    This table holds the Distribution details of the PO Line.
Important Columns of this tables are PO_DISTRIBUTION_ID, LINE_LOCATION_ID, PO_RELEASE_ID, PO_LINE_ID and PO_HEADER_ID

JAI_PO_TAXES     This table holds the Tax Details which are applied on PO Line.
Important Columns of this table are LINE_LOCATION_ID,PO_LINE_ID,PO_HEADER_ID,TAX_ID  and TAX_CATEGORY_ID

PO_HEADERS_ALL.PO_HEADER_ID=PO_LINES_ALL.PO_HEADER_ID
PO_LINES_ALL.PO_LINE_ID=PO_LINE_LOCATIONS_ALL.PO_LINE_ID
PO_LINES_ALL.PO_LINE_ID=PO_DISTRIBUTIONS_ALL.PO_LINE_ID
PO_LINES_ALL.PO_LINE_ID=JAI_PO_TAXES.PO_LINE_ID

3. At the time of Receipt (GRN) creation following tables are get populated

RCV_SHIPMENT_HEADERS_ALL  This table holds the Receipt Header Details
Important Columns of this table are SHIPMENT_HEADER_ID and RECEIPT_NUM

RCV_SHIPMENT_LINES
Important Columns of this table are SHIPMENT_HEADER_ID and SHIPMENT_LINE_ID
 
RCV_TRANSACTIONS
Important Columns of this table are SHIPMENT_HEADER_ID,SHIPMENT_LINE_ID, TRANSACTION_ID and TRANSACTION_TYPE

 RCV_SHIPMENT_HEADERS_ALL.SHIPMENT_HEADER_ID=RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID
 RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID=RCV_TRANSACTIONS.SHIPMENT_LINE_ID
 RCV_SHIPMENT_LINES.PO_LINE_ID=PO_LINES_ALL.PO_LINE_ID

4. At the time of Invoice creation following tables are get populated

AP_INVOICES_ALL
Important Columns of this table are INVOICE_ID,INVOICE_DATE and INVOICE_NUM
 
AP_INVOICE_LINES
Important Columns of this table are INVOICE_ID,RCV_TRANSACTION_ID and RCV_SHIPMENT_LINE_ID
 
AP_INVOICE_DISTRIBUTIONS_ALL
Important Columns of this table are INVOICE_ID,INVOICE_DISTRIBUTION_ID and DIST_CODE_COMBINATION_ID

AP_ACCOUNTING_EVENTS_ALL

 AP_INVOICES_ALL.INVOICE_ID=AP_INVOICE_LINES.INVOICE_ID
 AP_INVOICE_LINES.RCV_SHIPMENT_LINE_ID=RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID
 AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID=AP_INVOICE_LINES.INVOICE_ID

5. At the time of Payment entry following tables are get populated

AP_INVOICE_PAYMENTS_ALL
 Important Columns of this table are INVOICE_ID,CHECK_ID and INVOICE_PAYMENT_ID

AP_PAYMENT_SCHEDULES_ALL
 Important Columns of this table are INVOICE_ID and PAYMENT_NUM

AP_CHECKS_ALL
Important Columns of this table are INVOICE_ID,PAYMENT_ID,BANK_ACCOUNT_ID and CHECK_ID

AP_BANK_ACCOUNTS_ALL
 Important Columns of this table are BANK_ACCOUNT_ID,BANK_ACCOUNT_NAME and BANK_BRANCH_ID

AP_BANK_BRANCHES
Important Columns of this table are BANK_BRANCH_ID,BANK_NAME and BANK_BRANCH_NAME.

AP_TERMS 
Important Columns of this table are TERM_ID and NAME


AP_INVOICE_PAYMENTS_ALL.INVOICE_ID=AP_INVOICES_ALL.INVOICE_ID
AP_INVOICE_PAYMENTS_ALL.CHECK_ID=AP_CHECKS_ALL.CHECK_ID
AP_CHECKS_ALL.BANK_ACCOUNT_ID=AP_BANK_ACCOUNTS_ALL.BANK_ACCOUNT_ID
AP_BANK_BRANCHES.BANK_BRANCH_ID=AP_BANK_ACCOUNTS_ALL.BANK_BRANCH_ID

Supplier Tables


AP_SUPPLIERS
Important Columns of this table are VENDOR_ID,VENDOR_NAME and SEGMENT1

AP_SUPPLIER_SITES_ALL
Important Columns of this table are VENDOR_ID,VENDOR_SITE_ID and VENDOR_SITE_CODE

AP_SUPPLIERS.VENDOR_ID=AP_SUPPLIER_SITES_ALL.VENDOR_ID

Join with PO Table
AP_SUPPLIERS.VENDOR_ID=PO_HEADERS_ALL.VENDOR_ID
AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID=PO_HEADERS_ALL.VENDOR_SITE_ID

 Join with Invoice Table
AP_SUPPLIERS.VENDOR_ID=AP_INVOICES_ALL.VENDOR_ID
AP_SUPPLIER_SITES_ALL.VENDOR_SITE_ID=AP_INVOICES_ALL.VENDOR_SITE_ID

Script to Register Table with Oracle Application

DECLARE
   v_appl_short_name   VARCHAR2 (40) := 'XX';-- Application Short name
   v_tab_name          VARCHAR2 (32) := 'XX_TABLE';
                                      -- Change the table name if you require
   v_tab_type          VARCHAR2 (50) := 'T';
   v_next_extent       NUMBER        := 512;
   v_pct_free          NUMBER;
   v_pct_used          NUMBER;
BEGIN
   -- Unregister the custom table if it exists
   ad_dd.delete_table (p_appl_short_name      => 'XX',---- Application Short Name
                       p_tab_name             => v_tab_name
                      );

   -- Register the custom table
   FOR tab_details IN (SELECT table_name, tablespace_name, pct_free, pct_used,
                              ini_trans, max_trans, initial_extent,
                              next_extent
                         FROM dba_tables
                        WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_table (p_appl_short_name      => v_appl_short_name,
                            p_tab_name             => tab_details.table_name,
                            p_tab_type             => v_tab_type,
                            p_next_extent          => NVL
                                                         (tab_details.next_extent,
                                                          512
                                                         ),
                            p_pct_free             => NVL
                                                         (tab_details.pct_free,
                                                          10
                                                         ),
                            p_pct_used             => NVL
                                                         (tab_details.pct_used,
                                                          70
                                                         )
                           );
   END LOOP;

   -- Register the columns of custom table
   FOR all_tab_cols IN (SELECT column_name, column_id, data_type, data_length,
                               nullable
                          FROM all_tab_columns
                         WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_column (p_appl_short_name      => v_appl_short_name,
                             p_tab_name             => v_tab_name,
                             p_col_name             => all_tab_cols.column_name,
                             p_col_seq              => all_tab_cols.column_id,
                             p_col_type             => all_tab_cols.data_type,
                             p_col_width            => all_tab_cols.data_length,
                             p_nullable             => all_tab_cols.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;

   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = v_tab_name)
   LOOP
      ad_dd.register_primary_key (p_appl_short_name      => v_appl_short_name,
                                  p_key_name             => all_keys.constraint_name,
                                  p_tab_name             => all_keys.table_name,
                                  p_description          => 'Register primary key',
                                  p_key_type             => 'S',
                                  p_audit_flag           => 'N',
                                  p_enabled_flag         => 'Y'
                                 );

      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name
                             AND constraint_name = all_keys.constraint_name)
      LOOP
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => v_appl_short_name,
                                      p_key_name             => all_keys.constraint_name,
                                      p_tab_name             => all_keys.table_name,
                                      p_col_name             => all_columns.column_name,
                                      p_col_sequence         => all_columns.POSITION
                                     );
      END LOOP;
   END LOOP;

   COMMIT;
END;

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