Subledger Accounting: Mathematical View

SLA method = ∑ Application accounting Defn

** Across Applications

Application accounting Defn = ∑ Journal Line Defn  

**Application Specific

 Journal Line Defn  =

[JOURNAL LINE TYPE (Account Derivation Rule)=>Expense]

+

[JOURNAL LINE TYPE (Account Derivation Rule) =>Liability]

**Event Specific

Read from bottom to top 

New tables in R12 for Payables and to what R12 objects are obsoleted 11i tables mapped

Suppliers

New R12 tables  -> Old 11i Tables
AP_SUPPLIERS - replaces PO_VENDORS
AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY(Payments) and HZ(TCA): IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.
HZ_PARTIES - Party data for the suppliers.
HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices

Additional table in R12: AP_INVOICE_LINES_ALL
Allocations - ap_chrg_allocations_all is obsolete in R12

Taxes

Functionality provided by E-Business Tax
New tables in R12
ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)
ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)
ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)
ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments

Functionality moved to central Payments (IBY)

New IBY tables in R12:
IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information
                                                               (11i Terminology is  Payment Batch)
IBY_PAY_INSTRUCTIONS_ALL - Payment Instruction information
IBY_DOC_PAYABLES_ALL - Invoice information stored by IBY for generating payment
IBY_PAYMENTS_ALL - Payment Information

The following tables are used in PPR in R12 . These were used in 11i for payment batches also.
AP_INV_SELECTION_CRITERIA_ALL
AP_SELECTED_INVOICES_ALL

The following AP tables are still in use in R12 to store payment related information.
AP_INVOICE_PAYMENTS_ALL
AP_CHECKS_ALL
AP_PAYMENT_HISTORY_ALL

Accounting

Functionality moved to SubLedger Accounting (SLA)

New R12 tables:
XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL 
XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL
XLA_AE_LINES-> replaces AP_AE_LINES_ALL
XLA_DISTRIBUTION_LINKS

Trial Balance

New R12 Table
XLA_TRIAL_BALANCES

AP_LIABILITY_BALANCE-> not used in new R12 transactions
AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts

Functionality moved to Cash Management.

CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL
CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

XML Report

1. Develop the SQL.
2. Write the required package for beforeReport Trigger
3. Write the Data Template.
4. Attach the data template under data definition.
5. register the conc program as executable XDO%EXE.
6. conc program short name = data definition code
7. register the parameters in conc program.
8. run the program.
9. save the output in xml
10. define the rtf template and attach it template definition.
11. run the program again, get the output in excel/pdf any format.


Detailed Step

<dataTemplate name="COUNTER_REPORT" description="Expense Counter Report" Version="1.0" defaultPackage="xx_xml_reports">
    <parameters>
        <parameter name="p_employee_id" dataType="number"/>
        <parameter name="p_counter_type" dataType="character"/>
    </parameters>
    <dataQuery>
        <sqlStatement name="Q1">
            <![CDATA[SELECT   c_submitter, c_company_id, c_counter, counter_type,
               organization_name
          FROM (SELECT ppf.full_name c_submitter,
                       ppf.employee_number c_company_id,
                       slec.counter c_counter, 'Function' AS counter_type,
                       a.NAME organization_name
                  FROM apps.xx_function_counter slec,
                       apps.per_all_people_f ppf,
                       apps.per_all_assignments_f paf,
                       apps.per_person_type_usages_f pptu,
                       apps.per_person_types ppt,
                       apps.per_assignment_status_types past,
                       hr_organization_units_v a,                     -- added
                       hr_organization_information_v b                 --added
                 WHERE 1 = 1
                   AND ppf.person_id = paf.person_id
                   AND slec.party_id = ppf.party_id
                   AND &pwhereclause1                  
                   AND paf.effective_end_date > SYSDATE
                   AND slec.ROWID = (SELECT MAX (ROWID)
                                       FROM xx_function_counter x
                                      WHERE x.party_id = slec.party_id)
                   AND ppf.person_id = pptu.person_id
                   AND pptu.person_type_id = ppt.person_type_id
                   AND ppf.person_id = pptu.person_id
                   AND pptu.person_type_id = ppt.person_type_id
                   AND SYSDATE BETWEEN pptu.effective_start_date
                                   AND pptu.effective_end_date
                   AND SYSDATE BETWEEN ppf.effective_start_date
                                   AND ppf.effective_end_date
                   AND paf.assignment_status_type_id =
                                                past.assignment_status_type_id
                   AND past.user_status NOT LIKE '%Intern%'
                   AND ppf.party_id = slec.party_id
                   AND ppt.system_person_type IN ('EMP')
                   AND paf.effective_end_date > SYSDATE - 1
                   AND a.organization_id = b.organization_id           --added
                   AND a.organization_id = paf.ass_attribute6
                   AND a.date_to IS NULL                               --added
                   AND b.org_information1_meaning = 'Operating Unit'   --added
                                                                    )
         WHERE &pwhereclause2
     ORDER BY c_company_id ]]>
        </sqlStatement>
    </dataQuery>
    <dataTrigger name="beforereporttrigger" source="xx_xml_reports.beforereporttrigger"/>
</dataTemplate>

Register Parameters

        <parameter name="p_employee_id" dataType="number"/>
        <parameter name="p_counter_type" dataType="character"/>


CREATE OR REPLACE PACKAGE xx_xml_reports
AS
   p_employee_id   NUMBER;
   p_counter_type  varchar2(100);
   pwhereclause1   VARCHAR2 (500);
   pwhereclause2   VARCHAR2 (500);
   FUNCTION beforereporttrigger
      RETURN BOOLEAN;
END;



CREATE OR REPLACE PACKAGE BODY xx_xml_reports
AS
   FUNCTION beforereporttrigger
      RETURN BOOLEAN
   IS
   BEGIN
      IF (p_employee_id IS NOT NULL)
      THEN
         pwhereclause1 := 'ppf.person_id LIKE :p_employee_id';
      ELSIF (p_employee_id IS NULL)
      THEN
         pwhereclause1 := '1=1';
      END IF;

      IF (p_counter_type IS NULL)
      THEN
         pwhereclause2 := '1=1';
      ELSIF (p_counter_type IS NOT NULL AND p_counter_type = 'Travelocity')
      THEN
         --pwhereclause2 := 'counter_type ='||'Travelocity';
         pwhereclause2 := '1=1';
      ELSIF (p_counter_type IS NOT NULL AND p_counter_type = 'Late ER')
      THEN
         pwhereclause2 := 'counter_type =' || 'Late ER';
      END IF;

      RETURN TRUE;
   END;
END xx_xml_reports;












FNDLOAD $APPS_LOGIN_ID 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct  $XX_AP_TOP/patch/115/import/US/EXPDETRPT_DDF.ldt


java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME $APPS_LOGIN -DB_PASSWORD $APPS_PWD -JDBC_CONNECTION $TNS_DETAIL -LOB_TYPE TEMPLATE -APPS_SHORT_NAME XXAP -LOB_CODE XXEXPDETRPT -LANGUAGE en -XDO_FILE_TYPE RTF -FILE_NAME $XX_AP_TOP/reports/US/TEMPLATE_SOURCE_XXAP_XXEXPDETRPT_en.rtf

PLSQL Collections Examples

set serveroutput on;
DECLARE
   --TYPE list_of_names_t IS TABLE OF ap_invoices_all.invoice_num%TYPE
    TYPE list_of_names_t IS TABLE OF varchar2(16) INDEX BY PLS_INTEGER;

   happyfamily   list_of_names_t;
   l_row         PLS_INTEGER;
BEGIN
   happyfamily (2020202020) := 'Eli';
   happyfamily (-15070) := 'Steven';
   happyfamily (-90900) := 'Chris';
   happyfamily (88) := 'Veva';
   l_row := happyfamily.FIRST;

   WHILE (l_row IS NOT NULL)
   LOOP
      DBMS_OUTPUT.put_line (happyfamily (l_row));
      l_row := happyfamily.NEXT (l_row);
   END LOOP;
END;

Chris
Steven
Veva
Eli



--access values one by one

SET SERVEROUTPUT ON;

DECLARE
   CURSOR c_cname
   IS
      SELECT responsibility_key, responsibility_id
        FROM fnd_responsibility
       WHERE responsibility_key LIKE
                        (SELECT meaning
                           FROM fnd_lookup_values_vl
                          WHERE lookup_type = 'XX_EXP_DATE_PROGRAM_ACCESS');

   TYPE type_cname_tab IS TABLE OF VARCHAR2 (60)    --store responsibility_id
      INDEX BY BINARY_INTEGER;

   tab_cname             type_cname_tab;
   v_cname_counter       NUMBER         := 0;
   v_responsibility_id   NUMBER;
BEGIN
   FOR r_cname IN c_cname
   LOOP
      v_cname_counter := v_cname_counter + 1;
      tab_cname (v_cname_counter) := r_cname.responsibility_key;
   END LOOP;

   FOR i_cname IN 1 .. v_cname_counter
   LOOP
      SELECT responsibility_id
        INTO v_responsibility_id
        FROM fnd_responsibility
       WHERE responsibility_key = tab_cname (i_cname);

      DBMS_OUTPUT.put_line ('Responsibilty_key: ' || tab_cname (i_cname));
      DBMS_OUTPUT.put_line ('Responsibilty_id: ' || v_responsibility_id);
   END LOOP;
END;