API to cancel an AP invoice in R12

Declare
v_boolean               BOOLEAN;
v_error_code            VARCHAR2(100);
v_debug_info            VARCHAR2(1000);
begin

Oracle AP-SLA-GL Link Query

SELECT aia.INVOICE_ID "Invoice Id",

      aia.INVOICE_NUM "Invoice Number",

      aia.INVOICE_DATE "Invoice Date",

      aia.INVOICE_AMOUNT "Amount",

GL - AP - Payments

SELECT glp.start_date, gjh.je_header_id, gjh.doc_sequence_value voucher_no,

gjh.je_source, gjh.je_category, entity_code, gjh.period_name,

gjh.status, gjh.actual_flag, gjh.default_effective_date,

gjl.je_line_num, gjl.code_combination_id,

gjl.description voucher_desc, xal.accounted_dr debit,

xal.accounted_cr credit,

R12 GL XLA AP: SQL Statement to join the GL Entries with the AP Payments through the XLA tables

-------------------------AP Payments------------------------- 
SELECT glp.start_date, gjh.je_header_id, gjh.doc_sequence_value voucher_no,
gjh.je_source, gjh.je_category, entity_code, gjh.period_name,
gjh.status, gjh.actual_flag, gjh.default_effective_date,
gjl.je_line_num, gjl.code_combination_id,

API to Create Bank

 -- API to Create Bank

 

DECLARE    lc_output                VARCHAR2(3000);
   lc_msg_dummy     VARCHAR2(3000);
   lc_return_status     VARCHAR2(3000);
   lc_msg_data           VARCHAR2(3000);

   ln_bank_id             NUMBER;
   ln_msg_count        NUMBER;

API to Create External Bank Branch

 -- API to Create External Bank Branch

DECLARE      p_api_version    NUMBER                   := 1.0;
     p_init_msg_list  VARCHAR2(1)          := 'F';
     v_bank_id           NUMBER                   := 530705;
     x_return_status  VARCHAR2(2000);
     x_msg_count      NUMBER(5);
     x_msg_data        VARCHAR2(2000);

API to Create AP Supplier Site

Example --

 -- API to Create Supplier Site

DECLARE  l_vendor_site_rec      ap_vendor_pub_pkg.r_vendor_site_rec_type;
 lc_return_status         VARCHAR2(10);
 ln_msg_count            NUMBER;
 lc_msg_data               VARCHAR2(1000);
 ln_vendor_site_id     NUMBER;
 ln_party_site_id         NUMBER;
 ln_location_id            NUMBER; 

API to Create AP Supplier

 -- API to Create Supplier 

DECLARE    l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;
   l_return_status   VARCHAR2(10);
   l_msg_count       NUMBER;
   l_msg_data         VARCHAR2(1000);
   l_vendor_id        NUMBER;
   l_party_id           NUMBER; 

API to Create AP Supplier

 -- API to Create Supplier 

DECLARE    l_vendor_rec       ap_vendor_pub_pkg.r_vendor_rec_type;
   l_return_status   VARCHAR2(10);
   l_msg_count       NUMBER;
   l_msg_data         VARCHAR2(1000);
   l_vendor_id        NUMBER;
   l_party_id           NUMBER; 

R12 GL XLA AP: SQL to join the GL Entries with the AP Invoices through the XLA tables

-------------------------AP Invoice--------------------------
SELECT glp.start_date, gjh.je_header_id, gjh.doc_sequence_value voucher_no,
gjh.je_source, gjh.je_category, entity_code, gjh.period_name,
gjh.status, gjh.actual_flag, gjh.default_effective_date,
gjl.je_line_num, gjl.code_combination_id,
gjl.description voucher_desc, xal.accounted_dr debit,
xal.accounted_cr credit,

Query to find AP Invoices posted to GL in R12

SELECT DISTINCT

      AID.AMOUNT AMOUNT,

      ASP.SEGMENT1 SupplierNo,

      ASP.VENDOR_NAME SupplierName,

      AI.INVOICE_NUM Invoice

     FROM ap_invoices_all ai,

      ap_suppliers asp,

      ap_supplier_sites_all ass,

      ap_invoice_lines_all ail,

      ap_invoice_distributions_all aid,

      xla.xla_ae_headers xah,

      xla.xla_ae_lines xal,

      gl_code_combinations gcc,

      xla.xla_transaction_entities xte,

      xla_distribution_links xdl,

      gl_import_references gir,

      gl_je_headers gjh,

      gl_je_lines gjl

WHERE ai.invoice_id = ail.invoice_id

  AND ail.line_number = aid.invoice_line_number

  AND ai.invoice_id = aid.invoice_id

  AND xte.source_id_int_1 = ai.invoice_id

  AND xte.entity_code = 'AP_INVOICES'

  AND xte.entity_id = xah.entity_id

  AND xah.ae_header_id = xal.ae_header_id

  AND aid.dist_code_combination_id = gcc.code_combination_id

  AND gcc.code_combination_id = xal.code_combination_id

  AND xdl.ae_header_id = xah.ae_header_id

  AND xdl.ae_line_num = xal.ae_line_num

  AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id

  AND ai.vendor_id(+) = asp.vendor_id

  AND asp.vendor_id = ass.vendor_id

  AND ass.vendor_site_id = ai.vendor_site_id

  AND aid.amount != 0

  and xal.gl_sl_link_id = gir.gl_sl_link_id

  and gir.je_header_id = gjl.je_header_id

  and gir.je_line_num = gjl.je_line_num

  and gir.je_header_id = gjh.je_header_id

  AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AP INVOICE ACCOUTING DETAILS

SELECT
TRUNC(SYSDATE) REPORT_DATE,
'AP' TYPE,
XTE.TRANSACTION_NUMBER INVOICE_NUM,
PV.VENDOR_NAME NAME,
xal.ACCOUNTED_DR ACCOUNTED_DR,
xal.ACCOUNTED_CR ACCOUNTED_CR,
xal.ENTERED_DR,
xal.ENTERED_CR,
XAL.DESCRIPTION DESCRIPTION,
XAL.ACCOUNTING_DATE GL_DATE,
GCC.SEGMENT4 ACCOUNT_NUMBER,
gcc.SEGMENT2,
gcc.SEGMENT3,
gcc.SEGMENT7,
ffv.DESCRIPTION Account_Description,
HOU.NAME
FROM
XLA_AE_LINES XAL,
xla.xla_ae_headers xah,
xla.XLA_TRANSACTION_ENTITIES XTE,
xla.xla_events xe,
PO_VENDORS PV,
GL_CODE_COMBINATIONS GCC,
fnd_flex_values_vl ffv,
hr_operating_units hou
WHERE-- xte.APPLICATION_ID = 200
xte.ENTITY_ID = xe.ENTITY_ID
and xal.AE_HEADER_ID = xah.AE_HEADER_ID
and xe.EVENT_ID = xah.EVENT_ID
AND XAL.PARTY_ID = PV.VENDOR_ID
AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND XTE.SECURITY_ID_INT_1=HOU.ORGANIZATION_ID
and gcc.SEGMENT4 = ffv.FLEX_VALUE_MEANING

Breakdown by Supplier_Account

SELECT

TRUNC(SYSDATE) REPORT_DATE,

'AP' TYPE,

XAL.ACCOUNTING_DATE,

XTE.TRANSACTION_NUMBER INVOICE_NUM,

PV.VENDOR_NAME NAME,

xal.ACCOUNTED_DR ACCOUNTED_DR,

xal.ACCOUNTED_CR ACCOUNTED_CR,

xal.ENTERED_DR,

xal.ENTERED_CR,

XAL.DESCRIPTION DESCRIPTION,

GCC.SEGMENT2 ACCOUNT_NUMBER,

gcc.SEGMENT1,

gcc.SEGMENT3,

gcc.SEGMENT4,

ffv.DESCRIPTION Account_Description,

HOU.NAME

FROM

XLA_AE_LINES XAL,

xla.xla_ae_headers xah,

xla.XLA_TRANSACTION_ENTITIES XTE,

xla.xla_events xe,

PO_VENDORS PV,

GL_CODE_COMBINATIONS GCC,

fnd_flex_values_vl ffv,

hr_operating_units hou

WHERE-- xte.APPLICATION_ID = 200

xte.ENTITY_ID = xe.ENTITY_ID

and xal.AE_HEADER_ID = xah.AE_HEADER_ID

and xe.EVENT_ID = xah.EVENT_ID

AND XAL.PARTY_ID = PV.VENDOR_ID

AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND XTE.SECURITY_ID_INT_1=HOU.ORGANIZATION_ID

and gcc.SEGMENT2 = ffv.FLEX_VALUE_MEANING

and gcc.SEGMENT2 in ( '100801' ) --Account code

and FLEX_VALUE_SET_ID = 1014191 -- Account flex set id

and XAL.ACCOUNTING_DATE between

TO_DATE('01/01/2014','DD/MM/YYYY') and TO_DATE('31/03/2014','DD/MM/YYYY')

-- remove this line if you want full history

Script to update Supplier Bank Payment Method

Script to update Supplier Bank Payment Method

DECLARE
x_return_status VARCHAR2 (200) := NULL;
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (200) := NULL;
t_output VARCHAR2 (200) := NULL;
t_msg_dummy VARCHAR2 (200) := NULL;
l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type;
p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type;
--IBY_PAYMENT_METHODS_B.payment_method_code%TYPE :='CHECK';
p_ext_payee_id_tab_type iby_disbursement_setup_pub.ext_payee_id_tab_type;
--IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE:=12193;
i NUMBER := 0;
BEGIN
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;
--for xyz in abc
--loop
i := i + 1;
p_external_payee_tab_type (i).default_pmt_method := 'EFT';
p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';
p_external_payee_tab_type (i).exclusive_pay_flag := 'N';
p_external_payee_tab_type (i).payee_party_id := 167912;
p_ext_payee_id_tab_type (i).ext_payee_id := 17030;
--end loop
apps.fnd_global.apps_initialize (1774, 51308, 200);
mo_global.set_policy_context ('S', 305);
iby_disbursement_setup_pub.update_external_payee
(p_api_version => 1.0,
p_init_msg_list => 'T',
--fnd_api.g_true,
p_ext_payee_tab => p_external_payee_tab_type,
p_ext_payee_id_tab => p_ext_payee_id_tab_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_ext_payee_status_tab => l_payee_upd_status
);
DBMS_OUTPUT.put_line ('Return Status : ' || x_return_status);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);

IF x_return_status <> 'S'
THEN
IF x_msg_count > 0
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, x_msg_data, t_msg_dummy);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);
t_output := (TO_CHAR (i) || ': ' || x_msg_data);
END LOOP;
END IF;

DBMS_OUTPUT.put_line
( 'Error occured while updating the Payment Method'
|| t_output
);
END IF;

FOR j IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST
LOOP
DBMS_OUTPUT.put_line ( 'Error Message from table type : '
|| l_payee_upd_status (j).payee_update_msg
);
END LOOP;
END;

Script to update Supplier Bank Payment Method

DECLARE
x_return_status VARCHAR2 (200) := NULL;
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (200) := NULL;
t_output VARCHAR2 (200) := NULL;
t_msg_dummy VARCHAR2 (200) := NULL;
l_payee_upd_status iby_disbursement_setup_pub.ext_payee_update_tab_type;
p_external_payee_tab_type iby_disbursement_setup_pub.external_payee_tab_type;
--IBY_PAYMENT_METHODS_B.payment_method_code%TYPE :='CHECK';
p_ext_payee_id_tab_type iby_disbursement_setup_pub.ext_payee_id_tab_type;
--IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE:=12193;
i NUMBER := 0;
BEGIN
fnd_msg_pub.delete_msg (NULL);
fnd_msg_pub.initialize;
--for xyz in abc
--loop
i := i + 1;
p_external_payee_tab_type (i).default_pmt_method := 'EFT';
p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';
p_external_payee_tab_type (i).exclusive_pay_flag := 'N';
p_external_payee_tab_type (i).payee_party_id := 167912;
p_ext_payee_id_tab_type (i).ext_payee_id := 17030;
--end loop
apps.fnd_global.apps_initialize (1774, 51308, 200);
mo_global.set_policy_context ('S', 305);
iby_disbursement_setup_pub.update_external_payee
(p_api_version => 1.0,
p_init_msg_list => 'T',
--fnd_api.g_true,
p_ext_payee_tab => p_external_payee_tab_type,
p_ext_payee_id_tab => p_ext_payee_id_tab_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_ext_payee_status_tab => l_payee_upd_status
);
DBMS_OUTPUT.put_line ('Return Status : ' || x_return_status);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);

IF x_return_status <> 'S'
THEN
IF x_msg_count > 0
THEN
FOR i IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (i, fnd_api.g_false, x_msg_data, t_msg_dummy);
DBMS_OUTPUT.put_line ('Error Message : ' || x_msg_data);
t_output := (TO_CHAR (i) || ': ' || x_msg_data);
END LOOP;
END IF;

DBMS_OUTPUT.put_line
( 'Error occured while updating the Payment Method'
|| t_output
);
END IF;

FOR j IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST
LOOP
DBMS_OUTPUT.put_line ( 'Error Message from table type : '
|| l_payee_upd_status (j).payee_update_msg
);
END LOOP;
END;

Hold information based on the Invoice Number

When we purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (We receive BILL for the Items you have received). And payment will be done automatically. If there is some discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the Summary level.

SELECT *

  FROM (SELECT api.invoice_id, api.invoice_date AS invoice_date,

               api.invoice_num AS invoice_num, pov.vendor_id AS vendor_id,

               pov.vendor_name AS supplier_name,

               apd.inv_lines AS total_inv_lines,

               NVL (hold_tab_info.hold_inv_lines, 0) AS total_line_holds,

               NVL

                  (CEIL (  (hold_tab_info.hold_inv_lines * 100)

                         / DECODE (apd.inv_lines, 0, 1, apd.inv_lines)

                        ),

                   0

                  ) AS percentage_line_hold,

               DECODE (hold_tab_info.hold_inv_lines,

                       NULL, 'N',

                       0, 'N',

                       'Y'

                      ) AS defect,

               DECODE (hold_tab_info.hold_inv_lines,

                       NULL, 0,

                       0, 0,

                       1

                      ) AS defect_count,

               1 inv_count, NVL (hold_count.hold_cnt, 0) AS total_inv_holds,

               NVL (c.hold_os, 0) AS days_outstanding,

               NVL (api.invoice_amount, 0) AS total_invoice_amount,

               NVL (hold_tab_info.hold_amount, 0) AS total_hold_amount,

               NVL

                  (CEIL (  (hold_tab_info.hold_amount * 100)

                         / DECODE (api.invoice_amount,

                                   0, 1,

                                   api.invoice_amount

                                  )

                        ),

                   0

                  ) AS percentage_amount_hold

          FROM apps.ap_invoices_all api,

               (SELECT   invoice_id, COUNT (invoice_id) inv_lines

                    FROM apps.ap_invoice_distributions_all

                GROUP BY invoice_id) apd,

               (SELECT   invoice_id, COUNT (hold_lookup_code) hold_cnt

                    FROM apps.ap_holds_all

                   WHERE 1 = 1 AND line_location_id IS NOT NULL

                GROUP BY invoice_id) hold_count,

               (SELECT   invoice_id, COUNT (hold_tab.line_num) hold_inv_lines,

                         SUM (hold_tab.hold_amount) hold_amount

                    FROM (SELECT DISTINCT api.invoice_id invoice_id,

                                          apd.distribution_line_number

                                                                     line_num,

                                          apd.amount hold_amount

                                     FROM apps.ap_invoices_all api,

                                          apps.ap_invoice_distributions_all apd,

                                          apps.po_distributions_all pod,

                                          apps.ap_holds_all aph

                                    WHERE 1 = 1

                                      AND api.invoice_id = apd.invoice_id

                                      AND aph.invoice_id(+) = api.invoice_id

                                      AND api.cancelled_date IS NULL

                                      AND apd.po_distribution_id = pod.po_distribution_id(+)

                                      AND aph.line_location_id =

                                                          pod.line_location_id

                                      AND aph.line_location_id IS NOT NULL) hold_tab

                GROUP BY invoice_id) hold_tab_info,

               (SELECT   invoice_id, MAX (b.hold_os) hold_os

                    FROM (SELECT invoice_id,

                                 DECODE

                                    (status_flag,

                                     'R', (  TRUNC (NVL (last_update_date,

                                                         SYSDATE

                                                        )

                                                   )

                                           - TRUNC (hold_date)

                                      ),

                                     (TRUNC (SYSDATE) - TRUNC (hold_date))

                                    ) hold_os

                            FROM apps.ap_holds_all

                           WHERE line_location_id IS NOT NULL) b

                GROUP BY invoice_id) c,

               apps.po_vendors pov

         WHERE 1 = 1

           AND hold_tab_info.invoice_id(+) = api.invoice_id

           AND c.invoice_id(+) = api.invoice_id

           AND api.invoice_id = apd.invoice_id

           AND api.vendor_id = pov.vendor_id(+)

           AND api.cancelled_date IS NULL

           AND api.invoice_id = hold_count.invoice_id(+))

 WHERE invoice_num = 'Your Invoice number';