Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, July 21, 2022

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  




Wednesday, March 17, 2021

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



Friday, March 5, 2021

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;

 

 

  1. 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#;

 

 

  1. 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 ';

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect