Oracle Apps R12 AP Invoice creation (Account Payables) from backend API, Validate, Approve

PROCEDURE FUJ_CRT_TICK_APINVOICE
(ERRBUF out varchar2, RETCODE  out varchar2
,P_PO_NUMBER VARCHAR2,P_GL_DATE VARCHAR2,P_PAYMENT_METHOD VARCHAR2
--,AMOUNT NUMBER
)
AS
L_VENDOR_ID  NUMBER;
L_VENDOR_SITE_ID NUMBER;
L_AMOUNT NUMBER;
v_request_id NUMBER;
l_boolean boolean;
l_phase varchar2(200);
l_status varchar2(200);
l_dev_phase varchar2(200);
l_dev_status varchar2(200);
l_message varchar2(200);
BEGIN

SELECT  VENDOR_ID, VENDOR_SITE_ID,POL.UNIT_PRICE
INTO L_VENDOR_ID , L_VENDOR_SITE_ID, L_AMOUNT
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL
WHERE SEGMENT1 = P_PO_NUMBER --'3696'
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
;

insert into AP_INVOICES_INTERFACE (
            invoice_id,
            invoice_num,
            vendor_id,
            vendor_site_id,
            invoice_amount,
            INVOICE_CURRENCY_CODE,
            invoice_date,
            DESCRIPTION,
           --PAY_GROUP_LOOKUP_CODE,
            source,
            org_id
            ,po_NUMBER
            ,PAYMENT_METHOD_CODE
                )
values (
             ap_invoices_interface_s.NEXTVAL,
            'TICK-'||ap_invoices_interface_s.CURRVAL,--P_PO_NUMBER
            L_VENDOR_ID ,
            L_VENDOR_SITE_ID ,
            100,--L_AMOUNT,--
            'AED',
            fnd_conc_date.string_to_date(P_GL_DATE),
            'This Invoice is created for ticket',
           -- 'WUFS SUPPLIER',
            'MANUAL INVOICE ENTRY',
            102
            ,P_PO_NUMBER
            ,P_PAYMENT_METHOD
);

insert into AP_INVOICE_LINES_INTERFACE (
            invoice_id,
            invoice_line_id,
            line_number,
            line_type_lookup_code,
            amount,
            DIST_CODE_COMBINATION_ID
            )
values     (
             ap_invoices_interface_s.CURRVAL,
             AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
            1,
            'ITEM',
            100,
            3362334
);


COMMIT;

 begin
         mo_global.init ('SQLAP');
 MO_GLOBAL.set_policy_context('S',102);
fnd_global.apps_initialize (2605,50579,200,0,0);
 FND_REQUEST.SET_ORG_ID(102);
            v_request_id :=fnd_request.submit_request (APPLICATION        => 'SQLAP',
                                                   PROGRAM            => 'APXIIMPT',
                                                   DESCRIPTION        => '',
                                                   START_TIME         => NULL,
                                                   SUB_REQUEST        => FALSE,
                                                   ARGUMENT1          => 102,
                                                   ARGUMENT2          =>'MANUAL INVOICE ENTRY',
                                                   ARGUMENT3          =>null,
                                                   ARGUMENT4          =>NULL,
                                                   ARGUMENT5          =>NULL,
                                                   ARGUMENT6          =>NULL,
                                                   ARGUMENT7          =>NULL,
                                                   ARGUMENT8          =>'N',
                                                   ARGUMENT9          =>'Y'                                            
                                                   );
        Commit;

IF v_request_id > 0
         THEN
            l_boolean :=
               FND_CONCURRENT.WAIT_FOR_REQUEST (v_request_id --request_id IN number default NULL,
                                                ,20 --Interval   IN number default 60, SECONDS
                                                ,0 --max_wait   IN number default 0,
                                                ,l_phase --phase      OUT varchar2,
                                                ,l_status --status     OUT varchar2,
                                                ,l_dev_phase --dev_phase  OUT varchar2,
                                                ,l_dev_status --dev_status OUT varchar2,,
                                                ,l_message --message    OUT varchar2) return boolean
                                                         );  
end if;                                                             
      fnd_file.put_line(fnd_file.log,'Please see the output of Payables OPEN Invoice Import program request id :'|| v_request_id);
      dbms_output.put_line('success');
  EXCEPTION
  WHEN OTHERS THEN
   fnd_file.put_line(fnd_file.log,'Error :'||sqlerrm);
   dbms_output.put_line('Error :'||sqlerrm);
   end;
  
    fnd_file.put_line(fnd_file.log,'SUCCESS');
   EXCEPTION
  WHEN OTHERS THEN
   fnd_file.put_line(fnd_file.log,'Error :'||sqlerrm);
   dbms_output.put_line('Error :'||sqlerrm);
 
   END;

 

 

---------Validating the AP invoice from backend by submitting concurrent program.

 

declare
v_request_id number;
        begin
 mo_global.init ('SQLAP');
 MO_GLOBAL.set_policy_context('S',443);
fnd_global.apps_initialize (26015,524410,200,0,0);
 FND_REQUEST.SET_ORG_ID(443);
            v_request_id :=fnd_request.submit_request (APPLICATION        => 'SQLAP',
                                                   PROGRAM            => 'APPRVL',
                                                   DESCRIPTION        => '',
                                                   START_TIME         => NULL,
                                                   SUB_REQUEST        => FALSE,
                                                   ARGUMENT1          => 443,
                                                   ARGUMENT2          =>'All',
                                                   ARGUMENT3          =>null,
                                                   ARGUMENT4          =>TO_CHAR(SYSDATE,'YYYY-MM-DD'),
                                                   ARGUMENT5          =>TO_CHAR(SYSDATE,'YYYY-MM-DD'),
                                                   ARGUMENT6          =>NULL,
                                                   ARGUMENT7          =>NULL,
                                                   ARGUMENT8          =>NULL, --or pass invoice id here remove dates
                                                   ARGUMENT9          =>NULL ,
                                                   ARGUMENT10          =>'N',
                                                   ARGUMENT11          =>1000                                            
                                                   );
        Commit;
dbms_output.put_line(v_request_id);        
end;
 

--checking invoice ap approval status --

apps.ap_invoices_pkg.get_approval_status (p_invoice_id,
                                                   NULL,
                                                   NULL,
                                                   NULL
                                                  );

--To approve AP Invoice from backend 

create or replace procedure xxap_inv_auto_approval(p_invoice_id IN NUMBER)
IS
   v_hist_id                 NUMBER;
   v_user_id                 NUMBER;
   v_login_id                NUMBER;
   v_user_name               VARCHAR2 (150)                  := 'SYSTEM-USER';
   v_validation_status       VARCHAR2 (50);
   v_stop_approval_result    BOOLEAN;
   v_hist_rec                ap_inv_aprvl_hist_all%ROWTYPE;
   v_org_id                  ap_invoices_all.org_id%TYPE;
   v_invoice_amount          ap_invoices_all.invoice_amount%TYPE;
   v_validation_request_id   ap_invoices_all.validation_request_id%TYPE;
   v_wfapproval_status       ap_invoices_all.wfapproval_status%TYPE;
BEGIN
   BEGIN
      SELECT org_id, invoice_amount, wfapproval_status
        INTO v_org_id, v_invoice_amount, v_wfapproval_status
        FROM ap_invoices_all
       WHERE invoice_id = p_invoice_id;

      SELECT user_id
        INTO v_user_id
        FROM fnd_user
       WHERE user_name = v_user_name;
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line (fnd_file.LOG,'Exception in first block-' || SQLERRM
                           );
         DBMS_OUTPUT.put_line ('Exception in first block-' || SQLERRM);
   END;

   BEGIN
      v_validation_status :=
         apps.ap_invoices_pkg.get_approval_status (p_invoice_id,
                                                   NULL,
                                                   NULL,
                                                   NULL
                                                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         fnd_file.put_line
                        (fnd_file.LOG,
                            'Exception retrieving invoice validation status-'
                         || SQLERRM
                        );
         DBMS_OUTPUT.put_line
                         (   'Exception retrieving invoice validation status-'
                          || SQLERRM
                         );
   END;

   IF (UPPER (v_validation_status) <> 'APPROVED')
   THEN
      fnd_file.put_line (fnd_file.LOG, 'Invoice is not validated');
      DBMS_OUTPUT.put_line ('Invoice is not validated');
   ELSE
      /*set values for manual approval*/
      IF (v_wfapproval_status = 'INITIATED')
      THEN
         v_stop_approval_result :=
            ap_workflow_pkg.stop_approval
                               (p_invoice_id,
                                NULL,
                                'INV_SUM_ACTIONS_WFAPPROVE.do_force_approval'
                               );

         --Bug5527190
         IF v_stop_approval_result = TRUE
         THEN
            fnd_file.put_line
                             (fnd_file.LOG,
                              'Invoice approval workflow is forcibly stopped'
                             );
            DBMS_OUTPUT.put_line
                              ('Invoice approval workflow is forcibly stopped');
         END IF;
      END IF;

      UPDATE ap_invoices_all
         SET wfapproval_status = 'MANUALLY APPROVED'
       WHERE invoice_id = p_invoice_id;

      UPDATE ap_invoice_lines_all
         SET wfapproval_status = 'MANUALLY APPROVED'
       WHERE invoice_id = p_invoice_id;

      --insert into the history table
      v_hist_rec.history_type := 'DOCUMENTAPPROVAL';
      v_hist_rec.invoice_id := p_invoice_id;
      v_hist_rec.iteration := 0;
      v_hist_rec.org_id := v_org_id;
      v_hist_rec.approver_name := v_user_name;
      v_hist_rec.amount_approved := v_invoice_amount;
      v_hist_rec.created_by := v_user_id;
      v_hist_rec.last_updated_by := v_user_id;
      v_hist_rec.last_update_login := -1;
      v_hist_rec.creation_date := SYSDATE;
      v_hist_rec.last_update_date := SYSDATE;
      v_hist_rec.response := 'MANUALLY APPROVED';
      v_hist_rec.approver_comments := 'System Auto Approved';
      ap_workflow_pkg.insert_history_table (v_hist_rec);
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (fnd_file.LOG,
                         'Exception in auto approval main block-' || SQLERRM
                        );
      DBMS_OUTPUT.put_line ('Exception in auto approval main block-' || SQLERRM);
END xxap_inv_auto_approval; 

Create PO requisition in R12 from backend and apps initialization for OU's.

1. Package to insert data into PO_REQUISITIONS_INTERFACE_ALL


CREATE OR REPLACE PACKAGE FUJ_EMP_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION(P_EMPLOYEE_NUMBER VARCHAR2) ;
END;

CREATE OR REPLACE PACKAGE BODY FUJ_EMP_TICKET_INTEG
IS
PROCEDURE POREQ_CREATION( P_EMPLOYEE_NUMBER VARCHAR2)
IS
CURSOR C1 IS

SELECT EMPLOYEE_NUMBER,
DECODE(INFO1,1,QUOT_AMOUNT1,
DECODE(INFO2,2,QUOT_AMOUNT2,
DECODE(INFO3,3,QUOT_AMOUNT3
)))AMOUNT
 FROM XXFUJ_TICKET_DETAILS
WHERE EMPLOYEE_NUMBER = P_EMPLOYEE_NUMBER

;

        BEGIN

FOR I IN C1 LOOP

INSERT INTO PO_REQUISITIONS_INTERFACE_ALL (INTERFACE_SOURCE_CODE,
                                           ORG_ID,
                                           DESTINATION_TYPE_CODE,
                                           AUTHORIZATION_STATUS,
                                           PREPARER_ID,
                                           --PREPARER_NAME,
                                           CHARGE_ACCOUNT_ID,
                                           SOURCE_TYPE_CODE,
                                           UNIT_OF_MEASURE,
                                           LINE_TYPE_ID,
                                           CATEGORY_ID,
                                           UNIT_PRICE,
                                           QUANTITY,
                                           DESTINATION_ORGANIZATION_ID,
                                           DELIVER_TO_LOCATION_ID,
                                           DELIVER_TO_REQUESTOR_ID,
                                           HEADER_DESCRIPTION,
                                           ITEM_DESCRIPTION,
                                           SUGGESTED_VENDOR_ID,
                                           SUGGESTED_VENDOR_SITE_ID,
                                           --ACCRUAL_ACCOUNT_ID ,
                                           --VARIANCE_ACCOUNT_ID,
                                           --BUDGET_ACCOUNT_ID,
                                           HEADER_ATTRIBUTE1,
                                           HEADER_ATTRIBUTE2,
                                           HEADER_ATTRIBUTE3,
                                           HEADER_ATTRIBUTE4,
                                           GL_DATE,
                                           REQUISITION_HEADER_ID                                         
                                           )
     VALUES ('IMPORT_EXP',                                  --Interface Source
             102,                                             --Operating Unit
             'EXPENSE',                                     --Destination Type
            'APPROVED',--'INCOMPLETE',       -- 'APPROVED',--                                     --Status
             26876,--6439,                      --This comes from per_people_f.person_id
            -- '???? ??? ????? ????? ?????????',
             106847,                                      --Code Combination ID
             'VENDOR',                                           --Source Type
             'Quantity',                                                     --UOM ok??yes
             1,                                           --Line Type of Goods
             1123,                                           --MISC.MISC Category
             I.AMOUNT,                                                       --Price
             1,                                                    --quantity
             108,--102                      --Represents Vision Operations Inv Org.
             142,--162,                                --Represents V1-New York City
             26876,--6439 ,                            --This is the Deliver to Requestor
             I.EMPLOYEE_NUMBER,                        --One Time Header Description
             I.EMPLOYEE_NUMBER,                        --One Time Item Description
             222,
             261,
             --17979,--Accrual
             --106847,--Varianlce
             --106847, --Budget
             'Y',--Header. ATT1
             'India',--Header 2
            'New Delhi',--Header 3
             I.EMPLOYEE_NUMBER,--Header 4
             TO_DATE('01-JAN-2017'),
              PO_REQUISITION_HEADERS_S.NEXTVAL
                           );
                           COMMIT;
/*
UPDATE  XXFUJ_TICKET_DETAILS
SET INFO4 = 'PR Created Success'
WHERE EMPLOYEE_NUMBER = I.EMPLOYEE_NUMBER;
COMMIT;
*/                         
END LOOP;


EXCEPTION WHEN OTHERS THEN NULL;

        END;
END;                                                        

2.to run concurrent program 'Requisition Import' from backend:

DECLARE
V_OUTPUT NUMBER;
BEGIN
         mo_global.init ('PO');
 MO_GLOBAL.set_policy_context('S',102);
fnd_global.apps_initialize (2605,50597,201,0,0);
 FND_REQUEST.SET_ORG_ID(102); --this is passed as operating unit
V_OUTPUT := fnd_request.submit_request
                        (
                         application      => 'PO',
                         program          => 'REQIMPORT',
                         description      => '',
                         start_time       => TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'),
                         sub_request      => FALSE,
                         argument1        => 'IMPORT_EXP',--Interface Source code,
                         argument2        => '',  ----APPS.SONA_BATCH_ID_S.CURRVAL,--Batch ID,
                         argument3        => 'BUYER',--Group By,
                         argument4        => '',--Last Req Number,
                         argument5        => 'N',--Multi Distributions,
                         argument6        => 'Y'
                          ) ;
DBMS_OUTPUT.PUT_LINE(   V_OUTPUT);
commit;
EXCEPTION WHEN OTHERS THEN
                      DBMS_OUTPUT.PUT_LINE( SQLERRM);
END;     

3. To view exceptions after the above concurrent program.

Run the concurrent program 'Requisition Import Exceptions Report '.