Task:

How to find a AP invoice is validated or not through a SQL query

Script:
SELECT i.invoice_id,
       i.invoice_amount,
       DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(i.invoice_id, i.invoice_amount,i.payment_status_flag,i.invoice_type_lookup_code),
               'NEVER APPROVED', 'Never Validated',
               'NEEDS REAPPROVAL', 'Needs Revalidation',
               'CANCELLED', 'Cancelled',
               'Validated') INVOICE_STATUS
  FROM ap_invoices_all i
 WHERE i.invoice_num = '&Invoice_Number';

Notes:
Invoice Distributions are validated individually and the status is stored at AP_INVOICE_DISTRIBUTIONS_ALL.match_status_flag

Validated
- If ALL of the invoice distributions have a MATCH_STATUS_FLAG = 'A'
- If MATCH_STATUS_FLAG is 'T' on ALL the distributions and org has no encumbrance enabled then Invoice would show Validated (provided there is no Unreleased Hold)
Never Validated
- If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'
Needs Re-validation
- If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T' and the org has Encumbrance enabled
- If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed)
- If the invoice distributions have MATCH_STATUS_FLAG value = 'S' (stopped)
- If there are any rows in AP_HOLDS that do not have a release code

MATCH_STATUS_FLAG would remain 'T' if invoice has hold which does not allow Accounting.
As soon as Hold is released from Holds Tab/Invoice Workbench event status is set to 'U'.
Invoice is shown as Validated and accounting is allowed. Match_Status_Flag still remains 'T'.

AP_INV_APRVL_HIST_ALL table will give you all the details related to approval of particular invoice.

Query to get the latest transaction on particular invoice :

select created_by from AP_INV_APRVL_HIST_ALL

where invoice_id = &inv_id

and approval_history_id =

(select max(approval_history_id)   from AP_INV_APRVL_HIST_ALL 

   where invoice_id = &inv_id 

)

Query to get the Initiator of particular invoice :

select created_by from AP_INV_APRVL_HIST_ALL

where invoice_id = &inv_id

and approval_history_id =

(select min(approval_history_id) from AP_INV_APRVL_HIST_ALL 

 where invoice_id = &inv_id 

)

SELECT fcr.request_id,

DECODE(fcpt.user_concurrent_program_name,

'Report Set',

'Report Set:' || fcr.description,

fcpt.user_concurrent_program_name) CONC_PROG_NAME,

argument_text PARAMETERS,

NVL2(fcr.resubmit_interval,

'PERIODICALLY',

NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,

DECODE(NVL2(fcr.resubmit_interval,

'PERIODICALLY',

NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),

'PERIODICALLY',

'EVERY ' || fcr.resubmit_interval || ' ' ||

fcr.resubmit_interval_unit_code || ' FROM ' ||

fcr.resubmit_interval_type_code || ' OF PREV RUN',

'ONCE',

'AT :' ||

TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),

'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,

fu.user_name USER_NAME,

requested_start_date START_DATE

FROM apps.fnd_concurrent_programs_tl fcpt,

apps.fnd_concurrent_requests fcr,

apps.fnd_user fu,

apps.fnd_conc_release_classes fcrc

WHERE fcpt.application_id = fcr.program_application_id

AND fcpt.concurrent_program_id = fcr.concurrent_program_id

AND fcr.requested_by = fu.user_id

AND fcr.phase_code = 'P'

AND fcr.requested_start_date > SYSDATE

AND fcpt.LANGUAGE = 'US'

AND fcrc.release_class_id(+) = fcr.release_class_id

AND fcrc.application_id(+) = fcr.release_class_app_id;

SELECT

    *

FROM

    (

        SELECT

            'N' select_flag,

            'N' hide_show,

            xgl.name              ledger,

            xte.ledger_id,

            le.name               legal_entity,

            xte.legal_entity_id,

            xlk.meaning           event_status,

            xe.event_status_code,

            xe.process_status_code,

            xecl.name             event_class,

            xecl.event_class_code,

            xetl.name             event_type,

            xetl.event_type_code,

            xte.transaction_number,

            xe.creation_date      event_creation_date,

            xe.event_id,

            'N' retrieve_flag,

            NULL transaction_id1_prompt,

            NULL transaction_id1_value,

            NULL transaction_id2_prompt,

            NULL transaction_id2_value,

            NULL transaction_id3_prompt,

            NULL transaction_id3_value,

            NULL transaction_id4_prompt,

            NULL transaction_id4_value,

            NULL transaction_id5_prompt,

            NULL transaction_id5_value,

            NULL transaction_id6_prompt,

            NULL transaction_id6_value,

            NULL transaction_id7_prompt,

            NULL transaction_id7_value,

            NULL transaction_id8_prompt,

            NULL transaction_id8_value,

            NULL transaction_id9_prompt,

            NULL transaction_id9_value,

            NULL transaction_id10_prompt,

            NULL transaction_id10_value,

            xe.event_date         event_date,

            xe.event_number       event_number,

            xte.entity_code,

            DECODE(xe.event_status_code, 'U', DECODE(xe.process_status_code, 'E', 'Error', 'I', 'Error', 'NoError'), 'NoError') error_switch

            ,

            xte.source_id_int_1,

            xte.source_id_int_2,

            xte.source_id_int_3,

            xte.source_id_int_4,

            xte.source_id_char_1,

            xte.source_id_char_2,

            xte.source_id_char_3,

            xte.source_id_char_4,

            xte.security_id_int_1,

            xte.security_id_int_2,

            xte.security_id_int_3,

            xte.security_id_char_1,

            xte.security_id_char_2,

            xte.security_id_char_3,

            xte.valuation_method,

            xlk.lookup_code       display_status_code,

            xe.application_id,

            xe.on_hold_flag,

            xlk1.meaning          on_hold_status,

            xe.transaction_date,

            xe.transaction_date   transaction_date_from,

            xe.transaction_date   transaction_date_to

        FROM

            xla_events                 xe,

            xle_entity_profiles        le,

            xla_transaction_entities   xte,

            gl_ledgers                 xgl,

            xla_event_classes_tl       xecl,

            xla_event_types_tl         xetl,

            xla_lookups                xlk,

            xla_lookups                xlk1

        WHERE

            xe.entity_id = xte.entity_id

            AND xte.application_id = xe.application_id

            AND xte.ledger_id = xgl.ledger_id

            AND xte.legal_entity_id = le.legal_entity_id (+)

            AND xetl.application_id = xe.application_id

            AND xetl.event_type_code = xe.event_type_code

            AND xetl.language = userenv('LANG')

            AND xecl.application_id = xetl.application_id

            AND xecl.entity_code = xetl.entity_code

            AND xecl.event_class_code = xetl.event_class_code

            AND xecl.language = userenv('LANG')

            AND xlk.lookup_type = 'XLA_EVENT_DISPLAY_STATUS'

            AND xlk.lookup_code = DECODE(xe.event_status_code, 'I', 'I', 'N', 'N', 'P', 'A', DECODE(xe.process_status_code, 'U', 'U'

            , 'D', 'D', 'R', 'R', 'E'))

            AND xlk1.lookup_type = 'XLA_YES_NO'

            AND xlk1.lookup_code = xe.on_hold_flag

            AND xe.application_id = P_Application_Id 

            AND xgl.object_type_code = 'L'

            AND xgl.le_ledger_type_code = 'L'

            AND xgl.ledger_category_code IN (

                'NONE',

                'PRIMARY',

                P_Leder_Id

            )

    ) qrslt

WHERE

    ( entity_code = P_ENTITY_CODE

      AND ledger_id = P_ledger_id

      AND nvl(source_id_int_1, - 99) = P_source_id_int_1);

    

SELECT distinct 

    aia.invoice_id,

    aia.invoice_num,

    aia.invoice_date,

    aia.invoice_amount,

    aia.invoice_currency_code,

    aia.payment_currency_code,

    aia.gl_date,

    xah.period_name,

    aia.payment_method_code,

    xah.je_category_name,

    xev.event_id,

    xte.ENTITY_CODE

    ,xte.APPLICATION_ID

    ,xte.legal_entity_id

    ,source_id_int_1

    ,source_application_id

    ,source_id_int_2

    ,source_id_char_1

    ,gjh.status

FROM

    ap.ap_invoices_all             aia,

    xla.xla_transaction_entities   xte,

    xla.xla_events                 xev,

    xla.xla_ae_headers             xah,

    xla.xla_ae_lines               xal,

    gl_import_references           gir,

    gl_je_headers                  gjh,

    gl_je_lines                    gjl,

    gl_code_combinations           gcc

WHERE

    aia.invoice_id = xte.source_id_int_1

    AND xev.entity_id = xte.entity_id

    AND xah.entity_id = xte.entity_id

    AND xah.event_id = xev.event_id

    AND xah.ae_header_id = xal.ae_header_id

    AND xah.je_category_name = 'XX_CATEGORY_NAME'--Purchase Invoices

    AND xah.gl_transfer_status_code = 'Y'

    AND xal.gl_sl_link_id = gir.gl_sl_link_id

    AND gir.gl_sl_link_table = xal.gl_sl_link_table

    AND gjl.je_header_id = gjh.je_header_id

    AND gjh.je_header_id = gir.je_header_id

    AND gjl.je_header_id = gir.je_header_id

    AND gir.je_line_num = gjl.je_line_num

    and gjh.name='XX_JOURNAL_NAME'

    and aia.invoice_num='XX_INVOICE_NUM';

DECLARE

p_event_source_info xla_events_pub_pkg.t_event_source_info;

lv_valuation_method VARCHAR2(100) :=null;

lv_security_context xla_events_pub_pkg.t_security;

BEGIN

p_event_source_info.source_application_id := 200; --101 GL -- 602 XLE

p_event_source_info.application_id := 200; -- AR

p_event_source_info.legal_entity_id := 23273;

p_event_source_info.ledger_id := 2021;

p_event_source_info.entity_type_code := 'AP_INVOICES';

p_event_source_info.transaction_number := NULL;

p_event_source_info.source_id_int_1 := 138348; -- Customer Transaction ID;

p_event_source_info.source_id_int_2 := NULL;

p_event_source_info.source_id_int_3 := NULL;

p_event_source_info.source_id_int_4 := NULL;

p_event_source_info.source_id_char_1 := NULL;

p_event_source_info.source_id_char_2 := NULL;

p_event_source_info.source_id_char_3 := NULL;

p_event_source_info.source_id_char_4 := NULL;

xla_events_pub_pkg.delete_event

(p_event_source_info => p_event_source_info,

p_event_id => 154087, -- xla_events

p_valuation_method =>lv_valuation_method,

p_security_context => lv_security_context

);

DBMS_OUTPUT.put_line('Process completed');

commit;

exception when others then

DBMS_OUTPUT.put_line('Error:'||SQLERRM);

END;

/

** To Find Parameters used in API navigate to below page :

XLA Tables Details And Query For SLA API's

Below query can be used to add (any)responsibility from backend database:

BEGIN

FND_USER_PKG.ADDRESP (

'XX_USERNAME', — Username

'SYSADMIN', –application short name

'SYSTEM_ADMINISTRATOR', — Responsibility Key

'STANDARD', — Security Group Key

'Sysadmin Responsibility added due to work', –Description

sysdate, –Start date

null –End date

);

COMMIT;

DBMS_OUTPUT.put_line ('Responsibility Added Successfully');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (

' Responsibility is not added due to '

|| SQLCODE

|| SUBSTR (SQLERRM, 1, 100)

);

ROLLBACK;

END;

                   API - pay_element_entry_api.create_element_entry

set serveroutput on;

DECLARE

l_date date := '01-APR-2020'; /*Start date of element*/

x NUMBER := 0;

input_value_id2 number;

input_value_id3 number;

LV_ERROR_MSG VARCHAR2(240);

LD_EFFECTIVE_START_DATE DATE;

LD_EFFECTIVE_END_DATE DATE;

LN_ELEMENT_ENTRY_ID NUMBER;

LN_OBJECT_VERSION_NUMBER NUMBER;

LB_CREATE_WARNING BOOLEAN;

lb_delete_warning BOOLEAN;

Cursor CREATE_ELEMENT_CUR Is

    SELECT

    emd.emp_name,

    emd.employee_number,

    paaf.assignment_id,

    paaf.object_version_number,

    emd.person_id,

    emd.start_date,

    ead.end_date,

    emd.total_amt   march_da,

    ead.total_amt   april_da,

    emd.FIRST_NAME,

    emd.TIME_PERIOD_ID,

    emd.EMPLOYMENT_CATEGORY,

    ( ead.total_amt - emd.total_amt) difference_in_da,

    (select pelf.element_link_id

                  from  PAY_ELEMENT_LINKS_F PELF,pay_element_types_f petf2

                  where petf2.element_type_id        =  pelf.element_type_id

                  and  petf2.element_name           = 'Dearness Allowance Adjustment') element_link_id  /*Name of Element */

                   ,(select pivf2.input_value_id input_value_id1

                  from pay_input_values_f pivf2, pay_element_types_f petf2

                  where petf2.element_name = 'Dearness Allowance Adjustment'

                  AND pivf2.element_type_id = petf2.element_type_id

                  AND pivf2.NAME = 'Pay Value') input_value_id1 

FROM

    emp_mar_da   emd,

    emp_apr_da   ead,

    per_All_assignments_f paaf

WHERE

    emd.person_id = ead.person_id

    and emd.person_id=paaf.person_id

   AND sysdate BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)

;

BEGIN

FOR i in CREATE_ELEMENT_CUR 

         LOOP

/*Create custom table to get errors or success message and status if any as per requirements*/

         insert into XXEMP_UPD values(

         i.emp_name,

         i.PERSON_ID,

         i.EMPLOYMENT_CATEGORY,

         i.FIRST_NAME,

         i.EMPLOYEE_NUMBER,

         i.difference_in_da,

         i.START_DATE,

         i.END_DATE,

         null,

         null,

         i.march_da,

         i.april_da);

         input_value_id1 := i.input_value_id1;

            BEGIN

                  

                    pay_element_entry_api.create_element_entry 

                        ( -- Input data elements

                        -- -----------------------------

                        p_effective_date => l_date ,

                        P_BUSINESS_GROUP_ID => 83, -- Put BUSINESS_GROUP_ID as per client

                        P_ASSIGNMENT_ID => i.assignment_id, 

                        P_ELEMENT_LINK_ID => i.element_link_id,                                 --Link_id of User Declared Income Tax

                        p_entry_type => 'E', 

                        P_INPUT_VALUE_ID1 => i.input_value_id1,                                  --Input_Value_Id for entry_value1

                        P_ENTRY_VALUE1 => i.difference_in_da,

                        -- Output data elements

                        -----------------------------------

                        p_effective_start_date => ld_effective_start_date, 

                        p_effective_end_date => ld_effective_end_date, 

                        p_element_entry_id => ln_element_entry_id, 

                        p_object_version_number => ln_object_version_number, 

                        p_create_warning => lb_create_warning, 

                        P_OVERRIDE_USER_ENT_CHK => 'Y' );

                        x := x + 1;

                        update  XXEMP_UPD EMAU set EMAU.ELEMENT_STATUS = 'S' , EMAU.ERROR_MSG ='Success'

                        where i.person_id = EMAU.person_id;

            EXCEPTION WHEN OTHERS THEN

                LV_ERROR_MSG:=SUBSTR(SQLERRM,1,230);

                update XXEMP_UPD EMAU set EMAU.ELEMENT_STATUS = 'E' , EMAU.ERROR_MSG = LV_ERROR_MSG

                where i.person_id = EMAU.person_id;

                dbms_output.put_line(SQLERRM);

            END;

        END LOOP;

        COMMIT;

        dbms_output.put_line('Employees Processed: '||x);

EXCEPTION WHEN OTHERS THEN

    dbms_output.put_line(SQLERRM);

    COMMIT;

END;

/**Comment below for any clarification**/

we have to use frmcmp(Form Compiler) for compiling forms and libraries in Oracle Applications Version R12, But in 11i we need to use f60gen to compile forms and libraries which is deprecated in R12.

1) Login to application server.

2) Go to the directory $AU_TOP/forms/US

3) Place “.fmb” file in binary mode

4) Execute the below command to generate “.fmx”.

frmcmp_batch userid=apps/<apps_paswd> module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special

EXAMPLE:

frmcmp_batch userid=apps/apps module=XXFORM.fmb output_file=$AU_TOP/forms/US/XXFORM.fmx module_type=form batch=no compile_all=special