Oracle Fusion Financial's Book (AP,AR,GL,FA)

Key Feature of the Book

• Complete Book for Implementing Fusion Financial AP, AR, GL & FA
• Sharing Complete Implementation Steps with Screen Shot for each step in this Book.
• Refer this book, as a practical workshop for implementing Oracle Fusion Financial.
• For Each Financial Module, we are sharing the configuration/setup steps and the Complete process flows & Business scenarios for that module with working Example
• Complete AP, AR, GL, FA flow with screen shots shared in this book.
• More then 1000+ screen shots while implementing and working on process flow in Fusion Financial.
• Providing 1-month support after purchasing this book. You can ask any concerns related to this book in support portal email shared in the Book and you will get the resolution in 24 hours.
• This is an E-pub/pdf book. 
•  These are E Books, We are selling these Books to anywhere in the World. You can contact us to purchase from Outside India also.
• We are the Most trusted Oracle application partner from last 7 years. We are providing E Book , Freelancing , Project support & On Job Support in Oracle r12 & Fusion Application. Please know more About Us

Contact us

oracleapplicationsblog@gmail.com

+91-9015292064

New Delhi, India

Note: These are E Books, We are selling these Books to anywhere in the World.

To Purchase this Book Please Contact on this below Email or fill the Form mentioned in the End of this Post.

Book Cost: 1499-/- INR

Contact us : OracleApplicationsBlog@gmail.com

Fill this E-Form to Purchase this Book

Download Book Sample Chapter

CLICK TO BUY(1499 INR)

General Ledger List of Topics Covered in this Book

1.     Overview of the Oracle Fusion Application

2.     Oracle fusion Application Home Page Feature

3.     Overview of the Functional setup Manager in Oracle fusion Financials

4.     How to Use functional Setup Manager

5.     Feature of Functional setup Manager

6.     How to Create Implementation Project in Functional setup Manager

7.     Implementation Project Task assignments to the Users

8.     Creating User/Roles Access for Financial Implementation

9.     Introduction of Organization Structure in Oracle Fusion

10. Implementation Example Scenario for Sample Organization

11. Create Legal Entity Address

12. Creating Legal Entity in Oracle Fusion

13. Creating Chart of Account Value Sets

14. Creating Chart of Accounts Structure

15. Creating Chart of Accounts Structure Instances

16. Creating Chart of Account Value Set Values

17. Creating Accounting Calendar for Ledger

18. Defining Currency Conversion Rate Type

19. Creating Subledger Accounting Methods

20. Creating Ledger in Oracle Fusion Financials

21. Creating Ledger

22. Assigning Legal Entity to Ledger

23. Maintaining Primary Ledger Information

24. Assigning Balancing Segment Values to Legal Entities

25. Creating Secondary Ledger in Oracle Fusion Financials

26. Creating Accounting Calendar for Secondary Ledger

27. Defining Specify Ledger Options for Secondary Ledger

28. Creating Primary to Secondary Ledger Mapping

29. Define Accounting Configurations

30. Creating Access in Oracle Fusion for Using General Ledger

31. Providing Data Access to access the Ledger Data in General Ledger

32. Opening First GL Period in Oracle Fusion

33. Open/Close Manage GL Period in Oracle Fusion

34. Creating Journal Sequence in Oracle fusion

35. Creating Journal Sources for External Source

36. Creating Journals Auto Post Criteria Set

37. Creating Auto Journal Reversal Criteria Sets

38. Configuring Revaluations in Oracle Fusion

39. How to Create the Budget in Oracle Fusion

40. Upload Budget in Oracle Fusion

41. Inquiry and View the GL Account Balances in Oracle Fusion

42. Creating New GL Account Combination in Oracle Fusion

43. Maintain Daily Rates in Oracle Fusion

44. Creating Manual Journals in Oracle Fusion

45. How to View the Created Journals

46. Open/Close GL Periods in Oracle Fusion

47. Manually Reversing Journals in Oracle Fusion

48. Creating GL Journals through Spreadsheet

49. Schedule Run Auto Post program to automatically post GL and Subledger journals in General Ledger.

50. Auto Reverse Journals

51. Translation in Oracle Fusion

52. Implement Journal Approval Process in Oracle Fusion

53. BPM Setup of oracle Fusion journal approval workflow

54. Creating Manual GL Journal for Testing Approval Process

Payables List of Topics Covered in this Book

1.     Creating Business Unit in Oracle Fusion

2.     Defining Location for Business Unit in Oracle Fusion

3.     Defining & Assigning Business Unit Business Function

4.     Creating Inventory Organization for the Business Unit in Oracle Fusion

5.     Creating Payables Calendar in Oracle Fusions

6.     Defining Common Options for payables and Procurement

7.     Manage Invoice Options in Oracle Fusion

8.     Manage Payment Options in Oracle Fusion

9.     Defining Document Sequencing for Payables Invoices in Oracle Fusion

10. Creating Document Sequence for Payables Payments

11. Define Manage Aging Periods in Oracle Fusion

12. Creating Payments Method in Oracle Fusion

13. Creating Payment System in Oracle Fusion

14. Create Transmission Configurations for Payment File Transfer to Bank

15. Creating Payment Process Profile in Oracle Fusion

16. How to Create the Custom Payment File Format/Template in Oracle Fusion

17. How to generate the custom check printing format from Payment Process Profile in Oracle fusion

18. Steps to Install Printer in Oracle Cloud BIP reports: Printer Setup in  Oracle.

19. Creating Internal Banks for Payments in Oracle Fusion

20. Creating Internal Bank Accounts (Important Step -Payment Document)

21. Creating Suppliers in Oracle Fusion

22. Roles Required to access the supplier roles in oracle Fusion

23. Manage Procurement Agents for Supplier Sites Creations.

24. Creating Supplier Address

25. Creating Supplier Sites in Oracle Fusion:

26. Creating Banking Information for Supplier in Oracle Fusion

27. Creating Payables Invoice in Oracle Fusion

28. How to Apply Invoice Hold in Oracle Fusion

29. How to Validate Payables (AP) Invoices in Oracle Fusion Through ESS Job.

30. How to Apply Invoices Hold at Supplier and Supplier site Level

31. Payables Invoice Adjustment/Edit Distribution in Oracle Fusion

32. Payables Invoice Cancellation Rules and Steps in Oracle Fusion

33. Create 2 Way PO-Matched Invoices in Oracle Fusion

34. How to Create PO Matched Payables Credit Memo in Oracle Fusion

35. Payables Period Close Process in Oracle Fusion

36. Payables Open Close Period in Oracle Fusion

37. Payables Unaccounted Transactions and Sweep Report in Oracle Fusion

38. Mass Addition Process in Oracle Fusion

39. Payables to General Ledger AP GL Reconciliation Process in Oracle Fusion

40. Standard Payables Reports in Fusion Payables

41. How to Make Payment in Oracle Fusion by Payment Process Request

42. Payment Process Request Batch Creating in Oracle Fusion

Receivables List of Topics Covered in this Book

1.     Creating Receivable Activities in Oracle Fusion.

2.     Creating Approval Limit for Receivable Activity Adjustments and Receipt Write.

3.     Manage Receivables Document Sequences

4.     Creating Receivables Transaction Type in Oracle Fusion

5.     Creating Transaction Sources in Oracle Fusion

6.     Creating Remit to Address in Oracle fusion

7.     Receivables System Options in Oracle Fusion : How to do AR setups in Oracle Fusion through System Options

8.     What is Receipt Class in Oracle Fusion. Setups for the Receipt Class

9.     Auto Invoice Line Ordering Rules in Oracle Fusion. Setups for Auto Invoice Line Ordering Rules

10. Auto Invoice Grouping Rule in Oracle Fusion

11. Creating Customer in Oracle Fusion

12. How to Manage Auto Invoice Import Interface from Fusion application

13. Fusion Receivables : How to create AR Transaction in Fusion

14. How To Upload Customers Through Spread Sheet in Oracle Fusion

15. How To Create and Apply Credit Memo to Receivable Invoice In Oracle Fusion

16. How to Create and Apply customer Receipts in Oracle Fusion

17. Create Accounting Process for Receivables in Oracle Fusion

18. How to Review Accounting/Journal Entry for the Oracle receivable Transactions in Oracle Fusion

19. AR Period Close Process and Open Close Accounting Periods in Oracle Fusion

20. Receivables and General Ledger AR GL Reconciliation in Oracle fusion

Fixed Assets List of Topics Covered in this Book

1.     Key Flexfield in Fixed Assets

2.     Creating Asset Key Flexfield

3.     Creating Asset Category Flexfield

4.     Creating Asset Location Flexfield

5.     Creating Value set for Asset Flexfields

6.     Creating Value set values for Asset Flexfields

7.     Creating Assets Key Flex Field structures in Oracle Fusion

8.     Creating Asset Category Flexfield in Oracle Fusion

9.     Creating Asset Location Flexfield in Oracle Fusion

10. Creating System Controls for Fixed Assets

11. Creating Asset Fiscal Year in Oracle Fusion

12. Creating Asset Calendar in Oracle Fusion

13. Creating Fixed Assets Prorate Conventions in Oracle Fusion for Asset Depreciation

14. Creating Fixed Assets Depreciation Methods in Oracle Fusion

15. Creating Fixed Asset Book in Oracle Fusion

16. Manage Fixed Assets Lookups

17. Oracle Fusion Roles required to Access Fixed Assets

18. Creating Data Access to Access the Fixed Assets Roles/Responsibility in Oracle Fusion

19. Creating Asset in Oracle Fusion

20. Capitalize CIP Assets in Oracle Fusion

21. Transfer of Fixed Asset in Oracle Fusion

22. Change Asset category in Oracle Fusion

23. Adjustment of Assets Financial details in Existing Assets in Oracle Fusion

24. Retirement of Asset unit in Oracle Fusion

25. Retirement of Asset Cost in Oracle Fusion

26. Mass Retirement of Asset in Oracle Fusion

27. How to do the Asset Inquiry

28. Run Depreciation in Oracle Fusion

If you have any concern related to this Book , You can contact us

Reconciliation of AP and GL in Oracle Apps R12

Reconciliation of  AP and GL in Oracle Apps R12

In this post , We will be discussed about Reconciliation of  AP and GL in Oracle Apps R12. We will be discuss about reconciliation method uses in oracle apps r12 to reconcile the ap and gl. We will go step by step for the Reconciliation of  AP and GL in Oracle Apps R12. In AP and GL reconciliation , we do reconcile the liability accounts balances between the ap and gl. Here below is the detail explanation about Reconciliation of  AP and GL in Oracle Apps R12.

In Friends , in this post I will share you the SQL queries by which you can easily reconcile from AP and GL in Oracle Apps R12.

Steps for the Reconciliation of  AP and GL in Oracle Apps R12

Step1:- First you need to run this below query from Oracle Apps DB for your AP Liability accounts.In this example I am doing reconciliation of my AP Liability Natural account 24103390 and you can select any dates of the month for which you want to do reconciliation.


select JE_SOURCE,sum(nvl(accounted_dr,0))-sum(nvl(accounted_cr,0)) Balanace from gl_je_headers a1,gl_je_lines a2,gl_code_combinations gcc
 where a1.JE_HEADER_ID=a2.JE_HEADER_ID
 and a2.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and gcc.segment5=24103390
 and effective_date between :p_from_date and :p_to_date
 and A2.status='P'
 GROUP BY JE_SOURCE

This above query will give you the source from which your data is coming in GL. You have to put Manual Invoice amount separate in the sheet to do the reco between AP and GL because manual entry will not come in AP and this entry is directly entered in GL so when you will do the reco you will always get a difference of that manual entry value but by this query you will know this difference.


Step2:- Now that you need to run this query with Account wise and put that in Excel.

select JE_SOURCE,GCC.CONCATENATED_SEGMENTS,sum(nvl(accounted_dr,0))-sum(nvl(accounted_cr,0)) Balanace from gl_je_headers a1,gl_je_lines a2,gl_code_combinations_kfv gcc
 where a1.JE_HEADER_ID=a2.JE_HEADER_ID
 and a2.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and gcc.segment5=24103390
 and effective_date between :p_from_date and :p_to_date
 and A2.status='P'
 GROUP BY JE_SOURCE,GCC.CONCATENATED_SEGMENTS



Step3:- Now run this query which will fetch the Payables/AP data for you Liability accounts.In this example I am doing reconciliation of my AP Liability Natural account 24103390 and you can select any dates of the month for which you want to do reconciliation.

 select CONCATENATED_SEGMENTS,sum(T_LEDGER_dr)-sum(T_LEDGER_Cr) bal from (
 SELECT
CASE WHEN
        XAL.PARTY_ID = (SELECT MAX(DUPLICATE_VENDOR_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID) THEN
        (SELECT MAX(VENDOR_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID)
        ELSE XAL.PARTY_ID
        END PARTY_ID,
        NVL(CASE WHEN
        XAL.PARTY_SITE_ID = (SELECT MAX(DUPLICATE_VENDOR_SITE_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID AND DUPLICATE_VENDOR_SITE_ID = XAL.PARTY_SITE_ID) THEN
        (SELECT MAX(VENDOR_SITE_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID AND DUPLICATE_VENDOR_SITE_ID = XAL.PARTY_SITE_ID)
        ELSE XAL.PARTY_SITE_ID
        END,XAL.PARTY_SITE_ID) PARTY_SITE_ID,
 GCC.CONCATENATED_SEGMENTS,GCC.CODE_COMBINATION_ID,
 0 O_LEDGER_CR,0 O_LEDGER_DR,
 NVL(XAL.ACCOUNTED_CR,0) T_LEDGER_CR,
 NVL(XAL.ACCOUNTED_DR,0) T_LEDGER_DR
 FROM
 XLA.XLA_TRANSACTION_ENTITIES XTE,
 XLA.XLA_AE_HEADERS XAH,
 XLA.XLA_AE_LINES XAL,
 GL_CODE_COMBINATIONS_KFV GCC
 WHERE 1 = 1
 AND :P_APPLICATION_ID=200
 AND XAL.APPLICATION_ID=200
 AND XTE.LEDGER_ID = XAH.LEDGER_ID
 AND XTE.ENTITY_ID = XAH.ENTITY_ID
 AND XTE.APPLICATION_ID = XAL.APPLICATION_ID
 AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
 AND XAL.GL_SL_LINK_TABLE = 'XLAJEL'
 AND ACCOUNTING_CLASS_CODE IN ('LIABILITY','PREPAID_EXPENSE')
 AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
 and xal.CODE_COMBINATION_ID in  (SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS
 WHERE 1=1
 AND SEGMENT5=24103390)
-- AND XAL.PARTY_ID=&VENDOR_ID
 AND NVL(XAL.GL_TRANSFER_MODE_CODE,'N')='S'
 AND TRUNC(XAL.ACCOUNTING_DATE) >= :FROM_DATE
 AND TRUNC(XAL.ACCOUNTING_DATE) <= :TO_DATE
 AND SECURITY_ID_INT_1=:P_ORG_ID)
 group by CONCATENATED_SEGMENTS


Step4:- Now match the accounts fetched with Step 2 and 3 and find the difference.



If you Want to Learn Oracle Fusion , Please Follow the Oracle Fusion Tutorial

Workflow Customization : How to restrict PO approval Notification to send to some specific Employees in the Position Hierarchy.

Workflow Customization : How to restrict PO approval Notification to send to some specific Employees in the Position Hierarchy.


Step1:- Open the PO approval workflow in workflow builder and then open Find Approver Process as below.

Step2:- Go to Get First Manager Approval Hierarchy Function in the process.

Step3:- Copy the name of PLSQL function

PO_REQAPPROVAL_FINDAPPRV1.GETMGR_PO_HIER

 

 

Step4:- Go to this package in Database and open it.

 

Step5:- In this package go to function as below

 

FUNCTION GetMgrHRHier(itemtype VARCHAR2, itemkey VARCHAR2)

 

Step6:- You can see below here in this function , workflow is setting the value of Next approver.

 

 

 

 

 

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

 

Step7:-  These are two below cursors are using to set the next approver. You just to put Not in condition in their where conditions with that employee Id's for which you don't want to sent approval Notification.

 

 

CURSOR c1 (p_empid NUMBER, p_business_group_id NUMBER) IS

SELECT pafe.supervisor_id

FROM   Per_All_Assignments_f pafe,  -- <BUG 6615913>

       Per_All_People_f ppfs,       -- <BUG 6615913>

       Per_All_Assignments_f pafs,  -- <BUG 6615913>

       per_person_types_v ppts,

       per_person_type_usages_f pptu

WHERE  pafe.business_group_id = p_business_group_id

       AND pafe.person_id = p_empid

       AND Trunc(SYSDATE) BETWEEN pafe.Effective_Start_Date

                              AND pafe.Effective_End_Date

       AND pafe.Primary_Flag = 'Y'

       AND pafe.Assignment_Type IN ('E','C')

       AND ppfs.Person_Id = pafe.Supervisor_Id

       AND Trunc(SYSDATE) BETWEEN ppfs.Effective_Start_Date

                              AND ppfs.Effective_End_Date

       AND pafs.Person_Id = ppfs.Person_Id

       AND Trunc(SYSDATE) BETWEEN pafs.Effective_Start_Date

                              AND pafs.Effective_End_Date

       AND pafs.Primary_Flag = 'Y'

       AND pafs.Assignment_Type IN ('E','C')

       AND pptu.Person_Id = ppfs.Person_Id

       AND ppts.person_type_id = pptu.person_type_id

       AND ppts.System_Person_Type IN ('EMP','EMP_APL','CWK');  



Part2: PO Workflow Customization to send Workflow Notification to Buyer Supervisor in Oracle Apps.

In this post I will share pending steps Part2: PO Workflow Customization to send Workflow Notification to Buyer Supervisor in Oracle Apps.

For part 1 of this topic please go through this below url.

http://rpforacle.blogspot.in/2018/01/part1-po-workflow-customization-to-send-notifications-to-buyer-supervisor.html

Step10 :-  Give the values for Notification function as below  

Step11:- Now Click on Node Tab of the same Notification functions as below.

select performer as below.

 

 

Step 12:- Now again Right click on the process window and click on New Function as below.

 

 

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.

Step13:- Create end function as below and give values as below.

Step14:- You Process will be designed as below. After that you need to bounce workflow builder and you can test the changes in Your Development Instance.

Please share your thoughts and comments on that.



If you want to see Other OAF Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2013/03/oaf_10.html

If you want to see  Oracle Fusion Related Posts , Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-fusion-learning-tutorial.html

If you want to Learn Oracle Workflow Builder, Please visit Below URL.
https://rpforacle.blogspot.in/2018/01/oracle-workflow-learning-tutorial-1.html

Oracle APPS R12 Creditor Ageing SQL query

Oracle APPS R12 Creditor Ageing SQL query


select  v.vendor_id,hp.party_name,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND  i.invoice_id  NOT IN  (62575)
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
--AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
--AND     i.payment_status_flag in ('N','P')
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-30) and to_date(trunc(sysdate))) ONE,
-----------------------ONE---------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-45) and to_date(trunc(sysdate-31))) TWO,
------------------------------------------TWO-------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND  i.invoice_id  NOT IN  (62575)
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-60) and to_date(trunc(sysdate-46))) THREE,
------------------------------------------------THREE'-------------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
--AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
--AND     i.payment_status_flag in ('N','P')
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-90) and to_date(trunc(sysdate-61))) FOUR,
--------------------------------------FOUR-------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-120) and to_date(trunc(sysdate-91)))  FIVE,
---------------------------FIVE---------------------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-180) and to_date(trunc(sysdate-121))) SIX,
------------------------SIX----------------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) between to_date(trunc(sysdate)-365) and to_date(trunc(sysdate-181))) SEVEN,
------------SEVEV----------------
(select SUM(case when sign(amount_remaining)=-1 then (-1*amount_remaining)-amount_applied else amount_remaining-amount_applied end) net_bal from (
select  v.vendor_id,hp.party_name,i.INVOICE_AMOUNT,i.INVOICE_NUM,i.invoice_id,I.INVOICE_DATE,i.gl_date,I.INVOICE_TYPE_LOOKUP_CODE,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE,ps.amount_remaining,
case when invoIce_type_lookup_code='PREPAYMENT' THEN i.INVOICE_AMOUNT-nvl((SELECT
  sum((-1)*(ail.amount - NVL(ail.included_tax_amount,0)))
  FROM   AP_INVOICES_ALL ai,AP_INVOICES_ALL ai2,
  AP_INVOICE_LINES_ALL ail,
  AP_SUPPLIERS pv
  WHERE ai.invoice_id = ail.invoice_id
  AND  ai2.invoice_id= ail.prepay_invoice_id
  AND   ail.amount< 0
  AND   NVL(ail.discarded_flag,'N') <> 'Y'
  AND   ail.line_type_lookup_code= 'PREPAY'
  AND   ai.vendor_id = pv.vendor_id
  AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')
  AND   ai2.invoice_id =i.INVOICE_ID),0) ELSE 0 END amount_applied
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
--and i.vendor_id=1231
--AND     ((to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') - decode(:P_AGING_DATE,'D',ps.due_date,'I',I.INVOICE_DATE)) --pks
          --    between :C_MINDAYS and  :C_MAXDAYS)
--AND     i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
--AND     (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1))  != 0
--AND     i.payment_status_flag in ('N','P')
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
order by I.GL_DATE,i.INVOICE_NUM)
where vendor_id=v.vendor_id
and to_date(gl_date) < to_date(trunc(sysdate)-366))  EIGHT
FROM    ap_payment_schedules_ALL ps,
        ap_invoices_ALL i,
        hz_parties hp,
        ap_suppliers v,
        po_vendor_sites_all vs
WHERE   i.invoice_id = ps.invoice_id
AND     i.party_id = hp.party_id
AND    hp.party_id = v.party_id (+)
AND     i.vendor_site_id = vs.vendor_site_id (+)
AND     i.vendor_site_id=nvl(:p_site_id, i.vendor_site_id)
AND     i.cancelled_date IS NULL
and  APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
           (
            I.INVOICE_ID
           ,I.INVOICE_AMOUNT
           ,I.PAYMENT_STATUS_FLAG
           ,I.INVOICE_TYPE_LOOKUP_CODE
           ) <>'NEVER APPROVED'
AND    ( (i.INVOICE_TYPE_LOOKUP_CODE<>'PREPAYMENT' and i.payment_status_flag in ('N','P')) or (i.INVOICE_TYPE_LOOKUP_CODE='PREPAYMENT' and i.payment_status_flag='Y' ))
--and      v.VENDOR_TYPE_LOOKUP_CODE=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,v.VENDOR_TYPE_LOOKUP_CODE)
and nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X')=nvl(:P_VENDOR_TYPE_LOOKUP_CODE,nvl(v.VENDOR_TYPE_LOOKUP_CODE, 'X'))
--&P_ORDER_BY, I.GL_DATE
group by
v.vendor_id,hp.party_name,vs.VENDOR_SITE_CODE,VENDOR_TYPE_LOOKUP_CODE

How to Migrate OAF personalizations from One Instance to Another Instance Automatically.

Hi Friends ,In this post I will show you how you can migrate your OAF personalization from one instance to another instance automatically.

If you are working on any project and  you have done lot of personalization in OAF pages now you are moving all the things to production so there is one option you can create personalization manually in production but this is a time consuming job and risky too because you can do mistakes so for this I am showing you the easy  quick and error free method to migrate OAF personalization from test to Production.

In Functional Administrator responsibility , you can directly go to Personalization Tab and there you can migrate your Personalizations


Step1 : -  I am sharing below profile , you have to set valid server path of you instance from where you want to copy the Personalizations.

'FND: Personalization Document Root Path'

As an example I set path /usr/tmp

 

Step2:- Go to Functional Administrator.

 

Step3- Go to Personalization Tab

Then Go to Personalization Repository.

 

 

 

 

Step 4:- Then enter the Document path/ page path for which you want to download all your Personalizations as below.
Select pages and click on Export to File System button.

This will save the Personalization registration in a file and will put this file under  path '/usr/tmp'

'FND: Personalization Document Root Path'

As an example I set path /usr/tmp


You have to copy this file in your desktop.

 

Step 8:- After Copy the file in you desktop. Then you have move this file to your target system server path.

You have to copy that path where you have moved this personalization registration file in target system/production.

Then again open the Target system application and set the below profile
'FND: Personalization Document Root Path'  to the path where you have moved the personalization file in your target system.

Step9 :- Then again go to Target system application and go to functional administrator and then personalization tab and Click on Exported Personalization's and upload all the personalization registrations.

After this you can check your personalization has been created in your targeted application.