Oracle APPS R12 Query to find PAYMENT AGAINST INVOICE

SELECT b.segment1 vendor_number,
       b.vendor_name vendor_name,
       c.vendor_site_code,
       c.pay_group_lookup_code,
       a.invoice_num invoice_number,
       a.invoice_date,
       a.gl_date,
       d.due_date,
       a.invoice_currency_code,
       a.invoice_amount,
       a.amount_paid,
       a.pay_group_lookup_code,
       d.payment_priority,
       (SELECT MAX (check_date)
          FROM ap_checks_all aca, ap_invoice_payments_all aip
         WHERE aca.CHECK_ID = aip.CHECK_ID AND aip.invoice_id = a.invoice_id)
          "Last Payment Made on",
          a.cancelled_date
  FROM apps.ap_invoices_all a,
       apps.ap_suppliers b,
       apps.ap_supplier_sites_all c,
       apps.ap_payment_schedules_all d,
       apps.ap_invoice_payments_all ap,
       ap_checks_all ac
WHERE     a.vendor_id = b.vendor_id
       AND a.vendor_site_id = c.vendor_site_id
       AND b.vendor_id = c.vendor_id
       AND a.invoice_id = d.invoice_id
       AND ap.invoice_id = a.invoice_id
       AND ac.CHECK_ID = ap.CHECK_ID
       and ac.STATUS_LOOKUP_CODE <> 'VOIDED'
       AND a.org_id = 89
       and a.invoice_id= 1234
       AND a.pay_group_lookup_code IN ('DISTRIBUTOR')
       AND ac.check_date BETWEEN TO_DATE ('01-Apr-2014', 'DD-MON-YYYY') AND TO_DATE ('30-Jun-2014 23:59:59', 'DD-MON-YYYY HH24:MI:SS')


Thanks
Amar Alam

Collection Methods in PLSQL

Collection Methods:

PL/SQL collections contain a number of built-in methods that prove useful when working with them. Here is some of them.

COUNT:
This method returns the number of elements in the collection.
Example:

declare
Type book is VARRAY(2) OF VARCHAR2(60);
book_tab book;
begin
book_tab:=book('The Angel','Hope on your way');
dbms_output.put_line('Number of elements in the Varray is 'book_tab.count());
end;

Output:
Number of elements in the Varray is 2

EXISTS:

Ø Returns Boolean true if element at specified index exists
Ø Returns Boolean false if element at specified index does not exists


Example:

DECLARE
TYPE numlist IS TABLE OF INTEGER;
n numlist := numlist (1, 3, 5, 7);
BEGIN
IF n.EXISTS (1)
THEN
DBMS_OUTPUT.put_line ('First element exists ');
ELSE
DBMS_OUTPUT.put_line ('First element Not exists');
END IF;

IF n.EXISTS (5)
THEN
DBMS_OUTPUT.put_line ('Fifth element exists ');
ELSE
DBMS_OUTPUT.put_line ('Fifth element Not exists');
END IF;
END;


Output:
First element exists
Fifth element Not exists

EXTEND :

Increases size of Collection by 1 or number specified
Note: Cannot use with Associative Array.
Ø EXTEND appends one null element to a collection.
Ø EXTEND(n) appends n null elements to a collection.
Ø EXTEND(n,i) appends n copies of the ith element to a collection.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
n.EXTEND(3); -- Add 3 new elements at the end.
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');
n.EXTEND; 
DBMS_OUTPUT.PUT_LINE ('Now there are ' n.COUNT ' elements in N.');

END;

Output:
There are 4 elements in N.
Now there are 7 elements in N.
Now there are 8 elements in N.


FIRST and LAST:

Ø FIRST-Navigates to the first element in the Collection.
Ø LAST -Navigates to last element in the Collection.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE('N''s first subscript is ' n.FIRST);
DBMS_OUTPUT.PUT_LINE('N''s last subscript is ' n.LAST);
END;

Output:
N's first subscript is 1.
N's last subscript is 4.



PRIOR and NEXT:

Ø PRIOR -Navigates to the previous element.
Ø NEXT -Navigates to the next element.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
DBMS_OUTPUT.PUT_LINE('The element after #2 is #' n.NEXT(2));
DBMS_OUTPUT.PUT_LINE('The element before #2 is #' n.PRIOR(2));
END;

Output:
The element after #2 is 3
The element before #2 is 1

TRIM:

Removes the last element, or the last n elements if a number is specified.

Ø TRIM removes one element from the end of a collection.
Ø TRIM(n) removes n elements from the end of a collection.

Note: Cannot use with Associative Array.

Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,2,3,5,7,11);
BEGIN
n.TRIM(2); -- Remove last 2 elements.
DBMS_OUTPUT.PUT_LINE('There are ' n.COUNT ' elements in N.');
n.TRIM; -- Remove last element.
DBMS_OUTPUT.PUT_LINE ('There are ' n.COUNT ' elements in N.');
END;

Output:
There are 4 elements in N.
There are 3 elements in N.


DELETE:

Removes all elements of a Collection, or the nth element, if a parameter is specified.

Ø DELETE removes all elements from a collection.
Ø DELETE(n) removes the nth element from an associative array or nested table.
Ø DELETE(n) does nothing,if n is null,
Ø DELETE(m,n) removes all elements in the range m..n from an associative array or nested table.
Ø DELETE(m,n) does nothing,if m is larger than n or if m or n is null,


Example:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(10,20,30,40,50,60,70,80,90,100);
BEGIN
n.DELETE(2); -- deletes element 2
n.DELETE(3,6); -- deletes elements 3 through 6
n.DELETE(7,7); -- deletes element 7
n.DELETE(6,3); -- does nothing since 6 > 3
n.DELETE; -- deletes all elements
END;


Thanks
Amar Alam

How to enable the “Operating Unit” Parameter for a Concurrent Program in Oracle Apps R12

We notice that the new field as 'Operating Unit' added in R12 and this is by default is in disabled mode:

An applications responsibility can access multiple OUs. Some concurrent programs have been enhanced to process multiple operating units simultaneously, while for other requests the operating unit must be specified when you run the program.

To support this, concurrent programs are defined with an operating unit mode of

'S' for single operating unit and 'M'(concurrent program will be in this mode by default) for multiple operating units.

If the 'Operating Unit Mode' is not set for the concurrent program it will fail.

The ‘Operating Unit’ field/parameter is known as ‘Reporting Context’ in MOAC(Multi Org Access Control) terminology.

How we can achieve from the Oracle Applications Front-End.

1. Login into application with System Administration responsibility (NOT System
     Administrator)
2. Navigate: Concurrent è Programs

3. Query for Short Name or Program Name of the concurrent program like as below.

4. Click on Update icon of your program.

5. Under 'Update Concurrent Program' region, select Request tab
6. Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit Mode' field.
7. Save changes by clicking on 'Apply' button.

8. Change responsibility where the ‘Concurrent Program’ can be run, in this example ‘TESTOU’ is assigned to ‘AR Super User’ responsibility                              

9. Select the ‘Concurrent Program’ from the ‘SRS Window’, now you can see that ‘Operating 

    Unit’ field is enabled and you can see the Operating Units in the LOV.

 

10. This particular selected Operating Unit value can be accessed using the standard MOAC API:MO_GLOBAL.GET_CURRENT_ORG_ID

Thanks

Amar Alam