API to update ap_invoice_lines_all in r12

API to update ap_invoice_lines_all in r12

In this post, we will be discussing about the API to update ap_invoice_lines_all in r12. If we have an requirement to update the ap invoice lines in r12, then we can use this below api. One important thing, i want to share, this ap invoice line update api is the internal api of the oracle and did not shared by oracle for general use. This api used in oracle r12 internally but if you have a Requirement to update ap_invoice_lines_all table in r12, then you can use this api 'AP_AIL_TABLE_HANDLER_PKG'. Here below is the complete plsql script to update ap_invoice_lines_all in r12.

Detail PLSQL Scrip Using API to update ap_invoice_lines_all in r12

Declare

ERROR_FLAG                               VARCHAR2(1);
ERROR_MSG                                VARCHAR2(2000);
V_ORG_ID NUMBER :=83;                              
V_LINE_TYPE                             AP_INVOICE_LINES_ALL.LINE_TYPE_LOOKUP_CODE%TYPE;
XX_AP_SEQ                                AP_INVOICES_ALL.INVOICE_ID%TYPE;
V_VENDOR_ID                             AP_INVOICES_ALL.VENDOR_ID%TYPE;
V_VENDOR_SITE_ID                        AP_INVOICES_ALL.VENDOR_SITE_ID%TYPE;
V_PAYMENT_METHOD_CODE                   AP_INVOICES_ALL.PAYMENT_METHOD_CODE%TYPE;
V_TERM_ID                               AP_INVOICES_ALL.TERMS_ID%TYPE;
V_INVENTORY_ITEM_ID                     AP_INVOICE_LINES_ALL.INVENTORY_ITEM_ID%TYPE;
V_UOM_CODE                              AP_INVOICE_LINES_ALL.UNIT_MEAS_LOOKUP_CODE%TYPE;
V_AP_INVOICE_DIS_ID                     AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
V_CODE_COMBINATION_ID                   GL_CODE_COMBINATIONS_KFV.CODE_COMBINATION_ID%TYPE;
V_PERIOD_NAME                           GL_PERIODS.PERIOD_NAME%TYPE;
V_SET_OF_BOOKS_ID                       GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
V_SOURCE                                AP_LOOKUP_CODES.LOOKUP_CODE%TYPE;
V_INVOICE_TYPE                          AP_LOOKUP_CODES.LOOKUP_CODE%TYPE;
L_USER_ID                                FND_USER.USER_ID%TYPE;
L_RESP_ID                                FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID%TYPE;
L_RESP_APPL_ID                           FND_RESPONSIBILITY_TL.APPLICATION_ID%TYPE;
V_PARTY_ID                              PO_VENDORS.PARTY_ID%TYPE;
V_PARTY_SITE_ID                         PO_VENDOR_SITES_ALL.PARTY_SITE_ID%TYPE;
V_LINE_NUMBER                           AP_INVOICE_LINES_ALL.LINE_NUMBER%TYPE;
V_DIS_NUMBER                            AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER%TYPE;
V_ROW_ID                                VARCHAR2(100);
V_INVOICE_ID                            AP_INVOICES_ALL.INVOICE_ID%TYPE;
V_DIST_CODE_COMBINATION_ID              GL_CODE_COMBINATIONS_KFV.CODE_COMBINATION_ID%TYPE;
V_PO_LINE_ID                            PO_LINES_ALL.PO_LINE_ID%TYPE;
V_LINE_LOCATION_ID                      PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID%TYPE;
V_PO_DISTRIBUTION_ID                    PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID%TYPE;
V_PO_HEADER_ID                          PO_HEADERS_ALL.PO_HEADER_ID%TYPE;

CURSOR UPD_INVOICE_LINES(INVOICE_ID NUMBER) IS
 SELECT ROWID ROW_ID,AIL.* FROM XX_AP_INVOICE_LINES_ALL  AIL
    WHERE INVOICE_ID=INVOICE_ID;

Begin

FOR L IN UPD_INVOICE_LINES (H.INVOICE_ID) LOOP

      V_LINE_NUMBER := V_LINE_NUMBER + 1;



     BEGIN
     SELECT INVENTORY_ITEM_ID
     INTO V_INVENTORY_ITEM_ID
     FROM MTL_SYSTEM_ITEMS_B
     WHERE SEGMENT1=L.ITEM
     AND ORGANIZATION_ID=V_ORG_ID;
     DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID IS VALID'||V_INVENTORY_ITEM_ID);
  EXCEPTION
  WHEN OTHERS THEN
  ERROR_FLAG:='E';
  ERROR_MSG:='INVENTORY_ITEM_ID  IS INVALID';
 DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID  IS INVALID'||V_INVENTORY_ITEM_ID);
  END;




        SELECT ROWID ROW_ID
         INTO V_ROW_ID
         FROM AP_INVOICE_LINES_ALL
         WHERE INVOICE_ID=(SELECT INVOICE_ID
     FROM AP_INVOICES_ALL WHERE INVOICE_NUM=H.INVOICE_NUM)
     AND LINE_NUMBER=V_LINE_NUMBER;



    BEGIN
    SELECT PO_LINE_ID
    INTO V_PO_LINE_ID
    FROM PO_LINES_ALL
    WHERE  PO_HEADER_ID=(SELECT  PO_HEADER_ID
    FROM
    PO_HEADERS_ALL WHERE SEGMENT1=H.PO_NUMBER)
    AND LINE_NUM=V_LINE_NUMBER;
     DBMS_OUTPUT.PUT_LINE('PO NUMBER DOES IS VALID'||H.PO_NUMBER);
    EXCEPTION
     WHEN OTHERS THEN
     ERROR_FLAG:='E';
     ERROR_MSG:='PO NUMBER DOES NOT EXITST';
    DBMS_OUTPUT.PUT_LINE('PO NUMBER DOES NOT EXITST'||H.PO_NUMBER);
     END;

API to update ap_invoice_lines_all in r12

    BEGIN
    SELECT LINE_LOCATION_ID
    INTO V_LINE_LOCATION_ID
     FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=
    (SELECT PO_LINE_ID FROM PO_LINES_ALL
    WHERE PO_HEADER_ID=
    (SELECT PO_HEADER_ID
   FROM PO_HEADERS_ALL
    WHERE SEGMENT1=H.PO_NUMBER) AND LINE_NUM=V_LINE_NUMBER);
    DBMS_OUTPUT.PUT_LINE(‘VALID PO NUMBER’||H.PO_NUMBER);
    EXCEPTION
     WHEN OTHERS THEN
     ERROR_FLAG:='E';
     ERROR_MSG:='PO NUMBER DOES NOT EXITST';
    DBMS_OUTPUT.PUT_LINE('PO NUMBER DOES NOT EXITST'||H.PO_NUMBER);
     END;



         BEGIN
             SELECT PO_DISTRIBUTION_ID
             INTO V_PO_DISTRIBUTION_ID
              FROM PO_DISTRIBUTIONS_ALL
           WHERE PO_LINE_ID=(SELECT PO_LINE_ID
         FROM PO_LINES_ALL
         WHERE PO_HEADER_ID=(SELECT PO_HEADER_ID
        FROM PO_HEADERS_ALL WHERE SEGMENT1=H.PO_NUMBER)
         AND LINE_NUM=V_LINE_NUMBER);
          DBMS_OUTPUT.PUT_LINE('VALID PO NUMBER’||H.PO_NUMBER);
        EXCEPTION
        WHEN OTHERS THEN
     ERROR_FLAG:='E';
     ERROR_MSG:='PO NUMBER DOES NOT EXITST';
    DBMS_OUTPUT.PUT_LINE('PO NUMBER DOES NOT EXITST'||H.PO_NUMBER);
     END;
           
    IF ERROR_FLAG!='E' THEN
      BEGIN
         AP_AIL_TABLE_HANDLER_PKG.UPDATE_ROW
 (P_ROWID                            =>V_ROW_ID
,P_INVOICE_ID                        =>V_INVOICE_ID
,P_LINE_NUMBER                       =>L.LINE_NUMBER
,P_LINE_TYPE_LOOKUP_CODE             =>L.LINE_TYPE
,P_LINE_GROUP_NUMBER                 =>NULL
,P_REQUESTER_ID                      =>NULL
,P_DESCRIPTION                       =>L.DESCRIPTION
,P_LINE_SOURCE                       =>'IMPORTED'
,P_ORG_ID                            =>V_ORG_ID
,P_INVENTORY_ITEM_ID                 =>V_INVENTORY_ITEM_ID
,P_ITEM_DESCRIPTION                  =>L.DESCRIPTION
,P_SERIAL_NUMBER                     =>NULL
,P_MANUFACTURER                      =>NULL
,P_MODEL_NUMBER                      =>NULL
,P_WARRANTY_NUMBER                   =>NULL
,P_GENERATE_DISTS                    =>'D'
,P_MATCH_TYPE                        =>'ITEM_TO_PO'
,P_DISTRIBUTION_SET_ID               =>NULL
,P_ACCOUNT_SEGMENT                   =>NULL
,P_BALANCING_SEGMENT                 =>NULL
,P_COST_CENTER_SEGMENT               =>NULL
,P_OVERLAY_DIST_CODE_CONCAT          =>NULL
,P_DEFAULT_DIST_CCID                 =>NULL
,P_PRORATE_ACROSS_ALL_ITEMS          =>NULL
,P_ACCOUNTING_DATE                   =>SYSDATE
,P_PERIOD_NAME                       =>V_PERIOD_NAME
,P_DEFERRED_ACCTG_FLAG               =>'N'
,P_DEF_ACCTG_START_DATE              =>NULL
,P_DEF_ACCTG_END_DATE                =>NULL
,P_DEF_ACCTG_NUMBER_OF_PERIODS       =>NULL
,P_DEF_ACCTG_PERIOD_TYPE             =>NULL
,P_SET_OF_BOOKS_ID                   =>V_SET_OF_BOOKS_ID
,P_AMOUNT                            =>L.LINE_AMOUNT
,P_BASE_AMOUNT                       =>NULL
,P_ROUNDING_AMT                      =>NULL
,P_QUANTITY_INVOICED                 =>L.QUANTITY_INVOICED
,P_UNIT_MEAS_LOOKUP_CODE             =>L.UNIT_MEASURE
,P_UNIT_PRICE                        =>L.UNIT_PRICE
,P_WFAPPROVAL_STATUS                 =>'NOT REQUIRED'
,P_DISCARDED_FLAG                    =>NULL
,P_ORIGINAL_AMOUNT                   =>NULL
,P_ORIGINAL_BASE_AMOUNT              =>NULL
,P_ORIGINAL_ROUNDING_AMT             =>NULL
,P_CANCELLED_FLAG                    =>NULL
,P_INCOME_TAX_REGION                 =>NULL
,P_TYPE_1099                         =>NULL
,P_STAT_AMOUNT                       =>NULL
,P_PREPAY_INVOICE_ID                 =>NULL
,P_PREPAY_LINE_NUMBER                =>NULL
,P_INVOICE_INCLUDES_PREPAY_FLAG      =>NULL
,P_CORRECTED_INV_ID                  =>NULL
,P_CORRECTED_LINE_NUMBER             =>NULL
,P_PO_HEADER_ID                      =>V_PO_HEADER_ID
,P_PO_RELEASE_ID                     =>NULL
,P_PO_LINE_LOCATION_ID               =>V_LINE_LOCATION_ID
,P_PO_DISTRIBUTION_ID                =>V_PO_DISTRIBUTION_ID
 ,P_PO_LINE_ID                       =>V_PO_LINE_ID
,P_RCV_TRANSACTION_ID                =>NULL
,P_FINAL_MATCH_FLAG                  =>NULL
,P_ASSETS_TRACKING_FLAG              =>'Y'
,P_ASSET_BOOK_TYPE_CODE              =>NULL
,P_ASSET_CATEGORY_ID                 =>NULL
,P_PROJECT_ID                        =>NULL
,P_TASK_ID                           =>NULL
,P_EXPENDITURE_TYPE                  =>NULL
,P_EXPENDITURE_ITEM_DATE             =>NULL
,P_EXPENDITURE_ORGANIZATION_ID       =>NULL
,P_PA_QUANTITY                       =>1
,P_PA_CC_AR_INVOICE_ID               =>NULL
,P_PA_CC_AR_INVOICE_LINE_NUM         =>NULL
,P_PA_CC_PROCESSED_CODE              =>NULL
,P_AWARD_ID                          =>NULL
,P_AWT_GROUP_ID                      =>NULL
,P_PAY_AWT_GROUP_ID                  =>NULL
,P_REFERENCE_1                       =>NULL
,P_REFERENCE_2                       =>NULL
,P_RECEIPT_VERIFIED_FLAG             =>NULL
,P_RECEIPT_REQUIRED_FLAG             =>NULL
,P_RECEIPT_MISSING_FLAG              =>NULL
,P_JUSTIFICATION                     =>NULL
,P_EXPENSE_GROUP                     =>NULL
,P_START_EXPENSE_DATE                =>NULL
,P_END_EXPENSE_DATE                  =>NULL
,P_RECEIPT_CURRENCY_CODE             =>NULL
,P_RECEIPT_CONVERSION_RATE           =>NULL
,P_RECEIPT_CURRENCY_AMOUNT           =>NULL
,P_DAILY_AMOUNT                      =>NULL
,P_WEB_PARAMETER_ID                  =>NULL
,P_ADJUSTMENT_REASON                 =>NULL
,P_MERCHANT_DOCUMENT_NUMBER          =>NULL
,P_MERCHANT_NAME                     =>NULL
,P_MERCHANT_REFERENCE                =>NULL
,P_MERCHANT_TAX_REG_NUMBER           =>NULL
,P_MERCHANT_TAXPAYER_ID              =>NULL
,P_COUNTRY_OF_SUPPLY                 =>NULL
,P_CREDIT_CARD_TRX_ID                =>NULL
,P_COMPANY_PREPAID_INVOICE_ID        =>NULL
,P_CC_REVERSAL_FLAG                  =>NULL
,P_CREATION_DATE                     =>NULL
,P_CREATED_BY                        =>L_USER_ID
,P_LAST_UPDATED_BY                   =>L_USER_ID
,P_LAST_UPDATE_DATE                  =>SYSDATE
,P_LAST_UPDATE_LOGIN                 =>NULL
,P_PROGRAM_APPLICATION_ID            =>NULL
,P_PROGRAM_ID                        =>NULL
,P_PROGRAM_UPDATE_DATE               =>NULL
,P_REQUEST_ID                        =>NULL
,P_ATTRIBUTE_CATEGORY                =>NULL
,P_ATTRIBUTE1                        =>NULL
,P_ATTRIBUTE2                        =>NULL
,P_ATTRIBUTE3                        =>NULL
,P_ATTRIBUTE4                        =>NULL
,P_ATTRIBUTE5                        =>NULL
,P_CALLING_SEQUENCE                  =>'1');


        DBMS_OUTPUT.PUT_LINE(' LINES  updated..: ');
        COMMIT;
         EXCEPTION
          WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('sql error is :'||SQLERRM);
            DBMS_OUTPUT.PUT_LINE('sql code error is :'||SQLCODE);
      END;

API to update ap_invoice_lines_all in r12