AOL: Script to reset fnd user login password from backend in Oracle

AOL: Script to reset password from backend in Oracle 

The below script is a ready to use script, you can set the new password according to your company password policy. 

/************************************************************

*PURPOSE: To change the   password of a user from backend(DB)   *

*AUTHOR: Arokia Antony Jayaraj                              *

*************************************************************/

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER('SYSADMIN');

  v_new_password VARCHAR2(30):= 'oracle123';

  v_status       BOOLEAN;

BEGIN

  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, 

                                              newpassword => v_new_password 

                                            );

  IF v_status  THEN

    dbms_output.put_line ('The password reset successfully for the entered User:'||v_user_name);

    COMMIT;

  ELSE

    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    ROLLBACK;

  END IF;

END;

 

HR: Organization Classifications Query

 This query help you to find the different Organization Classification information of an Org.

1.  select distinct t.ORG_INFORMATION_CONTEXT from hr_organization_information t

2.     SELECT o.organization_id,

               o.organization_id,

               otl.name,

               o.date_from,

               o.date_to,

               o.internal_address_line,

               o.location_id,

               o.comments,

               o4.org_information1

          FROM hr_all_organization_units    o,

               hr_all_organization_units_tl otl,

               hr_organization_information  o2,

               hr_organization_information  o3,

               hr_organization_information  o4

         WHERE o.organization_id = otl.organization_id

           AND o.organization_id = o2.organization_id(+)

           AND o.organization_id = o3.organization_id

           AND o.organization_id = o4.organization_id

           AND o3.org_information_context = 'Business Group Information' 

           AND o2.org_information_context(+) = 'Work Day Information' -- Get a distinct of this from the above query 

           AND o4.org_information_context = 'CLASS' -- This means the Classification

           AND o4.org_information1 = 'HR_BG' -- BG Business group 

           AND o4.org_information2 = 'Y';

AOL: Query to Check DB NLS Language settings and parameters

 

Query to Check DB NLS Language settings and parameters:

 SELECT    (SELECT VALUE

             FROM nls_database_parameters

            WHERE parameter = 'NLS_LANGUAGE')

       || '_'

       || (SELECT VALUE

             FROM nls_database_parameters

            WHERE parameter = 'NLS_TERRITORY')

       || '.'

       || (SELECT VALUE

             FROM nls_database_parameters

            WHERE parameter = 'NLS_CHARACTERSET') LANGUAGE

  FROM DUAL;

HR: Queries related to Absence (PENDING,APPROVED-HR_API_TRANSACTIONS)

 The below list of queries fetch the list of absences 

--To get pending leaves:

 

SELECT hat.transaction_id, hats.transaction_step_id

  FROM hr_api_transactions hat, hr_api_transaction_steps hats

 WHERE hat.transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

   AND hat.transaction_group = 'ABSENCE_MGMT'

   AND hat.transaction_identifier = 'ABSENCES'

   AND hat.transaction_ref_id IS NOT NULL

   AND hat.status = 'Y'

   AND hat.transaction_id = hats.transaction_id

   AND hat.CREATOR_PERSON_ID = :p_person_id;

   

-- Query to get Approved Leaves :

SELECT pat.NAME absence_type, paa.date_start leave_start_date,

                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,

                paa.abs_information2, paa.abs_information3,paa.abs_information4

           FROM per_absence_attendances paa,

                per_absence_attendance_types pat,

                per_all_people_f papf

          WHERE paa.absence_attendance_type_id =

                                                pat.absence_attendance_type_id

            AND papf.person_id = paa.person_id

            AND TRUNC (paa.date_start) BETWEEN TRUNC

                                                    (papf.effective_start_date)

                                           AND TRUNC (papf.effective_end_date)

            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)

                                   AND TRUNC (NVL (paa.date_end,

                                                   paa.date_start

                                                  )

                                             )

            AND papf.person_id = :p_person_id;

-- List of Absence types and their additional informations 

 SELECT pat.NAME absence_type, paa.date_start leave_start_date,

                paa.date_end leave_end_date, paa.absence_days, paa.abs_information_category,

                paa.abs_information2, paa.abs_information3,paa.abs_information4

           FROM per_absence_attendances paa,

                per_absence_attendance_types pat,

                per_all_people_f papf

          WHERE paa.absence_attendance_type_id =

                                                pat.absence_attendance_type_id

            AND papf.person_id = paa.person_id

            AND TRUNC (paa.date_start) BETWEEN TRUNC

                                                    (papf.effective_start_date)

                                           AND TRUNC (papf.effective_end_date)

            AND TRUNC (p_date) BETWEEN TRUNC (paa.date_start)

                                   AND TRUNC (NVL (paa.date_end,

                                                   paa.date_start

                                                  )

                                             )

            AND papf.person_id = :p_person_id;

HR: Query to Get Organizational Hierarchy of an Organization

This query lists the Organization structure from the below navigation : 

Global HRMS Manager -> Work Structures -> Hierarchy(Or Global Hierarchy)

 

SELECT *

  FROM (SELECT (lpad(' ', (lvl - 1) * 5, ' ') || haou_c.name) org_name,

               apps.hr_general.decode_lookup('ORG_TYPE', haou_c.type) TYPE,

               org_hier.*

          FROM hr_all_organization_units haou_c,

               (SELECT hier.organization_id_parent,

                       hier.organization_id_child,

                       LEVEL lvl,

                       sys_connect_by_path(hier.organization_id_child, '/') org_level,

                       sys_connect_by_path(LEVEL, '/') hier_level

                  FROM per_org_structure_elements hier,

                       (SELECT vers.org_structure_version_id

                          FROM per_org_structure_versions_v  vers,

                               per_organization_structures_v struct

                         WHERE struct.business_group_id = 81

                           AND struct.name = 'your Hierarchy Structure name'

                           AND vers.organization_structure_id =

                               struct.organization_structure_id

                           AND struct.business_group_id =

                               vers.business_group_id

                           AND SYSDATE BETWEEN vers.date_from AND

                               nvl(vers.date_to, SYSDATE)

                           AND rownum = 1) orgstr

                 WHERE hier.org_structure_version_id =

                       orgstr.org_structure_version_id

                   AND hier.business_group_id = 81

                 START WITH hier.organization_id_parent =

                            (SELECT a.organization_id

                               FROM hr_all_organization_units a

                              WHERE a.organization_id = 81)

                        AND hier.org_structure_version_id =

                            orgstr.org_structure_version_id

                        AND hier.business_group_id = 81

                CONNECT BY PRIOR

                            organization_id_child = organization_id_parent) org_hier

         WHERE haou_c.organization_id = org_hier.organization_id_child

         ORDER BY org_hier.org_level);

AOL: Query to get values of a Flex Field

The query lists all the values of a particular Flex structure:

 SELECT ffvs.flex_value_set_id,

       ffvs.flex_value_set_name,

       ffvs.description         set_description,

       ffvs.validation_type,

       ffv.flex_value           program,

       ffvt.description         pg_description,

       ffv.enabled_flag,

       ffv.last_update_date,

       ffv.last_updated_by,

       ffv.attribute1,

       ffv.attribute2,

       ffv.attribute3

  FROM fnd_flex_value_sets ffvs,

       fnd_flex_values     ffv,

       fnd_flex_values_tl  ffvt

 WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id

   AND ffv.flex_value_id = ffvt.flex_value_id

   AND ffvt.language = userenv('lang')

   AND flex_value_set_name LIKE '%YOUR VALUE SET NAME%'

;

GL: Accounting Flow of Purchase Orders/Receipts/Invoices P2P process

 

 

Accounting Flow

for

Purchase Orders, Receipts and Matched Invoices in an Encumbrance Environment

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This document details the accounting entries that are expected for purchase orders with Expense or Inventory destinations, receipts and matched invoices. It also considers the two choices of accrual available for Expense items in an encumbrance-enabled environment.

 

Expense items can be accrued at

a)     Period-end

b)     On Receipt

                                        Inventory Items can ONLY be accrued at

a)     Receipt

 

 This document consists of the following case studies/scenarios.

 

Accrue on receipt: (APO)

Case 1: Expense Destination/Create, Approve, Reserve/Receive/Invoice

Case 2: Expense Destination/Create, Approve, Reserve/Invoice/Receive

Case 3: Expense Destination/Create, Approve, Reserve/Receive/Invoice at a price greater than PO price

Case 4: Expense Destination/Create, Approve, Reserve/Receive/Invoice at a quantity greater than PO quantity

Case 5: Inventory Destination/ Create, Approve, Reserve/Receive/Invoice

Case 6: Inventory Destination/ Create, Approve, Reserve/Invoice/Receive

 

Accrue at Period-end: (APE)

Case 1: Create PO /Receive PO /Deliver PO /Invoice/Run the period end program              

Case 2: Create PO /Invoice/Receive PO/Deliver PO/Run the period end program                

Case 3: Create PO/Receive PO/Deliver PO/Run the period end program/Invoice     

Case 4: Create PO/Receive PO/Run the Period end program/Deliver PO/Run the

            period end program/Invoice

Case 5: Create PO /Receive PO /Deliver PO /Invoice at a price greater than PO

            Price/Run the period end program

Case 6: Create PO /Receive PO /Deliver PO /Invoice at a quantity greater than PO

            Quantity/Run the period end Program          

 

The following Pages will explain each case in detail:

LEGEND: AOR= Accrue On Receipt, APE= Accrue at Period-End

 

 

 

 

 

 

AOR-Case 1:

Assumptions:

Accrue on receipt = Y

Destination type = Expense

Item = expense Item

 

 

  

     Sequence of actions:

·        Create, Approve, Reserve PO

·        Receive and Deliver the PO

·        Invoice Validation after matching

·        Create Accounting for invoice

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax)*	GL_BC_PACKETS	Encumbrance
2. Receive the PO	Receiving Inventory a/c DR @ PO price (base + NR tax)++	RRSL/GLI/GL_JE_LINES	Actual
	AP Expense accrual a/c CR @ PO price (base+ NR tax) ^^
3. Deliver the PO	Expense Charge a/c DR @ PO price (base + NR tax)	RRSL/GLI/GL_JE_LINES	Actual
	Receiving Inventory a/c CR @ PO price (base + NR tax)++
	Budget a/c CR @ PO price (base + NR tax)*		Encumbrance
4. Invoice Validation	REC Tax and NR tax DR @ tax amount++	GL_BC_PACKETS	Encumbrance
5. Create Accounting for Invoice	AP Expense accrual a/c DR @ PO price excluding tax and tax DR @ tax rate separately ^^	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price
	REC Tax and NR tax CR @ tax amount++	AP_ENCUMBRANCE_ LINES_ALL	Encumbrance

                                                                                                                                               

Note:

·        The corresponding offsetting records have the same symbol in order to more easily identify which debits and credits offset each other. (^, +, *)

·        PO base price refers to received quantity * unit price in PO Lines.

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * PO line unit price)

·        REC tax refers to Recoverable Tax calculated for the (received quantity * PO line unit price)

·        RRSL - RCV_RECEIVING_SUB_LEDGER

• GLI – GL_INTERFACE

 

 

 

 

AOR-Case 2:

Assumptions:

Accrue on receipt = Y

Destination type = Expense

Item = expense Item

 

 

      Sequence of actions:

·        Create, Approve, Reserve PO

·        Invoice Validation after matching

·        Create Accounting for invoice

• Receive and Deliver the PO

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) $$	GL_BC_PACKETS	Encumbrance
2. Invoice Validation	REC Tax and NR tax DR @ tax amount *	GL_BC_PACKETS	Encumbrance
3. Create Accounting for invoice	AP Expense accrual a/c DR @ PO price excluding tax and tax DR @ tax rate separately +++	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price
	REC Tax and NR tax CR @ tax amount *	AP_ENCUMBRANCE_LINES_ALL	Encumbrance
4. Receive the PO	Receiving Inspection a/c DR @ PO price (base + NR tax) ^^^^	RRSL/GLI/GL_JE_ LINES	Actual
	AP Expense accrual a/c CR @ PO price (base+ NR tax) +++
5. Deliver the PO	Expense Charge a/c DR @ PO price (base + NR tax)	RRSL/GLI/GL_JE_ LINES	Actual
	Receiving Inspection a/c CR @ PO price (base + NR tax) ^^^^
	Budget a/c CR @ PO price (base + NR tax)  $$		Encumbrance

 

Note:

·        Corresponding offsetting accounts have same symbols

·         PO base price refers to received quantity * unit Price in PO lines

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity *PO line unit price)

·        REC tax refers to Recoverable Tax calculated for the (received quantity *PO line unit price)

·        RRSL - RCV_RECEIVING_SUB_LEDGER

• GLI – GL_INTERFACE

 

 

AOR-Case 3:

Assumptions:

Accrue on receipt = Y

Destination type = Expense

Item = expense Item

 

 

Sequence of actions:

Create, Approve, Reserve PO

Receive and Deliver PO

Create Invoice at higher unit price than PO

Invoice Validation

Create Accounting

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) ^^^^	GL_BC_PACKETS	Encumbrance
2. Receive the PO	Receiving Inventory a/c DR @ PO price (base + NR tax) $$$	RRSL/GLI/GL_JE_ LINES	Actual
	AP Expense accrual a/c CR @ PO price (base+ NR tax) **
3. Deliver the PO	Expense Charge a/c DR @ PO price (base + NR tax)	RRSL/GLI/GL_JE_ LINES	Actual
	Receiving Inventory a/c CR @ PO price (base + NR tax) $$$
	Budget a/c CR @ PO price (base + NR tax) ^^^^		Encumbrance
4. Invoice Validation at higher price	REC Tax and NR tax DR @ tax amount #	GL_BC_PACKETS	Encumbrance
	IPV a/c DB @ difference in price  &
5. Create Accounting for Invoice	AP Expense accrual a/c DR @ PO price excluding tax and tax DR @ tax rate separately **	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price
	IPV a/c DR @ difference in price
	REC Tax and NR tax CR @ tax amount #	AP_ENCUMBRANCE_LINES_ALL	Encumbrance
	IPV a/c CR @ difference in price  &

Note:

·        Corresponding offsetting accounts have same symbols

·        PO base price refers to received quantity * unit Price in PO lines

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * PO line unit price)

·        REC tax refers to Recoverable Tax calculated for the (received quantity * PO line unit price)

• RRSL - RCV_RECEIVING_SUB_LEDGER
• GLI – GL_INTERFACE

 

 

AOR-Case 4:

Assumptions:

Accrue on receipt = Y

Destination type = Expense

Item = expense Item

 

 

 

     

      Sequence of actions:

·        Create, Approve, Reserve PO

·        Receive and Deliver PO

·        Create Invoice at higher quantity than PO

·        Invoice Validation

• Create Accounting

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) &	GL_BC_PACKETS	Encumbrance
2. Receive the PO	Receiving Inspection a/c DR @ PO price (base + NR tax) ##	RRSL/GLI/GL_JE_ LINES	Actual
	AP Expense accrual a/c CR @ PO price (base+ NR tax) ^^^
3. Deliver the PO	Expense Charge a/c DR @ PO price (base + NR tax)	RRSL/GLI/GL_JE_ LINES	Actual
	Receiving Inspection a/c CR @ PO price (base + NR tax) ##
	Budget a/c CR @ PO price (base+ NR tax) &		Encumbrance
4. Invoice Validation for higher quantity *See additional info below	REC Tax and NR tax DR @ tax amount ****	GL_BC_PACKETS	Encumbrance
5. Create Accounting for Invoice	AP Expense accrual a/c DR @ invoice price excluding tax and tax DR @ tax rate separately  ^^^	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price
	REC Tax and NR tax CR @ tax amount ****	AP_ENCUMBRANCE_LINES_ALL	Encumbrance

 

Note:

• Corresponding offsetting accounts have same symbols

• PO base price refers to received quantity * unit Price in PO line
• NR tax refers to Non Recoverable Tax calculated for the (received quantity * PO line unit price)
• REC tax refers to Recoverable Tax calculated for the (received quantity * PO line unit price)

• RRSL - RCV_RECEIVING_SUB_LEDGER
• GLI – GL_INTERFACE
• No separate accounting entry for Quantity Variation (#4) will be seen here

 

 

 

AOR-Case 5:

Assumptions:

Accrue on receipt = Y

Destination type = Inventory

Item = Inventory Item

 

 

      Sequence of actions:

·        Create, Approve, Reserve PO

·        Receive and Deliver the PO

·        Invoice Validation after matching

·        Create Accounting for invoice

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) #	GL_BC_PACKETS	Encumbrance
2. Receive the PO	Receiving Inspection a/c DR @ PO price (base + NR tax)  **	RRSL/GLI/GL_JE_LINES	Actual
	AP Inventory accrual a/c CR @ PO price (base+ NR tax)  %%%
3. Deliver the PO	Material Valuation a/c DR @ std cost	MTL_TRANSACTION_ ACCOUNTS	Actual (Account line type-1)
	Receiving Inspection a/c DR @ PO price (base + NR tax)  **		Actual (Account line type-5)
	PPV a/c CR/DR @ difference in price		Actual (Account line type-6)
	Budget a/c CR @ PO price (base+ NR tax) #		Encumbrance (Account line type- 15)
4. Invoice Validation	REC Tax and NR tax DR @ tax amount $$$$	GL_BC_PACKETS	Encumbrance
5. Create Accounting for Invoice	AP Inventory accrual a/c DR @ PO price excluding tax and tax DR @ tax rate separately %%%	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price
	REC Tax and NR tax CR @ tax amount $$$$	AP_ENCUMBRANCE_ LINES_ALL	Encumbrance

 

Note:

·        Corresponding offsetting accounts have same symbols

·        PO base price refers to received quantity * unit Price in PO line

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * PO line unit price)

·        REC tax refers to Recoverable Tax calculated for the (received quantity * PO line unit price)

·        RRSL - RCV_RECEIVING_SUB_LEDGER

• GLI – GL_INTERFACE

 

 

 

 

AOR-Case 6:

Assumptions:

Accrue on receipt = Y

Destination type = Inventory

Item = Inventory Item

 

 

      Sequence of actions:

·        Create, Approve, Reserve PO

·        Invoice Validation after matching

·        Create Accounting for invoice

• Receive and Deliver the PO

 

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) ####	GL_BC_PACKETS	Encumbrance
2. Invoice Validation	REC Tax and NR tax DR @ tax amount*	GL_BC_PACKETS	Encumbrance
3. Create Accounting for invoice	AP Inventory accrual a/c DR @ PO price excluding tax and tax DR @ tax rate separately @@@	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price
	REC Tax and NR tax CR @ tax amount*	AP_ENCUMBRANCE_LINES_ALL	Encumbrance
4. Receive the PO	Receiving Inspection a/c DR @ PO price (base + NR tax)  ^^^^	RRSL/GLI/GL_JE_ LINES	Actual
	AP Inventory accrual a/c CR @ PO price (base+ NR tax) @@@
5. Deliver the PO	Material Valuation a/c DR @ std cost	MTL_TRANSACTION_ACCOUNTS	Actual (Account line type-1)
	Receiving Inspection a/c CR @ PO price (base + NR tax)  ^^^^		Actual (Account line type-5)
	PPV a/c CR/DR @ difference in price		Actual (Account line type-6)
	Budget a/c CR @ PO price (base + NR tax) ####		Encumbrance (Account line type- 15)

 

Note:

·        Corresponding offsetting accounts have same symbols

·        PO base price refers to received quantity * unit Price in PO line

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * PO line unit price)

·        REC tax refers to Recoverable Tax calculated for the (received quantity * PO line unit price)

·        RRSL - RCV_RECEIVING_SUB_LEDGER

• GLI – GL_INTERFACE

Accrue at Period End

APE-Case 1:

Assumptions:

Accrue on receipt = N

Destination type = Expense

Item = expense Item

Purchase Encumbrance type and Invoice    Encumbrance type are different

 

Sequence of actions:

Create, Approve, Reserve PO

Receive and Deliver the PO

Invoice Validation after matching

Create Accounting for invoice

Run the Period End Program

 

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) *	GL_BC_PACKETS	Encumbrance
2. Receive the PO	No Accounting Transactions	N/A	N/A
3. Deliver the PO	No Accounting Transactions	N/A	N/A
4. Invoice Validation	Budget a/c CR @ PO price (base + NR tax) *	GL_BC_PACKETS	Encumbrance
	REC Tax and NR tax DR @ tax amount ^^
	Budget a/c DR @ invoice price excluding NR tax-INV Enc  &&&
5. Create Accounting for invoice	Expense charge a/c DR @ invoice price excluding tax and tax DR @ tax rate separately	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price including NR tax
	REC Tax and NR tax CR @ tax amount ^^	AP_ENCUMBRANCE_ LINES_ALL	Encumbrance
	Budget a/c CR @ invoice price excluding NR tax  &&&
6. Run the Period end program	No Accounting Transactions	N/A	N/A
7. Manual Reversal of journal entry	Not Required since there are no accrual entries	N/A	N/A

 

Note:

·        Corresponding offsetting accounts have same symbols

·        PO base price refers to received quantity * unit Price in PO line

• NR tax refers to Non Recoverable Tax calculated for the (received quantity * po line unit price)
• REC tax refers to Recoverable Tax calculated for the (received quantity * po line unit price)

 

 

 

APE-Case 2:

Assumptions:

Accrue on receipt = N

Destination type = Expense

Item = expense Item

Purchase Encumbrance type and Invoice Encumbrance type are different

 

Sequence of actions:

Create, Approve, Reserve PO

Invoice Validation after matching

Create Accounting for invoice

Receive and Deliver the PO

Run the Period end Program

 

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1.PO is reserved	Budget a/c DR @ PO price (base + NR tax)  ####	GL_BC_PACKETS	Encumbrance
2.Invoice Validation	Budget a/c CR @ PO price (base + NR tax)  ####	GL_BC_PACKETS	Encumbrance
	REC Tax and NR tax DR @ tax amount***
	Budget a/c DR @ invoice price excluding NR tax- INV Enc  %%
3.Create Accounting for invoice	Expense charge a/c DR @ invoice price excluding tax and tax DR @ tax rate separately	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price including NR tax
	Budget a/c CR @ invoice price excluding NR tax  %%	AP_ENCUMBRANCE_ LINES_ALL	Encumbrance
	REC Tax and NR tax CR @ tax amount***
4.Receive the PO	No Accounting Transactions	N/A	N/A
5. Deliver the PO	No Accounting Transactions	N/A	N/A
6.Run the Period end program	No Accounting Transactions	N/A	N/A
7. Manual Reversal of journal entry	Not Required since there are no accrual entries	N/A	N/A

 

Note:

·        Corresponding offsetting accounts have same symbols.

·        PO base price refers to received quantity * unit Price in PO line

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * po line unit price)

• REC tax refers to Recoverable Tax calculated for the (received quantity * po line unit price)

APE-Case 3:

Assumptions:

Accrue on receipt = N

Destination type = Expense

Item = expense Item

Purchase Encumbrance type and Invoice Encumbrance type are different

 

Sequence of actions:

Create, Approve, Reserve PO

Receive and Deliver the PO

Run the Period End Program

Invoice Validation after matching

Create Accounting for invoice

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1.PO is reserved	Budget a/c DR @ PO price (base + NR tax) $	GL_BC_ PACKETS	Encumbrance
2.Receive the PO	No Accounting Transactions	N/A	N/A
3.Deliver the PO	No Accounting Transactions	N/A	N/A
4.Run the Period end program	Expense Charge a/c DR @ PO Price (base + NR tax) #	RRSL/GLI	Actual
	AP Expense accrual a/c CR @ PO Price (base + NR tax) &
	Budget a/c CR @ PO price (base + NR tax) $		Encumbrance
5.Manual Reversal of journal entry (Both actual and encumbrance)	Expense Charge a/c CR @ PO Price (base + NR tax) #	GL_JE_LINES	Actual
	AP Expense accrual a/c DR @ PO Price (base + NR tax) &
	Budget a/c DR @ PO price (base + NR tax) ++
6.Invoice Validation	Budget a/c CR @ PO price (base + NR tax) ++	GL_BC_ PACKETS	Encumbrance
	Budget a/c DR @ invoice price excluding NR tax - INV Enc **
	REC Tax and NR tax DR @ tax amount ^^
7.Create Accounting for invoice	Expense charge a/c DR @ invoice price excluding tax and tax DR @ tax rate separately	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price including NR tax
	REC Tax and NR tax CR @ tax amount ^^	AP_ENCUMBRANCE_LINES_ALL	Encumbrance
	Budget a/c CR @ invoice price excluding NR tax **

 

Note:

·        Corresponding offsetting accounts have same symbols.

·        PO base price refers to received quantity * unit Price in PO line

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * po line unit price)

·        REC tax refers to Recoverable Tax calculated for the (received quantity * po line unit price)

·        RRSL – RCV_RECEIVING_SUB_LEDGER

·        GLI – GL_INTERFACE

 

APE-Case 4:

Assumptions:

Accrue on receipt = N

Destination type = Expense

Purchase Encumbrance type and Invoice Encumbrance type are different

 

Sequence of Actions:

Create, Approve, Reserve PO/Receive the PO and Run the Period End Program

Deliver the PO and Run the Period End Program

Invoice Validation and Create Accounting

 

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1.PO is reserved	Budget a/c DR @ PO price (base + NR tax)  ^	GL_BC_PACKETS	Encumbrance
2.Receive the PO	No Accounting Transactions	N/A	N/A
3.Run the Period end program	Expense Charge a/c DR @ PO Price (base + NR tax) +	RRSL/GLI	Actual
	AP Expense accrual a/c CR @ PO Price (base + NR tax)  &
	Budget a/c CR @ PO price (base + NR tax) ^		Encumbrance
4.Manual Reversal of journal entry (Both actual and encumbrance)	Expense Charge a/c CR @ PO Price (base + NR tax) +	GL_JE_LINES	Actual
	AP Expense accrual a/c DR @ PO Price (base + NR tax) &
	Budget a/c DR @ PO price (base + NR tax) *
5.Deliver the PO	No Accounting Transactions	N/A	N/A
5. Deliver the PO	No Accounting Transactions	N/A	N/A
6. Run the Period end program	Expense Charge a/c DR @ PO Price (base + NR tax) +	RRSL/GLI	Actual
	AP Expense accrual a/c CR @ PO Price (base + NR tax) &
	Budget a/c CR @ PO price (base + NR tax) *		Encumbrance
7.Manual Reversal of journal entry (Both actual and encumbrance)	Expense Charge a/c CR @ PO Price (base + NR tax) +	GL_JE_LINES	Actual
	AP Expense accrual a/c DR @ PO Price (base + NR tax) &
	Budget a/c DR @ PO price (base + NR tax) #
8.Invoice Validation	Budget a/c CR @ PO price (base + NR tax) #	GL_BC_PACKETS	Encumbrance
	Budget a/c DR @ invoice price excluding NR tax- INV Enc  /////
	REC Tax and NR tax DR @ tax amount \\\\
9.Create Accounting for invoice	Expense charge a/c DR @ invoice price excluding tax and tax DR @ tax rate separately	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price including NR tax
	REC Tax and NR tax CR @ tax amount \\\\	AP_ENCUMBRANCE_ LINES_ALL	Encumbrance
	Budget a/c CR @ invoice price excluding NR tax /////

 

APE-Case 5:

Assumptions:

Accrue on receipt = N

Destination type = Expense

Item = expense Item

Purchase Encumbrance type and Invoice Encumbrance type are different

 

Sequence of actions:

Create, Approve, Reserve PO

Receive and Deliver the PO

Create Invoice at higher price than PO

Invoice Validation after matching

Create Accounting for invoice

Run the Period End Program

 

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax) $$	GL_BC_PACKETS	Encumbrance
2. Receive the PO	No Accounting Transactions	N/A	N/A
3. Deliver the PO	No Accounting Transactions	N/A	N/A
4. Invoice Validation at higher price	Budget a/c CR @ PO price (base + NR tax) $$	GL_BC_PACKETS	Encumbrance
	Budget a/c DR @ PO price excluding NR tax  ***
	Budget a/c DR @ difference in invoice price ***
	REC Tax and NR tax DR @ tax amount &
5. Create Accounting for invoice	Expense charge a/c DR @ invoice price excluding tax and tax DR @ tax rate separately	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price including NR tax
	REC Tax and NR tax CR @ tax amount &	AP_ENCUMBRANCE_LINES_ALL	Encumbrance
	Budget a/c CR @ invoice price excluding NR tax
6. Run the Period end program	No Accounting Transactions	N/A	N/A
7. Manual Reversal of journal entry	Not Required since there are no accrual entries	N/A	N/A

 

 

Note:

·        Corresponding offsetting accounts have same symbols.

·        PO base price refers to received quantity * unit Price in PO line

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * po line unit price)

• REC tax refers to Recoverable Tax calculated for the (received quantity * po line unit price)

 

 

 

APE-Case 6:

Assumptions:

Accrue on receipt = N

Destination type = Expense

Item = expense Item

Purchase Encumbrance type and Invoice Encumbrance type are different

 

Sequence of actions:

Create, Approve, Reserve PO

Receive and Deliver the PO

Create Invoice at higher quantity than PO

Invoice Validation after matching

Create Accounting for invoice

Run the Period End Program

 

Action	Accounting entries	Table in which records exist	Flag/nature of entry
1. PO is reserved	Budget a/c DR @ PO price (base + NR tax)  ##	GL_BC_PACKETS	Encumbrance
2. Receive the PO	No Accounting Transactions	N/A	N/A
3. Deliver the PO	No Accounting Transactions	N/A	N/A
4. Invoice Validation at higher quantity	Budget a/c CR @ PO price (base + NR tax) ##	GL_BC_PACKETS	Encumbrance
	Budget a/c DR @ PO price excluding NR tax $$
	Budget a/c DR @ additional invoice price -INV enc $$
	REC Tax and NR tax DR @ tax amount  ////
5. Create Accounting for invoice	Expense charge a/c DR @ invoice price excluding tax and tax DR @ tax rate separately	AP_AE_LINES_ALL	Actual
	AP Liability a/c CR @ invoice price including NR tax
	REC Tax and NR tax CR @ tax amount ////	AP_ENCUMBRANCE_LINES_ALL	Encumbrance
	Budget a/c CR @ PO price excluding NR tax ^^^^
	Budget a/c CR @ additional invoice price with encumbrance line type as QV  ^^^^
6. Run the Period end program	No Accounting Transactions	N/A	N/A
7. Manual Reversal of journal entry	Not Required since there are no accrual entries	N/A	N/A

Note:

·        Corresponding offsetting accounts have same symbols.

·        PO base price refers to received quantity * unit Price in PO line

·        NR tax refers to Non Recoverable Tax calculated for the (received quantity * po line unit price)

• REC tax refers to Recoverable Tax calculated for the (received quantity * po line unit price)

 

Important Notes:

 

1. Accrual entries will be generated /created ONLY when the Received quantity > Billed quantity at the time of running Receipt Accruals-Period end program.

 

2. Under the cases mentioned above under the Period-end Section, manual reversal of journal entries are not required except for Case 3 and Case 4 since there are NO accrual entries created /generated.

 

3. For all these cases the account records from receiving will also be inserted into GL_BC_PACKETS, along with the other subledger tables mentioned above. These records in GL_BC_PACKETS can be viewed by querying the  max(packet_id), as there are no REFERENCE fields populated for receiving records.

 

4. Liability a/c  which is recorded at invoice price will also include the recoverable and non-recoverable tax rates.