AP Payments Drilldown

SELECT  DISTINCT
    gjh.je_source source,
    gjh.je_category category,
    gjb.name journal,
    gjl.je_line_num line_num,
    gjh.actual_flag actual_flag,
    gjh.period_name period,
    gjl.effective_date gl_date,
    gcc.segment1 fund,
    gcc.segment2 FUNCTION,
    gcc.segment3 object,
    gcc.segment4 sub_object,
    gcc.segment5 org,
    gcc.segment6 yr,
    gcc.segment7 program,
    gjl.entered_dr debit,
    gjl.entered_cr credit,
    Flv.displayed_field type,
    NULL purchase_document_num,
    pv.vendor_name vendor,
    aia.invoice_num,
    aid.distribution_line_number,
    aca.CHECK_NUMBER check_num,
    to_date(aca.check_date) trx_date
    FROM
    ap_payment_history_all aph,
    ap_payment_hist_dists phd,
    xla_distribution_links xdl,
    ap_invoice_distributions_all aid,
    ap_invoices_all aia,
    po_vendors pv,
    ap_checks_all aca,
    xla.xla_ae_lines xal,
    xla.xla_ae_headers xah,
    gl_import_references gir,
    gl_je_lines gjl,
    gl_je_headers gjh,
    gl_je_batches gjb,
    gl_code_combinations gcc,
    ap_lookup_codes flv
    WHERE
    phd.payment_history_id             = aph.payment_history_id
    AND xdl.source_distribution_type     = 'AP_PMT_DIST'
    AND xdl.source_distribution_id_num_1 = payment_hist_dist_id
    AND NVL("SOURCE_DISTRIBUTION_ID_NUM_2",(-99))= -99
    AND phd.invoice_distribution_id      = aid.invoice_distribution_id
    AND aid.invoice_id                   = aia.invoice_id
    AND aia.vendor_id                    = pv.vendor_id
    AND aca.check_id                     = aph.check_id
    AND xal.ae_header_id                 = xdl.ae_header_id
    AND xal.ae_line_num                  = xdl.ae_line_num
    AND xal.application_id               = xdl.application_id
    AND xal.ae_header_id                 = xah.ae_header_id
    AND xal.ae_header_id                 = xah.application_id
    AND gir.gl_sl_link_id                = xal.gl_sl_link_id
    AND gir.gl_sl_link_table             = xal.gl_sl_link_table
    AND gjl.je_header_id                 = gir.je_header_id
    AND gjl.je_line_num                  = gir.je_line_num
    AND gjh.je_header_id                 = gir.je_header_id
    AND gjh.je_category                  = 'Payments'
    AND gjh.actual_flag                  = 'A'
    AND gjh.status                       = 'P'
    AND gjl.status                       = 'P'
    AND gjb.je_batch_id                  = gjh.je_batch_id
    AND gjl.code_combination_id          = gcc.code_combination_id
    AND flv.lookup_type                  = 'PAYMENT METHOD'
    AND lookup_code                      = aca.payment_method_lookup_code;
    

AP to GL Drilldown (for Invoices)

Reference Note: Doc ID 605707.1



SELECT
  gjh.je_source source,
  gjh.je_category category,
  gjb.name journal,
  gjl.je_line_num line_num,
  gjh.actual_flag actual_flag,
  gjh.period_name period,
  gjl.effective_date gl_date,
  gcc.segment1 fund,
  gcc.segment2 FUNCTION,
  gcc.segment3 object,
  gcc.segment4 sub_object,
  gcc.segment5 org,
  gcc.segment6 yr,
  gcc.segment7 program,
  DECODE(SIGN(aid.amount),    -1,0,aid.amount) debit,
  DECODE(SIGN(aid.amount),1,0,-aid.amount) credit,
  pha.segment1 purchase_document_num,
  to_number(NULL) purchase_line_num,
  to_number(NULL) shipment_num,
  to_number(NULL) purchase_distribution_num,
  pv.vendor_name vendor,
  ai.invoice_num invoice_num,
  to_number(aid.distribution_line_number) invoice_distribution_num,
  to_number(NULL) check_num,
  aid.expenditure_type,
  hou.name expenditure_org,
  to_date(ai.invoice_date) trx_date
FROM
  apps.gl_je_headers gjh,
  apps.gl_je_lines gjl,
  apps.gl_je_batches gjb,
  apps.gl_import_references gir,
  xla.xla_ae_lines xal,
  xla.xla_ae_headers xah,
  gl_code_combinations gcc,
  xla_distribution_links xdl,
  ap_invoice_distributions_all aid,
  ap_invoice_lines_all ail,
  ap_invoices_all ai,
  po_headers_all pha,
  apps.ap_suppliers pv ,
  hr_all_organization_units hou,
  GL_PERIOD_STATUSES ps
WHERE
  gjh.je_category                = 'Purchase Invoices'
AND gjh.je_source                = 'Payables'
AND gjb.status                   = 'P'
AND gjh.status                   = 'P'
AND gjl.status                   = 'P'
AND gjh.actual_flag              = 'A'
AND gjb.JE_BATCH_ID              = gjh.JE_BATCH_ID
AND gjh.je_header_id             = gjl.je_header_id
AND gjl.CODE_COMBINATION_ID      = gcc.CODE_COMBINATION_ID
AND gjl.je_header_id             = gir.je_header_id
AND gjl.je_line_num              = gir.je_line_num
AND gir.gl_sl_link_id            = xal.gl_sl_link_id
AND gir.gl_sl_link_table         = xal.gl_sl_link_table
AND xal.application_id           = 200
AND xal.ae_header_id             = xah.ae_header_id
AND xah.application_id           = 200
AND xal.application_id           = xah.application_id
AND xal.ae_header_id             = xdl.ae_header_id
AND xal.ae_line_num              = xdl.ae_line_num
AND xdl.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
  --AND gjh.period_name = 'SEP-14'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 = aid.invoice_distribution_id
AND aid.invoice_id                   = ail.invoice_id
AND aid.invoice_line_number          = ail.line_number
AND ai.invoice_id                    = ail.invoice_id
AND pha.po_header_id (+)             = ail.po_header_id
AND ai.vendor_id                     = pv.vendor_id
AND aid.expenditure_organization_id  = hou.organization_id(+)
AND gjl.effective_date BETWEEN ps.start_date AND ps.end_date
  -- change start
  -- user using date range instead of period to retrieve records
AND ps.application_id = 101
AND gjh.period_name   = ps.period_name
AND PS.start_date     = to_date(TO_CHAR(gjl.effective_date, 'MON-YYYY'),
  'MON-YYYY')
AND ps.end_date = TRUNC(last_day(gjl.effective_date))
--
and ps.period_name = 'SEP-14';

AP to GL Drilldown (Encumbrance Transactions)

SELECT
  gjh.je_source source,
  gjh.je_category category,
  gjb.name batch_name,
  gjl.je_line_num line_num,
  gjh.actual_flag actual_flag,
  gjh.period_name period,
  gjl.effective_date gl_date,
  gcc.segment1 fund,
  gcc.segment2 FUNCTION,
  gcc.segment3 object,
  gcc.segment4 sub_object,
  gcc.segment5 org,
  gcc.segment6 yr,
  gcc.segment7 program,
  DECODE(SIGN(aid.amount),    -1,0,aid.amount) debit,
  DECODE(SIGN(aid.amount),1,0,-aid.amount) credit,
  gjh.je_category type,
  pha.segment1 purchase_document_num,
  pv.vendor_name,                                                   
  aia.invoice_num invoice_num,                                      
  TO_NUMBER(AID.DISTRIBUTION_LINE_NUMBER) invoice_distribution_num, 
  aid.expenditure_type,
  to_date(aia.invoice_date) trx_date
FROM
  apps.gl_je_batches jb,
  apps.gl_je_headers gjh,
  apps.gl_je_lines gjl,
  apps.gl_je_batches gjb,
  apps.gl_import_references gir,
  gl_code_combinations gcc,
  ap_encumbrance_lines_all ael ,
  ap_invoice_distributions_all Aid, 
  po_headers_all pha,
  po_distributions_all pda,
  po_vendors pv,
  ap_invoices_all aia,
  gl_period_statuses ps
WHERE
  jb.je_batch_id                    = gjh.je_batch_id
AND gjh.je_header_id                = gjl.je_header_id
AND gjl.je_header_id                = gir.je_header_id (+)
AND gjh.je_batch_id                 = gjb.je_batch_id
AND gjl.je_line_num                 = gir.je_line_num (+)
AND gjh.actual_flag                 = 'E'
AND gjh.je_source                   = 'Payables'
AND gjh.je_category                 = 'Purchase Invoices'
AND gjb.status                      = 'P'
AND gjh.status                      = 'P'
AND gjl.status                      = 'P'
AND gcc.code_combination_id         = gjl.code_combination_id
AND gir.gl_sl_link_id               = ael.gl_sl_link_id (+)
AND ael.invoice_distribution_id     = aid.invoice_distribution_id (+)
AND aid.po_distribution_id          = pda.po_distribution_id (+)
AND pda.po_header_id                = pha.po_header_id (+)
AND aid.invoice_id                  = aia.invoice_id (+)
AND aia.vendor_id                   = pv.vendor_id (+)
  -- change start
  -- user uses date range rather than period to run the report
AND ps.application_id = 101
AND gjh.period_name   = ps.period_name
AND PS.start_date     = to_date(TO_CHAR(gjl.effective_date, 'MON-YYYY'),
  'MON-YYYY')
AND ps.end_date = TRUNC(last_day(gjl.effective_date));
--and ps.period_name = 'SEP-14';

R12 Multi Org Access Control (MOAC) Reporting Notes

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

// Context Initialization Procedure for MOAC in Database Session

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

-- Author: Faraz Anwar

-- Dated : 2010/11/3

//For Multiple Org Context Initialization in R12

BEGIN

MO_GLOBAL.init('AR');

mo_global.set_policy_context('M', 115);

--username, responsibility_id, resp_application_id, security_profile_id, server_id

FND_GLOBAL.APPS_INITIALIZE(1116,50651,222,62,7118);

END;

//For Single Org Context Initialization in R12

BEGIN

MO_GLOBAL.init('AR');

mo_global.set_policy_context('S', 115);

FND_GLOBAL.APPS_INITIALIZE(1116,50651,222,62,7118);

END;

 

// Important Procedures

-- This procedure only works with Security Profiles. MO Operating Unit is not Affected with this procedure

MO_UTILS.check_org_in_sp(p_organization_id, p_class);   

e.g.,

SELECT organization_id, name, MO_UTILS.check_org_in_sp(ORGANIZATION_ID, 'OPERATING_UNIT') accessable FROM HR_OPERATING_UNITS;

-- Returns Y or N depending on whether user has access to given organization

mo_global.check_access(p_organization_id)

e.g.,

SELECT organization_id, name, mo_global.check_access(ORGANIZATION_ID) accessable FROM HR_OPERATING_UNITS;

//Related Tables

-- Lists all the Organizations as per Current Security Context either Orgs Accessable through "MO Operating Unit" or

-- MO Security Profile" profile options

mo_glob_org_access_tmp   

-- Lists products enabled for MOAC

FND_MO_PRODUCT_INIT    

-- Other Misc Tables

PER_SECURITY_PROFILES 

PER_ORGANIZATION_LIST 

// Example Standard Report

Accounts Payable Trial Balance

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

// Multi-Org Reporting in R12 for Custom Reports

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

// Modify Report Definintion to use either 'Single' or 'Multiple' Operating Unit Mode.

1. Login into application with System Administration responsibility (NOT System Administrator)

2. Navigate: Concurrent -> Programs

3. Query for short name of the concurrent program

4. Click on Update pencil 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.

OR

UPDATE FND_CONCURRENT_PROGRAMS

SET MULTI_ORG_CATEGORY='S'

WHERE CONCURRENT_PROGRAM_NAME = '<your program name>'

Reference Document:

1. Metalink Note: Operating Unit Not Selectable When starting Concurrent Requests For MOAC Responsibility [ID 837615.1]

2. Forum Ref:     http://forums.oracle.com/forums/thread.jspa?threadID=1056143&tstart=60

// Value Set in Report

The Default Value Set provided by Oracle Applications for MOAC based Security is

FND_MO_OU

// Convert your reports to use synonyms created for _ALL Tables

e.g.,

RA_CUSTOMER_TRX_ALL Table has synonym RA_CUSTOMER_TRX for APPS Schema.

This RA_CUSTOMER_TRX synonym has security predicate attached to it using VPD (Virtual Private Database).

OR

// OR If you don't wish to use VPD synonyms directly

If you don't wish to use VPD synonyms directly then as a work around you can join

your _ALL tables org_id column with

   mo_glob_org_access_tmp.organization_id

// Create User Parameter

P_CONC_REQUEST_ID

Datatype: Number

Length 15

// Add following code to After Parameter Form (NOT Before Report Trigger) to Initialize Context

SRW.USER_EXIT('FND SRWINIT');

--You may also put some debug messages here for instance

SRW.MESSAGE(999,'After SRWINIT');

// Add following code in After Report Trigger

SRW.USER_EXIT('FND SRWEXIT');

// VPD References

http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/1/

Oracle Applications Multiple Organizations Access Control for Custom Code Doc ID: Note:420787.1

API Hook to Validate Payroll Element Entry Value

CREATE OR REPLACE PACKAGE APPS.xx_py_api_hooks

AS

   PROCEDURE restrict_element_entry (p_assignment_id NUMBER, p_element_type_id NUMBER);

   PROCEDURE check_entry_value (p_element_entry_id NUMBER);

END;

/

CREATE OR REPLACE PACKAGE BODY apps.xx_py_api_hooks

AS

   /***

    *       Author:         Faraz Anwar

    *       Desciption:     Element Entry Level Validations

    ***/

  /**

   * Modlue Type        = PAY_ELEMENT_ENTRIES_F

   * Hook Called from   = Before Insert

   * Purpose            = Restrict Element Entries for Employees on Vacation

   **/

  

   PROCEDURE restrict_element_entry (p_assignment_id NUMBER, p_element_type_id NUMBER)

   AS

      l_element_type_id     NUMBER;

      l_emp_cat             VARCHAR2 (30);

      l_classification_id   NUMBER;

      CURSOR csr_vac

      IS

         SELECT employment_category

           FROM per_assignments_f paf, fnd_sessions sesh

          WHERE assignment_id = p_assignment_id

            AND sesh.session_id = USERENV ('sessionid')

            AND sesh.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date;

      CURSOR csr_clasf

      IS

         SELECT classification_id

           FROM pay_element_types_f pet, fnd_sessions sesh

          WHERE element_type_id = p_element_type_id

            AND sesh.session_id = USERENV ('sessionid')

            AND sesh.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date

            AND classification_id IN (SELECT classification_id

                                        FROM pay_element_classifications);

   /*WHERE (classification_name) IN

            ('Voluntary Deductions', 'Involuntary Deductions', 'Employer Charges', 'Statutory Deductions',

             'Pre-Statutory Deductions')

     AND legislation_code = 'SA');*/

   BEGIN

      OPEN csr_vac;

      FETCH csr_vac

       INTO l_emp_cat;

      CLOSE csr_vac;

      OPEN csr_clasf;

      FETCH csr_clasf

       INTO l_classification_id;

      CLOSE csr_clasf;

      IF (l_classification_id IS NOT NULL) AND (l_emp_cat = 'VAC')

      THEN

         fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

         fnd_message.set_token ('MESSAGE', 'Employee on Vacation. Element Entries are not Allowed.');

         app_exception.raise_exception;

      END IF;

   END;

    /**

   * Modlue Type        = PAY_ELEMENT_ENTRIES_F

   * Hook Called from   = Before Insert, Before Update

   * Purpose            = Validate Entries for Recovery Elements

   **/

   PROCEDURE check_entry_value (p_element_entry_id NUMBER)

   IS

      /* Cursor to Fetch Screen Entry Value */

      CURSOR c_curr_entry_value (p_element_entry_id NUMBER, p_input_name VARCHAR2, p_element_type_id NUMBER, p_effective_date DATE)

      IS

         SELECT peev.screen_entry_value

           FROM pay_element_entry_values_f peev, pay_input_values_f piv

          WHERE peev.element_entry_id = p_element_entry_id

            AND piv.NAME = p_input_name

            AND peev.input_value_id = piv.input_value_id

            AND piv.element_type_id = p_element_type_id

            AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date

            AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date;

      /* Cursor to Fetch Element Type Id */

      CURSOR c_elem_type (p_element_entry_id NUMBER, p_effective_date DATE)

      IS

         SELECT pel.element_type_id

           FROM pay_element_entries_f pee, pay_element_links_f pel

          WHERE pee.element_link_id = pel.element_link_id

            AND pee.element_entry_id = p_element_entry_id

            AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date

            AND p_effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date;

      CURSOR c_elem_set (p_element_type_id NUMBER)

      IS

         SELECT 'X'

           FROM apps.xx_py_element_set_members

          WHERE element_set_id = 13071 AND element_type_id = p_element_type_id;

      --

      --Element Set ID 13071 equals "Recovery Elements Entry Validation Set"

      --

      l_installment_amt      NUMBER;

      l_total_recovery_amt   NUMBER;

      l_repmt_amt            NUMBER;

      l_override_amt         NUMBER;

      l_total_due            NUMBER;

      l_effective_date       DATE;

      l_element_type_id      NUMBER;

      l_recovery_type        VARCHAR2(1);

   BEGIN

     

      l_effective_date := hr_general.effective_date;

      OPEN c_elem_type (p_element_entry_id, l_effective_date);

      FETCH c_elem_type

       INTO l_element_type_id;

      CLOSE c_elem_type;

      OPEN c_elem_set (L_element_type_id);

      FETCH c_elem_set

       INTO l_recovery_type;

      

      CLOSE c_elem_set;

      /* For Recovery Elements */

      IF l_recovery_type is not null                                                                 

      THEN

         OPEN c_curr_entry_value (p_element_entry_id, 'Total Recovery Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_total_recovery_amt;

         CLOSE c_curr_entry_value;

         OPEN c_curr_entry_value (p_element_entry_id, 'Installment Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_installment_amt;

         CLOSE c_curr_entry_value;

         OPEN c_curr_entry_value (p_element_entry_id, 'Repayment Amount', l_element_type_id, l_effective_date);

         FETCH c_curr_entry_value

          INTO l_repmt_amt;

         CLOSE c_curr_entry_value;

         /*

         open c_curr_entry_value (p_element_entry_id, 'Override Installment', p_element_type_id, l_effective_date);

         fetch c_curr_entry_value into  l_override_amt;

         close c_curr_entry_value;

         */

         /* verify that amounts entered are positive */

         IF (l_total_recovery_amt < 0) OR (l_installment_amt < 0) OR (l_repmt_amt < 0)

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Negative Amount not Allowed');

            app_exception.raise_exception;

         END IF;

         /* verify that amounts entered are logically correct */

         IF l_installment_amt > l_total_recovery_amt

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Installment Amount cannot be greater than Total Recovery Amount');

            app_exception.raise_exception;

         ELSIF l_repmt_amt > l_total_due

         THEN

            fnd_message.set_name ('FND', 'FND_GENERIC_MESSAGE');

            fnd_message.set_token ('MESSAGE', 'Repayment Amount cannot be greater than Total Due');

            app_exception.raise_exception;

         END IF;

      END IF;

   END;

END;

/