Oracle Cloud P2P - Query to get Oracle Cloud (Fusion) Procure to Payment (P2P) details

SELECT

  poha.po_header_id,

  poha.segment1 AS po_num,

  haot.name AS proc_bu,

  haot.name AS req_bu,

  haot.name AS billto_bu,

  hrla1.location_name AS bill_to_loc,

  hrla.location_name AS ship_to_loc,

  ps.segment1 vendor_num,

  hp.party_name AS vendor_name,

  hps.party_site_name AS site_name,

  apt.name,

  ppnf.last_name||', '||ppnf.first_name AS buyer_name,

  poha.document_status,

  poha.currency_code,

  pola.line_num,

  pola.item_description,

  polt.line_type,

  pola.quantity AS quantity,

  DECODE (polt.line_type,'Goods', pola.unit_price,'Fixed Price Services', pola.amount) AS unit_price,

  DECODE (polt.line_type,'Goods', (pola.quantity * pola.unit_price), 'Fixed Price Services', pola.amount) AS po_lin_amount,

  pola.line_status,

  to_char(polla.need_by_date,'DD-MON-YYYY') need_by_date,

  to_char(polla.promised_date,'DD-MON-YYYY') promised_date,

  pod1.distribution_num,

  pod1.destination_subinventory,

  pod1.quantity_ordered,

  gcc.segment2 cost_center,

  gcc.segment1||'.'|| gcc.segment2||'.'|| gcc.segment3||'.'|| gcc.segment4||'.'|| gcc.segment5||'.'|| gcc.segment6||'.'|| gcc.segment7||'.'|| gcc.segment8 AS CHARGE_ACCOUNT,

  inv.invoice_num,

  to_char(inv.invoice_date,'DD-Mon-YYYY') invoice_date,

  inv.invoice_amount,

  inv.amount_paid,

  decode(ap_invoices_utility_pkg.get_approval_status(inv.invoice_id,inv.invoice_amount,inv.payment_status_flag,inv.invoice_type_lookup_code),

         'FULL'            , 'Fully Applied'

        ,'NEVER APPROVED'  , 'Never Validated'

        ,'NEEDS REAPPROVAL', 'Needs Revalidation'

        ,'CANCELLED'       , 'Cancelled'

        ,'UNPAID'          , 'Unpaid'

        ,'AVAILABLE'       , 'Available'

        ,'UNAPPROVED'      , 'Unvalidated'

        ,'APPROVED'        , 'Validated'

        ,'PERMANENT'       , 'Permanent Prepayment'

        ,NULL

  ) inv_status,

  ipa.payment_method_code,

  ipa.paper_document_number,

  ipa.payment_date,

  ipa.payment_amount payment_amount1,

  ipa.ext_bank_account_number,

  ipa.ext_branch_number,

  ipa.payment_profile_sys_name,

  ipa.payment_process_request_name

FROM

  po_headers_all poha,

  hr_organization_units_f_tl haot,

  hr_locations_all hrla,

  hr_locations_all hrla1,

  poz_suppliers ps,

  hz_parties hp,

  hz_party_sites hps,

  ap_terms_tl apt,

  per_person_names_f_v ppnf,

  poz_supplier_sites_all_m pss,

  po_lines_all pola,

  po_line_types_tl polt,

  po_line_locations_all polla,

  po_distributions_all pod1,

  gl_code_combinations gcc,

  ap_invoices_all inv,

  ap_invoice_lines_all lin,

  ap_invoice_distributions_all invd,

  ap_invoice_payments_all aipa,

  iby_docs_payable_all idpa,

  iby_payments_all ipa

WHERE 1 = 1

  AND haot.organization_id = poha.prc_bu_id

  and haot.language = 'US'

  AND hrla.location_id = poha.ship_to_location_id

  AND hrla1.location_id = poha.bill_to_location_id

  AND xep.legal_entity_id = poha.soldto_le_id

  AND ps.vendor_id = poha.vendor_id

  AND hp.party_id = hps.party_id

  AND pss.party_site_id =  hps.party_site_id

  AND pss.vendor_site_id = poha.vendor_site_id

  AND apt.term_id = poha.terms_id

  and apt.language = 'US'

  AND ppnf.person_id = poha.agent_id

  AND poha.po_header_id = pola.po_header_id

  AND pola.line_type_id = polt.line_type_id

  and polt.language = 'US'

  AND poha.po_header_id = pola.po_header_id

  AND pola.category_id = cat.category_id

  AND polla.po_header_id = poha.po_header_id

  AND polla.po_line_id  = pola.po_line_id

  AND pod1.code_combination_id = gcc.code_combination_id

  AND pod1.po_header_id = poha.po_header_id

  AND pod1.po_header_id = pola.po_header_id

  AND pod1.line_location_id = polla.line_location_id

  AND poha.po_header_id = lin.po_header_id

  AND pola.po_line_id = lin.po_line_id

  AND pod1.po_distribution_id = lin.po_distribution_id

  AND inv.invoice_id = lin.invoice_id

  AND invd.invoice_id = inv.invoice_id

  AND lin.line_number = invd.invoice_line_number

  AND inv.invoice_id = idpa.calling_app_doc_unique_ref2

  AND aipa.invoice_payment_id = idpa.calling_app_doc_unique_ref4

  AND aipa.invoice_id = idpa.calling_app_doc_unique_ref2

  AND aipa.check_id = idpa.calling_app_doc_unique_ref1

  AND idpa.formatting_payment_id = ipa.payment_id

  AND haot.name like 'US1 B%'

ORDER BY

   haot.name

  ,poha.segment1

  ,pola.line_num

  ,pod1.distribution_num  

R12 - Oracle EBS R12 Technical Training Manuals

 

R12 - Oracle EBS R12 Technical Training Manuals
Training Manual Document	Google Drive URL
01 - SQL - Oracle SQL Training Manual	https://drive.google.com/file/d/1dz2lOEaHMY-VFnF2rPNYSqzfrcYHLli0
02 - PL-SQL - Oracle PL SQL Training Manual	https://drive.google.com/file/d/1mZ1DLqCK2pZ0MfaVZ5I2TAlgRtUSJeOm
03 - AOL - Oracle Application Object Library (AOL) Training	https://drive.google.com/file/d/1OOM3km5nnbv6EvWWoi8KTdIzi6Sswz5e
04 - SQL LOADER - Oracle SQL Loader Training Manual	https://drive.google.com/file/d/1Dymmxy1lUC9_eYFxjjazxOLpEUWW7uSA
05 - INTERFACE - Oracle Inbound Conversion (Interface) Process	https://drive.google.com/file/d/1HNYUPibfkzDEdipLJ0iVwa-uARztxCT1
	https://drive.google.com/file/d/1pJs5Gy2T9o-gF3vW_-zBd4HaS7AtTAxa
06 - CONVERSION - Oracle Outbound Interface Process	https://drive.google.com/file/d/1DKe3OQUUvyEb5QaNtDTqMrMR8pn1rphT
07 - XMLP - Oracle XML Publisher Training Document	https://drive.google.com/file/d/1WLNIQN40lb3WIRTRU27hyN6RVRpt9Pr1
08 - DISC - Oracle Discoverer Training Manual	https://drive.google.com/file/d/1wbHhGes0uLq6ZsAuxiG3a60wmRKEPSbp
09 - REPORT - Oracle PL-SQL Report Development Process	https://drive.google.com/file/d/18ijjFGAMKChZhojWGK6xx98gaLlTBNrf
10 - FORMS - Oracle 10g Forms Development Training Manual	https://drive.google.com/file/d/13_7wR_z9anWtivfmZ-Y70LfN9ofQ9F6z
11 - FORMS - Oracle Forms Personalization Training Manual	https://drive.google.com/file/d/1ihkZS1l3HKxiI0H7Wt4J5go4lt183n-n
12 - OAF - Oracle Application Framework Training Manual	https://drive.google.com/file/d/1uR_Hgbl_DEu6t9dmQKSMGsjxcGJPancn
13 - OAF - Oracle Application Framework Personalizations	https://drive.google.com/file/d/1emScu8RpDZDO5Jxz84i9RUaD6BOPIvcy
14 - WF - Oracle Workflow Training Manual	https://drive.google.com/file/d/11BkMg9ME9YWUB3JYzhPjw6UUxvmtP0fO
15 - ADI - Oracle Custom WEB ADI Creation for Supplier	https://drive.google.com/file/d/1-Uzvn5PbhZPdAPIXc3xlIUgOwOaIqr1q
16 - UNIX-LINUX Training Material	https://drive.google.com/file/d/1DbX3I8bgx_f4VyQgA_OHeljbLhOf1ESe
17 - P2P - Oracle Procure To Pay (P2P) Life Cycle Training Manual	https://drive.google.com/file/d/182fypmcpkHBOcsCMx6UhjFXmLqf3f5Wp
18 - O2C - Oracle Order To Cash (O2C) Life Cycle Training Manual	https://drive.google.com/file/d/1mUopcALdTa1nH0z0JyVPaF49cAnWnmAo

R12.2.X – Materialized Views Migration/Implementation in R12.2.X

 

1. Drop the existing Materialized view and underlying table of the materialized view

DROP MATERIALIZED VIEW XXCUST.XXCUST_TEST_MV;

 

 

2. Create the Logical View in your development database.

The Logical View name must be the desired materialized view name with a '#' character appended to it.

A materialized view name must be unique within the first 29 bytes.

A materialized view definition must be stored in an ordinary view called MV_NAME||'#'.

A materialized view definition must specify a column alias for each item in the select list.[minimal]

• Failure to specify a column alias may cause the error ORA-00998 "must name this expression with a column alias".
• Example:

SELECT SUM (EMP.SALARY) AS SUM_EMP_SALARY,

       MIN (EMP.empno) AS MIN_EMP_NUM,

       'XX' || 'YY' AS XXYY

  FROM TEST_EMP EMP

 

 

Logical View Creation Example:

                CREATE OR REPLACE VIEW XXCUST.XXCUST_TEST_MV#

AS

   SELECT UPPER (oracle_username) USERNAME,

          DECODE (read_only_flag,'C','pub','E','applsys','U','apps') USERTYPE

     FROM fnd_oracle_userid

    WHERE read_only_flag IN ('C','E','U');

 

Test the materialized view definition for accuracy before generating the materialized view implementation

SELECT * FROM XXCUST.XXCUST_TEST_MV#;

 

 

3. Generate the Materialized View.

On an editioned database, materialized views are generated from their corresponding logical views using the AD_ZD_MVIEW.UPGRADE procedure.

• Do not attempt to directly create or replace the materialized view implementation. To recreate a materialized view implementation, call the AD_ZD_MVIEW.UPGRADE procedure.

BEGIN

      AD_ZD_MVIEW.UPGRADE('XXCUST', 'XXCUST_TEST_MV');

      COMMIT;

END;

 

 

In this example, the UPGRADE procedure detects that materialized view is missing and generates it from the Logical View. The Materialized View definition is generated by transforming the Logical View query into an equivalent implementation query that directly references the underlying tables and columns.

 

These 4 Rows get created in the process for

·         Logical View (Ending with '#') in Custom Schema

·         Materialized View in Custom Schema

·         Table in Custom Schema

·         Synonym in APPS

 

SELECT object_type, object_name

FROM dba_objects

WHERE object_name like 'XXCUST_TEST_MV%';

4.       Use the ALTER MATERIALIZED VIEW statement to modify an existing materialized view in one or more of the following ways:

·         To change its storage characteristics

·         To change its refresh method, mode, or time

·         To alter its structure so that it is a different type of materialized view

·         To enable or disable query rewrite

 

ALTER MATERIALIZED VIEW XXCUST.XXCUST_TEST_MV REFRESH FORCE ON DEMAND USING TRUSTED CONSTRAINTS DISABLE QUERY REWRITE;

/

 

5.       If the referenced objects are be placed in a non editioned schema that is different from the schema where the mview is to be created, then grant execute permission on the package to the mview owner.References:
12.2 E-Business Suite Upgrade Of Custom Materialized Views Fails Due To Changes Required For 12.2 ADOP Editioned Objects (Doc ID 2205375.1)
ORA-38818: Illegal Reference To Editioned Object When Creating Materialized View (Doc ID 1556379.1)

 

A materialized view should use 'REFRESH FORCE' instead of 'REFRESH FAST'. The 'FORCE' option allows the materialized view to fall back to using a complete refresh in situations where the fast refresh is not possible.

 

If the materialized view implementation content must be automatically refreshed after patching, then you must include the '/*AUTOREFRESH*/' comment tag in the materialized view definition query.

o    Do not specify the /*AUTOREFRESH*/ tag for large materialized views that will take a long time to refresh. For these cases use a concurrent program to refresh the materialized view after patching cutover.

• Example: create or replace view XXCUST_TEST_MV# as select /*AUTOREFRESH*/ ... ;

 

BEGIN 

      DBMS_MVIEW.REFRESH('XXCUST.XXCUST_TEST_MV','C',ATOMIC_REFRESH=>TRUE);

END;

/

 

Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes by incrementally applying changes to the materialized view.
		For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT	P	Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE	?	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

 

 

6.       Create a comment to an oracle Materialized view

COMMENT ON MATERIALIZED VIEW XXCUST.XXCUST_TEST_MV IS 'snapshot table for snapshot XXCUST.XXCUST_TEST_MV ';