Query to Cancel Scheduled Concurrent Program

This Query helps you to cancel scheduled concurrent program/programs from backend

Step 1. Execute Given Query for Specific Request Id or for all requests which are scheduled

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N'
AND CONCURRENT_PROGRAM_ID in (select CONCURRENT_PROGRAM_ID from fnd_concurrent_programs where CONCURRENT_PROGRAM_NAME='Your Program Short Name')
and REQUEST_ID =:p_req_id ---- If you want to cancel all scheduled request then comment this line and execute the query

Step 2. Excute Commit to save the changes

COMMIT;

Query To Get Customer Details with Additional Information

 This query will help you to get Customer Ship to and Bill to site address details along with Additional Information of Sites like Vat Registration No, Service Tax Reg. No etc...

SELECT   hou.NAME ou_name,hou.organization_id, hp.party_name customer_name,
         hp.party_number customer_number, hp.address1 cust_address1,
         hp.address2 cust_address2, hp.address3 cust_address3,
         hp.address4 cust_address4, hp.city cust_city, hp.state cust_state,
         hp.province cust_province, hca.pin_number cust_pin,
         hca.account_number, hca.customer_class_code customer_class,
         hcsu.LOCATION, hcsu.orig_system_reference site_number,
         hcsu.site_use_code,hcsua.cust_acct_site_id, hl.address1 site_address1,
         hl.address2 site_address2, hl.address3 site_address3,
         hl.address4 site_address4, hl.city site_city, hl.state site_state,
         hl.province site_province, hl.postal_code site_postal_code,
         jcca.excise_duty_range, jcca.excise_duty_division, jcca.excise_duty_reg_no,
         jcca.excise_duty_zone, jcca.excise_duty_region, jcca.excise_duty_circle,
         jcca.excise_duty_comm, jcca.ec_code, jcca.cst_reg_no, jcca.st_reg_no, jcca.vat_reg_no,
         jcca.service_tax_regno, jcca.pan_no, jcca.confirm_pan, jcca.tan_no
    FROM hz_parties hp,
         hz_party_sites hps,
         hz_cust_accounts hca,
         hz_locations hl,
         hz_cust_acct_sites_all hcsua,
         hz_cust_site_uses_all hcsu,
         hr_operating_units hou,
         jai_cmn_cus_addresses jcca
   WHERE hp.party_id = hca.party_id
     AND hps.party_id = hp.party_id
     AND hl.location_id = hps.location_id
     AND hps.party_site_id = hcsua.party_site_id
     AND hca.cust_account_id = hcsua.cust_account_id
     AND hcsua.cust_acct_site_id = hcsu.cust_acct_site_id
     AND hp.party_type = 'ORGANIZATION'
     AND hcsu.status = 'A'
     AND hcsu.org_id = hou.organization_id
     AND jcca.address_id(+) = hcsua.cust_acct_site_id
ORDER BY hcsu.LOCATION

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;