1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
anwarm.aziz

Custom ar api for uploading invoices and credit memos into interface 2011-12-13

Custom ar api for uploading invoices and credit memos into interface

  1. anwarm.aziz
    This is custom AR API for uploading invoices and credit memos into interface table after validation. More over it has procedure for creating credit memo from invoices and upload to interface table.

    Custom table as follows

    [highlight=sql]DROP SEQUENCE XXFIN_AR_CUSTOM_SEQ

    CREATE SEQUENCE XXFIN_AR_CUSTOM_SEQ
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    NOCYCLE
    NOCACHE;

    -- Create Custom table

    DROP TABLE XXFIN_AR_CUSTOM_API

    CREATE TABLE XXFIN_AR_CUSTOM_API AS
    SELECT
    XXFIN_AR_CUSTOM_SEQ.NEXTVAL SERIAL_NUMBER,
    ct.TRX_NUMBER,
    -- BATCH_SOURCE_NAME,
    rctl.INTERFACE_LINE_CONTEXT,
    rctl.INTERFACE_LINE_ATTRIBUTE1,
    rctl.INTERFACE_LINE_ATTRIBUTE2,
    ct.exchange_rate_type conversion_type,
    ct.exchange_date conversion_date,
    ct.exchange_rate conversion_rate,
    --SET_OF_BOOKS_ID,
    rctl.LINE_NUMBER,
    rctl.LINE_TYPE,
    rctl.DESCRIPTION,
    CT.INVOICE_CURRENCY_CODE CURRENCY_CODE,
    ct.CUST_TRX_TYPE_ID,
    --NULL CUST_TRX_TYPE_NAME,
    ct.TERM_ID,
    --NULL TERM_NAME,
    --NULL ORIG_SYSTEM_BILL_CUSTOMER_REF,
    --NULL ORIG_SYSTEM_BILL_CUSTOMER_ID,
    --NULL ORIG_SYSTEM_BILL_ADDRESS_ID,
    --NULL ORIG_SYSTEM_SHIP_CUSTOMER_ID,
    --NULL ORIG_SYSTEM_SHIP_ADDRESS_ID,
    --NULL ORIG_SYSTEM_SOLD_CUSTOMER_ID,
    CT.TRX_DATE TRX_DATE,
    APS.GL_DATE GL_DATE ,
    rctl.INVENTORY_ITEM_ID,
    RCTL.QUANTITY_INVOICED,
    RCTL.EXTENDED_AMOUNT AMOUNT,
    RCTL.UOM_CODE,
    CT.ORG_ID,
    APS.CLASS,
    APS.CUSTOMER_ID,
    APS.RAC_CUSTOMER_NAME CUSTOMER_NAME,
    APS.RAC_CUSTOMER_NUMBER CUSTOMER_NUMBER
    FROM
    ra_customer_trx_all ct,
    ra_customer_trx_lines_all rctl,
    AR_PAYMENT_SCHEDULES_V aps
    WHERE
    ct.customer_trx_id=rctl.customer_trx_id
    AND CT.CUSTOMER_TRX_ID= APS.CUSTOMER_TRX_ID
    AND ct.org_id=APS.ORG_ID
    AND ct.org_id=:p_org_id;
    /
    ALTER TABLE XXFIN_AR_CUSTOM_API ADD STATUS_FLAG VARCHAR2(1 BYTE);
    /
    ALTER TABLE XXFIN_AR_CUSTOM_API ADD ERR_MESSEGE VARCHAR2(2000 BYTE);
    /
    UPDATE XXFIN_AR_CUSTOM_API set STATUS_FLAG='I'[/highlight]