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!

AP Invoice Interface Doubts

Discussion in 'Oracle Apps Technical' started by jagadekara, Feb 3, 2015.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I am doing AP Invoice Interface in Oracle Apps R12.

    In that i have a doubt like below.

    There is one Invoice which have two lines.
    In my code that invoices got passed all validations at header level and in line level second line got failed in one validation. So what can I do?

    If I insert processed header and line, then after modifying second line and load again, that time my header level validations will fail. So what can I do now in this situation?
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    One more doubt.

    I need validation on Invoice Amount field. If user enters abd123 then it should goes to error. So how to check that field is numeric or not?
     
  3. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hi

    Below logic might help Amount field Validation .

    DECLARE

    l_var varchar2(10);
    l_amnt_flag VARCHAR2(1):='P';
    Begin

    l_var:='123.55';

    SELECT DECODE (TRANSLATE (l_var, '0123456789.,', ' '), NULL, 'P', 'E')
    INTO l_amnt_flag
    FROM DUAL;
    IF l_amnt_flag ='E' then
    DBMS_OUTPUT.PUT_LINE(l_amnt_flag);
    -- Raise custom exception
    END IF;
    DBMS_OUTPUT.PUT_LINE(l_amnt_flag);
    END;
     
  4. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    You mean to say Validation is from Staging table to Interface table? If Yes As per my knowledge any line got failed validation that invoice i.e both header and line should not process further. We could remember on delivery details interface to Legacy system for further processing . We had applied a logic as if any delivery line fails validation we skipped that Delivery i.e both header and line info from processing.
     
    ramprasad.cgs likes this.
  5. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula

    Thanks Hegde,

    I just create staging table with data type as number for that column.
    So while inserting data to that table it will reject the record.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks,

    Yes, the whole invoice should not process further.
     
  7. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    One more doubt.

    How to do validation on Payment Method?

    We have some custom payment methods. So those are not available in AP_LOOKUP_CODES. Only few values are there in look up table.

    So how can we validate Payment Method Column?

    Any Idea?
     
  8. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    You can create custom lookup for the custom payment methods.. I believe this will resolve your issue . But I am not 100% sure about this because I dont know the background of your interface requirement .
     
  9. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Got it. I got one select statement from Vendor Site OAF Page.
    There we can find Seeded Payment Methods as well as custom Payment Methods
     
  10. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I am loading AP Prepayment Invoices through Interface. I am not giving Prepayment type and settlement date, But system taking Sysdate as settlement date and Temporary as a Prepayment Type.

    Now I don't want Temporary Type, I need Permanent there. So how can I do that?

    In interface table I didn't find any field regarding Prepayment Type.
     
  11. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jagadekara,

    I think if settlement date is same as invoice date, then system defaults the type as 'Temporary'. Can you please try with different settlement date (future date for settlement ) and check if it works.
     
  12. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jagadekara,

    I analyzed the standard invoice form, and noticed that if earliest_settlement_date is NULL then system defaults prepayment_type_lookup_code to PERMANENT else TEMPORARY is defaulted.
     
  13. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Yowan Cristo,

    But I am not giving any earliest_settlement_date while inserting into interface tables. Even I am not able to find the column for that in interface tables.
     
  14. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula

    My Invoice date is 02-Feb-2015, but when I ran the payable open interface import program today, It creates the Prepayment Invoice with Temporary type and settlement date is sysdate (09-Feb-2015)
     
  15. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jagadekara,

    I have not tried AP Invoice Interface in R12 hence not sure of the interface table column for settlement date.

    However if you manually update the base table as below, it will automatically change the prepayment type to PERMANENT.

    Code (SQL):

    UPDATE  AP_INVOICES_ALL
    SET     earliest_settlement_date = NULL
    WHERE   invoice_id = <invoice_id>;
     
    In worst case if you are not finding respective column in interface table, after creation of prepayment invoice, you can manually update earliest_settlement_date to NULL to make Prepayment Type as Permanent.

    Please let me know if it is not working.

    Thanks,
    Yowan Cristo
     
  16. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Yowan,

    It is working fine. But I need it through Standard way.
    Because in some cases I need temporary type but for that I need user specified date instead of sysdate.
     
  17. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi Jagadekara,

    In that case, you can update the table with condition that if user input is not null then update with that value else update with NULL something like

    Code (SQL):

    UPDATE  AP_INVOICES_ALL
    SET     earliest_settlement_date = <USER provided earliest_settlement_date (NULL IF NOT provided) >
    WHERE   invoice_id = <invoice_id>;
     
     
  18. Oracleapps003

    Oracleapps003 Newly Initiated

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    New Jersey
    Hi,

    I have a requirement were we need to create prepayments from legacy system to Oracle EBS can we do it using Oracle Payables open interface ?

    Thanks.