Summary Hold information based on the Invoice Number

AP Summary 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 (In other words 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';

Introduction of DYNAMIC SQL

Introduction of DYNAMIC SQL:-
------------------------------------

If we have to create some PROCEDURE, FUNCTION or PACKAGE and run them, then we have to do two steps.

1) Compile the code.
2) Execute the code which you have compiled.

Errors can occur at any above steps.

At the compile Time (Step 1), system will check for the syntax of the code and also if all the objects used in the code are exisiting in database or not.

If you want to hide the objects at the compile time so that you do not get the error message at the compile time (step 1) then use DYNAMIC SQL.

Difference between DBMS_SQL and EXECUTE IMMEDIATE
-----------------------------------------------------------------------

EXECUTE IMMEDIATE type of Dynamic SQL would not work in the old versions of Oracle Database like 10.7 or older then this version.

DBMS_SQL type of Dynamic SQL would work in all the version of Oracle Database.

                                             DYNAMIC SQL HOME

DBMS_SQL

DBMS_SQL:-
--------------

For the Select statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER;
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
BEGIN
--- Code converted in dynamic SQL start(phani).
L_SQL := 'select max(sal) from emp where deptno = :l_deptno';
L_CUR := dbms_sql.OPEN_CURSOR;
dbms_sql.PARSE( L_CUR, L_SQL, dbms_sql.NATIVE );
dbms_sql.BIND_VARIABLE( L_CUR, ':l_deptno', L_DEPTNO );
-- describe defines
dbms_sql.DEFINE_COLUMN( L_CUR, 1, L_SAL );
-- execute
L_RC := dbms_sql.EXECUTE( L_CUR );
LOOP
-- fetch a row
IF dbms_sql.FETCH_ROWS( L_CUR ) > 0 THEN
-- fetch columns from the row
dbms_sql.COLUMN_VALUE( L_CUR, 1, L_SAL );
ELSE
EXIT;
END IF;
END LOOP;
dbms_sql.CLOSE_CURSOR( L_CUR );
dbms_output.PUT_LINE( L_SAL );
--- Code converted in dynamic SQL end (phani).
END;

For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_empno NUMBER := 1;
g_ename VARCHAR2(30) := 'REDDY';
g_deptno NUMBER := 10;
BEGIN
--- Code converted in dynamic SQL start.
l_sql:= 'insert into emp
(EMPNO,
ENAME,
DEPTNO)
VALUES
( :p_empno,
:p_ename,
:p_deptno)';

l_cur := dbms_sql.open_cursor;
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);
dbms_sql.bind_variable(l_cur, ':p_empno', g_empno);
dbms_sql.bind_variable(l_cur, ':p_ename', g_ename);
dbms_sql.bind_variable(l_cur, ':p_deptno',g_deptno);
-- execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);
END;

For the Update Statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-

DECLARE
L_SQL VARCHAR2( 3000 );
L_CUR NUMBER;
L_RC NUMBER;
g_new_ename VARCHAR2(20) := 'REDDY01';
g_old_ename VARCHAR2(20) := 'REDDY';
BEGIN
--- Code converted in dynamic SQL start.

l_sql:= 'update emp
set ename = :p_new_ename
where ename = :p_old_ename';

l_cur := dbms_sql.open_cursor;
dbms_sql.parse(l_cur, l_sql, dbms_sql.native);

dbms_sql.bind_variable(l_cur, ':p_new_ename',g_new_ename);
dbms_sql.bind_variable(l_cur, ':p_old_ename',g_old_ename);

--- execute
l_rc := dbms_sql.execute(l_cur);
DBMS_SQL.CLOSE_CURSOR (l_cur);

--- Code converted in dynamic SQL end.
END;


                                             DYMANIC SQL-HOME

EXECUTE IMMEDIATE

EXECUTE IMMEDIATE:-
---------------------------

Note:- This type of Dynamic SQL would not work in the 10.7 version Database.

For the Select statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
----------

DECLARE
L_DEPTNO NUMBER DEFAULT 10;
L_SAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select max(sal) from emp
where deptno = :l_deptno'
INTO L_SAL
USING L_DEPTNO;
DBMS_OUTPUT.PUT_LINE(L_SAL);
END;

For the Insert statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
-----------

DECLARE
L_ENAME VARCHAR2(20) DEFAULT 'PHANI';
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO EMP(ENAME,EMPNO,DEPTNO) VALUES
(:L_ENAME,:L_EMPNO,:L_DEPTNO)'
USING L_ENAME,
L_EMPNO,
L_DEPTNO;
END;

For the Update Statement

Note:- I have created the sample Examples code based on the EMP and DEPT table in the scott schema.

Example:-
-----------

DECLARE
L_ENAME VARCHAR2(20) DEFAULT 'PHANI';
L_EMPNO NUMBER DEFAULT 2;
L_DEPTNO NUMBER DEFAULT 10;
BEGIN
EXECUTE IMMEDIATE 'UPDATE EMP
SET ENAME = ''RAHUL''
WHERE ENAME = :l_ENAME'
USING L_ENAME;
END;

DYMANIC SQL-HOME

Dynamic SQL

Dynamic SQL:-
----------------

INTRODUCTION

We got two ways for the Dynamic SQL.

1) EXECUTE IMMEDIATE

2) DBMS_SQL

Note:- Click on "EXECUTE IMMEDIATE or DBMS_SQL to check the sample Examples.

AP Module 12 Release New Features

This section is design to give all the information about the changes in the AP Module from 11i to Release 12.

Introduction

Suppliers Added to Trading Community Architecture

Invoice Lines

Centralized Banks and Bank Account Definitions in Oracle Cash Management

Document Sequencing of Payments

Integration with Oracle Payments for Funds Disbursement

Payment Configuration Controlled by Global Descriptive Flexfields

Integration with Oracle Subledger Accounting

Integration with Oracle E-Business Tax

Multiple Organizations Access Control

Multiple Organizations Access Control

Multiple Organizations Access Control:-
----------------------------------------------

Multiple Organizations Access Control is an enhancement to the Multiple
Organizations feature of Oracle Applications. Multiple Organizations Access Control
allows a user to access data from one or many Operating Units while within a given
responsibility. Data security is maintained using the Multiple Organizations Security
Profile, defined in Oracle HRMS, which specifies a list of operating units and
determines the data access privileges for a user.

In Release 12, several controls are moved from the Payables Options or Financials Options forms to a new setup form that is common for Oracle Payables across all
operating units, the Payables System Setup form. If the upgrade finds conflicts in the
settings across multiple operating units, it will choose the most frequently occurring
setting.

Oracle Applications will not automatically create security profiles during the Release 12
upgrade. If you want to use Multiple Organizations Access Control, you will first need
to define security profiles, then link them to responsibilities or users.