Oracle Receivable Receipts API's

Oracle Receivable Receipts API's

DECLARE

    l_return_status VARCHAR2(1);

    l_msg_count NUMBER;

    l_msg_data VARCHAR2(240);

    l_cash_receipt_id NUMBER;

    p_count number := 0;

    l_receipt_number varchar(10);

 BEGIN

     -- 1) Set the applications context

     mo_global.init('AR');

     mo_global.set_policy_context('S','204');

     fnd_global.apps_initialize(1011902, 50559, 222,0);

    l_receipt_number := 'misc';

    AR_RECEIPT_API_PUB.CREATE_MISC

    ( p_api_version => 1.0,

      p_init_msg_list => FND_API.G_TRUE,

      p_commit => FND_API.G_TRUE,

      p_validation_level => FND_API.G_VALID_LEVEL_FULL,

      x_return_status => l_return_status,

      x_msg_count => l_msg_count,

      x_msg_data => l_msg_data,

      p_amount => 4560.00,

      p_receipt_date => '22-NOV-2014',

      p_gl_date => '22-NOV-2014',

      p_receipt_method_id => 2046,

      p_activity => 'TEST_ACTIVITY'

      p_misc_receipt_id => l_cash_receipt_id ,

      p_receipt_number => l_receipt_number);

     -- 3) Review the API output

     dbms_output.put_line('Status ' || l_return_status);

     dbms_output.put_line('Message count ' || l_msg_count);

     dbms_output.put_line('Cash Receipt ID ' || l_cash_receipt_id );

     if l_msg_count = 1 Then

        dbms_output.put_line('l_msg_data '|| l_msg_data);

     elsif l_msg_count > 1 Then

        loop

           p_count := p_count + 1;

           l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);

           if l_msg_data is NULL Then

              exit;

           end if;

           dbms_output.put_line('Message ' || p_count ||'. '||l_msg_data);

        end loop;

     end if;

 END;

plsql procedure for submit journal import program

CREATE OR REPLACE PROCEDURE apps.xx_submit_journal_import_prg

AS

   --DECLARE

   v_request_id   VARCHAR2 (100);

BEGIN

   -- First set the environment of the user submitting the request by submitting

   -- fnd_global.apps_initialize().

   -- The procedure requires three parameters

   -- Fnd_Global.apps_initialize(userId,responsibilityId,applicationId)

   -- replace the following code with correct value as get from sql above

   fnd_global.apps_initialize (2836, 20434, 101);

   --(  user_id , responsibility_id, application_id )

   v_request_id :=

      fnd_request.submit_request ('SQLGL',           -- Application short name

                                  'GLLEZLSRS',           -- Program short name

                                  '',

                                  --'Allows customers to submit Journal Import through the Standard Request Submission form.', -- description (Not require)

                                  '',        -- start_time (start immediately)

                                  FALSE,        -- sub_request (default FALSE)

                                  -- Next is the parameters list of ARACCPB

                                  '1000', --  Parameter  =   Data access_set_id

                                  '98',

                                  --    Parameter  =   Journal Source Name ( use journal soure name not journal suer source name)

                                  '1',          --     Parameter  =  Ledger ID

                                  '',            --      Parameter  = Group ID

                                  'N', --     Parameter  =  Post Error to Suspense

                                  'N', --     Parameter  = Create Summary Journal

                                  'N' --      Parameter  =  Import Descriptive Flexfeild

                                     );

   DBMS_OUTPUT.put_line ('Request submitted. ID = ' || v_request_id);

   fnd_file.put_line (fnd_file.output,

                      'Request submitted. ID = ' || v_request_id);

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (

         'Request set submission failed - unknown error: ' || SQLERRM);

      fnd_file.put_line (

         fnd_file.output,

         'Request set submission failed - unknown error: ' || SQLERRM);

END;

Oracle Apps P2P Procure to pay

Oracle Apps P2P  Procure to Pay  Flow With Table Structure

 

In this article, we will go through the Procure to Pay Cycle (P2P). The below are the steps in short:

1. Enter the Purchase Requisition
2. Enter the RFQ
3. Enter the Quotation
4. Enter the Purchase Order
5. Enter the Receipts
6. Enter the Invoices
7. Enter the Payments
8. Create Accounting
9. Transfer to General Ledger
10. Journal Import
11. Posting

 

Oracle Apps P2P ( Procure to Pay) – Flow With Table Structure


Step 1: Create the Requisition in Oracle Apps

When the company/person wants to purchase any item a Requisition is created.

PO_REQUISITIONS_INTERFACE_ALL         --.Req. Interface. Info ( Stores Requisition Headers, Lines, Distributions Information’s stores in this tables)

--Check Interface Success & Rejected Tables

 

PO_REQUISITION_HEADERS_ALL             --.Req. Header. Info
PO_REQUISITION_LINES_ALL               --.Req. Lines. Info
PO_REQ_DISTRIBUTIONS_ALL               --.Req. Distributions. Info

 

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of REQUISITION like (Incomplete, In process, Approved, Rejected Etc.)

----What are backend Column Status to know the matching status with Purchase Order?

Step 2: RFQ (Request For Quotation)
Once Requisition is approved then the company/person will Request for Quotation (RFQ) specifying
the required item, quantity.... etc and send them to the vendors who can supply the item

 

PO_HEADERS_INTERFACE              -- PO. Header. Interface Information

PO_LINES_INTERFACE                -- PO. Line Interface Information

PO_LINE_LOCATIONS_INTERFACE      -- PO. Shipment. Interface Information

PO_DISTRIBUTIONS_INTERFACE       -- PO. Distribution Interface Information

--Check Interface Success & Rejected Tables

 

PO_HEADERS_ALL                    --.PO.Header.Information (Look Type  =  RFQ)
PO_LINES_ALL                      --.PO.Line.Information
PO_LINE_LOCATIONS_ALL            --.PO.Line.Shipment.Info
PO_DISTRIBUTIONS_ALL              --.PO.Accounting.Info

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of RFQ like (Incomplete, In process, Approved, Rejected Etc.)

----What are backend Column Status to know the matching status with Purchase Order?

 

 

Step 3: Quotation
Once the vendor receives the RFQ then he will prepare the Quotation and send it back to the company/person.

PO_HEADERS_INTERFACE              -- PO. Header. Interface Information

PO_LINES_INTERFACE                -- PO. Line Interface Information

PO_LINE_LOCATIONS_INTERFACE      -- PO. Shipment. Interface Information

PO_DISTRIBUTIONS_INTERFACE       -- PO. Distribution Interface Information

--Check Interface Success & Rejected Tables

 

 

PO_HEADERS_ALL                    --.PO. Header. Information (Look Type  =  Quotations)
PO_LINES_ALL                      --.PO.Line.Information
PO_LINE_LOCATIONS_ALL            --.PO.Line.Shipment.Info
PO_DISTRIBUTIONS_ALL              --.PO.Accounting.Info

 

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of Quotation like (Incomplete, In process, Approved, Rejected Etc.)

----What are backend Column Status to know the matching status with Purchase Order?

Step 4: Create the Purchase Order (PO) in Oracle Apps
After receiving different quotations from different vendors company/person will go through the quotations
and approves the quotation that is best suited/reliable for the company/person.
Based on the approved quotation Purchase Order is generated to the vendor. Now the vendor will supply the item.

PO_HEADERS_INTERFACE             -- PO. Header. Interface Information

PO_LINES_INTERFACE                -- PO. Line Interface Information

PO_LINE_LOCATIONS_INTERFACE      -- PO. Shipment. Interface Information

PO_DISTRIBUTIONS_INTERFACE       -- PO. Distribution Interface Information

--Check Interface Success & Rejected Tables

 

 

PO_HEADERS_ALL                    --.PO. Header. Information
PO_LINES_ALL                      --.PO.Line.Information
PO_LINE_LOCATIONS_ALL            --.PO.Line.Shipment.Info
PO_DISTRIBUTIONS_ALL              --.PO.Accounting.Info

 

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of Purchase Order like (Incomplete, In process, Approved, Rejected Etc.)

----What are backend Column Status to know the matching status with Receipts or Invoice?

 

 

AP_SUPPLIERS_INT                  --.Supplier Interface .Info

AP_SUPPLIER_SITES_INT            --.Supplier   Site Interface .Info

AP_SUP_SITE_CONTACT_INT          --.Supplier Contact Interface .Info

AP_SUPPLIER_INT_REJECTIONS       --.Supplier Interface Rejection .Info

 

AP_SUPPLIERS (HZ_PARTY)          --.Supplier Header .Info

AP_SUPPLIER_SITES_ALL            --.Supplier Site.Info

AP_SUPPLIER_CONTACTS              --.Supplier Contact .Info

--Check the All TABLE JOINS Relationship

 

Step 5: Create the Receipts in Oracle Apps
Once the Item is shipped the vendor will raise the Receipt to the company/person.
RCV_HEADERS_INTERFACE            --.Receipt. Header Interface .Info
RCV_TRANSACTIONS_INTERFACE       --.Receipt. Transaction Interface .Info
--Check Interface Success & Rejected Tables

 

 

RCV_SHIPMENT_HEADERS              --.Receipt. Header .Info
RCV_SHIPMENT_LINES               --.Receipt.Line.Info
RCV_TRANSACTIONS                  --.Receipt.Transactions
RCV_ROUTING_HEADERS               --.Receipt.Routing.info

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of Receipts like(Goods Inspection Completed or not)

----What are backend Column Status to know the matching status with Invoice?

 

 

 

Step 6: Create the Invoice in Oracle Apps
Based on the receipt Invoice is generated. For this invoice the company/person will make Payments.
AP_INVOICES_INTERFACE                                   --Invoice Header Interface Information

AP_INVOICE_LINES_INTERFACE                    --Invoice Lines Interface Information

AP_INTERFACE_REJECTIONS         --Invoice Interface Rejection errors Information

AP_INTERFACE_CONTROLS            --Invoice Interface Control Information

 

AP_INVOICES_ALL                                                     --Invoice Header Information

AP_INVOICE_LINES_ALL                                      --Invoice Lines Information

AP_INVOICE_DISTRIBUTIONS_ALL                            --Invoice Header Interface Information

AP_INV_SELECTION_CRITERIA_ALL                          

AP_INVOICE_PREPAYS_ALL                                              --Invoice Prepayment Information

AP_PAYMENT_SCHEDULES_ALL                              

AP_INVOICE_PAYMENTS_ALL                                           --Invoice Payment Information

 

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of Invoice like (Invalidated, Validated, Accounted, Unaccounted, Paid, Unpaid, Partially Paid, Match with Prepayment or Debit memo or Credit Memo, Track as asset or not Transferred to GL or Not)

 

Step 7: Create the Payment in Oracle Apps

Based on Invoice the payment will generate. For this Payments the company/person will process further.

AP_CHECKS_ALL               -- Payment Information

AP_CHECK_STOCKS_ALL         -- Payment Information

 

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of Payment like (Accounted or UN accounted, Reconciled or not, Transferred to GL or not,)

 

Step 8:  Create Accounting

Based on the period closing process transfer all the transactions of invoice and payments to GL. (Daily, weekly, fortnightly, monthly).

 

 XLA_TRANSACTION_ENTITIES        -- Accounting Information

 XLA_DISTRIBUTION_LINKS                -- Accounting Information

 XLA_EVENTS                       -- Accounting Information

 XLA_AE_HEADERS                   -- Accounting Information

 XLA_AE_LINES                      -- Accounting Information

 

 --Check the All TABLE JOINS Relationship

 

Step 9: Transfer to General Ledger

After transferring from Sub Ledger to GL you can import all the transactions, validate, review and post the journals to update account balance.

 GL_INTERFACE                      -- GL Interface Information

 GL_INTERFACE_CONTROL             -- GL Interface control Information

 GL_INTERFACE_HISTORY             -- GL Interface history Information

 GL_IMPORT_REFERENCES             -- Sub Ledger & GL interlink Information

 

--Check the All TABLE JOINS Relationship

 

Step 10: Journal Import

 GL_JE_BATCHES                    -- GL Batches Information

 GL_JE_HEADERS                    -- GL Headers Information

 GL_JE_LINES                       -- GL Lines Information

 

--Check the All TABLE JOINS Relationship

--What are backend Column Status for different status of Journals like (Incomplete, In process, Approved, Rejected, Approval not required, Un posted, and Posted Etc.?)

 

 

 GL_JE_SOURCES                    -- GL Source Information

 GL_JE_CATEGORIES                 -- GL Categories Information

 GL_LEDGERS                       -- GL Ledger Information

 GL_CODE_COMBINATIONS            -- GL Code combination Information

 GL_CODE_COMBINATIONS_KFV        -- GL Code combination Information

 

Step 11: Posting

 GL_BALANCES                       -- GL Balance Information

 GL_PERIODS                       -- GL Period Information

 

--Check the All TABLE JOINS Relationship

 

 

 

GL_BUDGET_INTERFACE

--Check Interface Success & Rejected Tables

 

GL_BUDGETS

GL_BUDGET_ASSIGNMENTS

GL_BUDGET_ASSIGNMENT_RANGES

GL_BUDGET_BATCHES

GL_BUDGET_ENTITIES

GL_BUDGET_FROZEN_RANGES

GL_BUDGET_PERIOD_RANGES

GL_BUDGET_TYPES

GL_BUDGET_VERSIONS

GL_ENTITY_BUDGETS

--Check the All TABLE JOINS Relationship