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 :

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;