R12 OM and AR Functional Overview

Coming Soon !!!

R12 iExpense functional flow

Coming Soon !!!

Concur and R12 Integration Flow

Coming Soon !!!

R12 General Ledger functional flow

Coming Soon !!!

R12 Fixed Asset functional flow

Coming Soon !!!

R12 iProcurement functional flow

Coming Soon !!!

R12 iSupplier functional flow

Coming Soon !!!

R12 Oracle Purchasing functional flow

Coming Soon !!!

R12 Accounts Payable functional flow

Coming Soon !!!

R12 Receivables Functional Flow

Coming Soon !!!

XML Publisher

Coming Soon!!!

How to process check printing in Oracle R12

Before you start check printing process, assuming you have created AP Invoice and is validated and approved, it is ok even if it is forced approved.

1. Responsibility – Payables Manager

2. Navigate to Payment Manager

3.Navigate to Submit Single Payment Process  Request link

4. Create Payment process request

Enter “Payment Process Request Name”

Enter Pay Through Date – this will be default date

Enter Payee – If you want to process the check for only one supplier else leave it blank for all

5. Go to “Payment Attribute” Tab

Enter "Disbursement Bank Account" - "BofA HKD Account"

Enter "Payment Document" - XX BofA HKD Check

Enter "Payment Process Profile" - XX BofA HKD Check (Default when Payment Document is selected)

Enter "Payment Exchange Rate Type" - "Corporate"

Submit the payment by clicking on the Submit button

After submitting the payment, go back to Oracle application Payables Manager, Menu View -> Requests
Concurrent program would be submitted as listed below
1. Payment Process Request Program
2. Scheduled Payment Selection Report
3. Build Payments
4. Format Payment Instructions

View output of "Format Payment Instructions" and print the check.

Oracle Application Functional Flow

Coming Soon !!!!
Work in progress

Contact details at party or customer level

-- Show Contact details at party/customer account level for all Bill To sites
-- Enter value - Customer Number
---------------------------------------------------------------------------------------------------------------------------
SELECT  hca.account_name  "Account Name"
       ,hca.account_number  "Account Number"
       ,Acct_contact.cust_contact "Account Level Contact"
       ,(select name from hr_operating_units where organization_id = hcasa.org_id) "Operating Unit"
       ,hps.party_site_number "Bill To Site Number"
       ,(SELECT address1||','||address2||','||address3||','|| address4||','|| city||','|| state||','||postal_code
           FROM hz_locations
          WHERE location_id = hps.location_id) "Bill To Address"
       , acct_contact.email_address "Contact Email Address"
         ,DECODE((select distinct responsibility_type from
         hz_cust_account_roles a, hz_role_responsibility b, hz_cust_acct_sites_all c
         where
          a.cust_account_id = c.cust_account_id
         and a.cust_account_role_id = b.cust_account_role_id
         and a.cust_account_role_id = acct_contact.cust_account_role_id
         and responsibility_type = 'INV'),'INV','Y','N' ) "Invoice Role"
            ,acct_contact.contact_type "Contact Type"
        FROM hz_parties hp,
        hz_party_sites hps,
        hz_cust_accounts_all hca,
        hz_cust_acct_sites_all hcasa,
        (select hp.party_name Cust_contact
              , hcar.cust_account_id
              , hcar.cust_account_role_id
              , NVL(rel_hp.EMAIL_ADDRESS, hoc.attribute7) EMAIL_ADDRESS
              , hoc.attribute6 contact_type
            FROM hz_parties hp,
                   hz_parties rel_hp,
                   hz_person_profiles hpp,
                   hz_relationships hr,
                   hz_org_contacts hoc,
                   hz_cust_account_roles hcar
             WHERE hoc.party_relationship_id = hr.relationship_id
               AND hr.subject_id             = hp.party_id
               AND rel_hp.party_id           = hr.party_id
               AND hp.party_id               = hpp.party_id
               AND hpp.effective_end_date IS NULL
               AND rel_hp.party_id           = hcar.party_id
               AND hoc.party_relationship_id = hr.relationship_id
               AND hr.subject_table_name     = 'HZ_PARTIES'
               AND hr.subject_type           = 'PERSON'
               AND hr.relationship_code      = 'CONTACT_OF'
               AND hcar.cust_acct_site_id   is null ) Acct_contact
  WHERE hp.party_id = hps.party_id
    AND hp.party_id = hca.party_id
    AND hcasa.party_site_id = hps.party_site_id
    AND hca.cust_account_id = hcasa.cust_account_id
    AND hca.account_number = :lv_account_number
    AND hcasa.cust_acct_site_id in (SELECT hcsua.cust_acct_site_id
           FROM hz_cust_site_uses_all hcsua
          WHERE hcsua.cust_acct_site_id =
                                     hcasa.cust_acct_site_id
            AND hcsua.site_use_code = 'BILL_TO')
    AND Acct_contact.cust_account_id = hca.cust_account_id;
---------------------------------------------------------------------------------------------------------------------------

Query to show AR Order details

Below queries are for 12.x.x

-- This query will show Receivable Sales Order transaction details posted between date range:
-- Enter values 1. GL Posted Date range
-----------------------------------------------------------------------------------------------------------------
SELECT qttl.NAME "Transaction Name",
       oha.order_number "Order Number",
       ola.line_number "SO Line Number",
       ola.ordered_item "Item",
       ola.ordered_quantity * ola.unit_selling_price "Line Amount",
       rcta.trx_number invoice_number, rcta.trx_date "Invoice Date",
       rctla.line_number "Inv Line Number",
       rctlg.gl_posted_date "AR-GL Posted Date"
  FROM oe_order_headers_all oha,
       oe_order_lines_all ola,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla,
       apps.oe_transaction_types_tl qttl,
       apps.ra_cust_trx_line_gl_dist_all rctlg
 WHERE oha.header_id = ola.header_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
   AND oha.order_type_id = qttl.transaction_type_id
   AND rctlg.customer_trx_line_id = rctla.customer_trx_line_id
   AND rctlg.gl_posted_date BETWEEN 'DD-MON-YYYY' AND 'DD-MON-YYYY';
-----------------------------------------------------------------------------------------------------------------

-- This query will show the same column details as above for a Sales Order
-- Enter value - 1. Sales Order number
-----------------------------------------------------------------------------------------------------------------
SELECT qttl.NAME "Transaction Name", oha.order_number "Order Number",
       ola.line_number "SO Line Number", ola.ordered_item "Item",
       ola.ordered_quantity * ola.unit_selling_price "Line Amount",
       rcta.trx_number invoice_number, rcta.trx_date "Invoice Date",
       rctla.line_number "Inv Line Number",
       rctlg.gl_posted_date "AR GL Posted Date"
  FROM oe_order_headers_all oha,
       oe_order_lines_all ola,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla,
       apps.oe_transaction_types_tl qttl,
       apps.ra_cust_trx_line_gl_dist_all rctlg
 WHERE oha.header_id = ola.header_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
   AND order_number = :p_order_number
   AND oha.order_type_id = qttl.transaction_type_id
   AND rctlg.customer_trx_line_id = rctla.customer_trx_line_id;
-----------------------------------------------------------------------------------------------------------------

-- This query will show the same column details as above for the specific account segment (Segment2)-- Change the segment number depending on combination setup
-- Enter value - Segment
-----------------------------------------------------------------------------------------------------------------
SELECT qttl.NAME "Transaction Name", oha.order_number "Order Number",
       ola.line_number "SO Line Number", ola.ordered_item "Item",
       ola.ordered_quantity * ola.unit_selling_price "Line Amount",
       rcta.trx_number invoice_number, rcta.trx_date "Invoice Date",
       rctla.line_number "Inv Line Number",
       rctlg.gl_posted_date "AR GL Posted Date"
  FROM oe_order_headers_all oha,
       oe_order_lines_all ola,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla,
       apps.oe_transaction_types_tl qttl,
       apps.ra_cust_trx_line_gl_dist_all rctlg
 WHERE oha.header_id = ola.header_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
   AND oha.order_type_id = qttl.transaction_type_id
   AND rctlg.customer_trx_line_id = rctla.customer_trx_line_id
   AND rctlg.code_combination_id IN (SELECT code_combination_id
                                       FROM gl_code_combinations
                                      WHERE segment2 = :SEGMENT);
-----------------------------------------------------------------------------------------------------------------

-- This query will show the same column details as above for the specific transaction type
-- Enter value - Transaction type
-----------------------------------------------------------------------------------------------------------------
SELECT qttl.NAME "Transaction Name", oha.order_number "Order Number",
       ola.line_number "SO Line Number", ola.ordered_item "Item",
       ola.ordered_quantity * ola.unit_selling_price "Line Amount",
       rcta.trx_number invoice_number, rcta.trx_date "Invoice Date",
       rctla.line_number "Inv Line Number",
       rctlg.gl_posted_date "AR GL Posted Date"
  FROM oe_order_headers_all oha,
       oe_order_lines_all ola,
       ra_customer_trx_all rcta,
       ra_customer_trx_lines_all rctla,
       apps.oe_transaction_types_tl qttl,
       apps.ra_cust_trx_line_gl_dist_all rctlg
 WHERE oha.header_id = ola.header_id
   AND rcta.customer_trx_id = rctla.customer_trx_id
   AND rctla.interface_line_attribute6 = TO_CHAR (ola.line_id)
   AND rctla.interface_line_attribute1 = TO_CHAR (oha.order_number)
   AND oha.order_type_id = qttl.transaction_type_id
   AND rctlg.customer_trx_line_id = rctla.customer_trx_line_id
   AND qttl.NAME LIKE '%' || :trx_type || '%' -----------------------------------------------------------------------------------------------------------------

Oracle Labor Distributions queries

Change the query as per the business requirements.
----------------------------------------------------------------------------------------------------------------------------
Query to find duplicate schedule lines based on Project ID, Task ID, Award ID, Expenditure Type, Schedule Start Date, Schedule End Date, Assignment Number and Row id in PSP SCHEDULE LINES

SELECT *
FROM apps.psp_schedule_lines psl,
           per_all_assignments_f paaf,
           psp_schedule_hierarchy psh
     WHERE psl.project_id = p_project_id
       AND psl.task_id = p_task_id
       AND psl.award_id = p_award_id
       AND psl.expenditure_type = p_expenditure_type
       AND paaf.assignment_id = psh.assignment_id
       AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
       AND paaf.assignment_number = p_assignment_num
       AND rowidtochar(psl.ROWID) = NVL(p_rowid,'XXX')
       AND TO_DATE (p_schedule_start_date, 'DD-MON-YYYY') = psl.schedule_begin_date
       AND to_date(psl.schedule_end_date,'DD-MON-YYYY')  = psl.schedule_end_date
       AND TRUNC(SYSDATE) between paaf.effective_start_date and paaf.effective_end_date;
----------------------------------------------------------------------------------------------------------------------------
Query to find duplicate schedule lines based on Schedule Start Date, Schedule End Date and Assignment Number
SELECT distinct 1
      FROM apps.psp_schedule_lines psl,
           per_all_assignments_f paaf,
           psp_schedule_hierarchy psh
     WHERE paaf.assignment_id = psh.assignment_id
       AND psl.schedule_hierarchy_id = psh.schedule_hierarchy_id
       AND paaf.assignment_number = p_assignment_num
      AND (   psl.schedule_end_date BETWEEN TO_DATE (p_schedule_start_date,
                                                      'DD-MON-YYYY'
                                                     )
                                         AND TO_DATE (p_sch_end_date,
                                                      'DD-MON-YYYY'
                                                     )
            OR psl.schedule_begin_date BETWEEN TO_DATE (p_schedule_start_date,
                                                        'DD-MON-YYYY'
                                                       )
                                           AND TO_DATE (p_sch_end_date,
                                                        'DD-MON-YYYY'
                                                       )
            OR (TO_DATE (p_schedule_start_date, 'DD-MON-YYYY')
                  BETWEEN psl.schedule_begin_date
                      AND psl.schedule_end_date)
            )
           and TRUNC(SYSDATE) between paaf.effective_start_date and paaf.effective_end_date;
----------------------------------------------------------------------------------------------------------------------------