ORACLE MASTERMINDS: March 2019

Monday 4 March 2019

Period End Processing Oracle Inventory and Oracle Cost Management

Steps

Oracle Inventory and Oracle Cost Management provide the required features to effect the necessary period-end procedures to:

§ Reconcile the inventory and work in process costs and values.

§ Transfer inventory and manufacturing costs to the General Ledger.

§ Transfer summary or detail accounting information to the general ledger.

§ Independently open and close periods for each inventory organization.

§ Perform interim transfers to the General Ledger without closing the period.

§ Maintain the same set of periods and fiscal calendar as for the General Ledger.

Attention: For period-end adjustment purposes, it may be appropriate to hold more than one period open per inventory organisation at the same time, although at other times, having only one period open at a time ensures that transactions are correctly dated and posted to the correct accounting period.

Procedures

The following steps are taken in performing period-end processing for Oracle Inventory:

Complete All Transactions for the Period Being Closed

§ Ensure that all issues, receipts, and adjustments have been entered and verify that no hard copy records exist or are awaiting data entry, e.g. packing slips in receiving.

§ Optional Report - Cycle Count Report

This report will highlight any unconfirmed ad-hoc Stocktakes.

Check Inventory and Work in Process Transaction Interfaces

Check the Interface Managers window to ensure that there are no background or concurrent programs unprocessed.

Check all three selections, Transactions Interface, Internal Transactions, and Demand Interface, and fix any rejected transactions.

The interface managers that need to be run are as follows:

§ Material Transaction Manager

§ Material Cost Transaction

§ Move Transaction Manager

§ Resource Cost Transaction Manager

§ Check the Reservation Managers window

§ Demand Reservation Manager

Check Oracle Order Management Transaction Processes

Ensure all sales order (Pick Release) issues through Oracle Order Management have been completed and transferred successfully to Oracle Inventory.

Attention: If orders have not been released, they do not have to be completed.

Review Inventory Transactions

Before closing a period, review all of the transactions for the period that have a high dollar value and/or a high transaction quantity. Verify that the correct accounts have been charged. Correcting incorrect account charges before closing the period is easier than writing manual journal entries to resolve them later.

Balance the Perpetual Inventory

Check that the perpetual inventory value up to the end of the period being closed matches the value reported in the General Ledger. This balancing is usually effected automatically, but one of the following three sources may create a problem:

§ Other inventory journal entries

Journal entries from products other than Oracle Inventory, that update the inventory accounts.

§ Charges to improper accounts

For example, material issued from a sub-inventory to a miscellaneous account, but one of the sub-inventory accounts was used as that miscellaneous account.

§ Transactions after period end reports

This occurs when the period-end inventory valuation reports are submitted before all transactions for the period have been completed.

Use the Historical Inventory Balance Report to obtain period valuation information before the extra transactions.

The following reports can be run to help with these reviews:

§ Inventory Value Report

Use the Inventory Value Report to show quantity, valuation, and detailed item information for the sub-inventories specified.

§ Period Close Value Summary Report

Use the Period Close Value Summary to see summary balances for sub-inventories. If you run this report for a closed accounting period, the report displays the sub-inventory values at the end of that period. If you run the report for an open period, the report displays the sub-inventory value at the point in time you run the report. You can see more sub-inventory balance detail by running the Inventory Value Report, or the Elemental Inventory Value Report.

§ Material Account Distribution Detail Report

Use the Material Account Distribution Detail Report to view the accounts charged for inventory transactions. Review inventory transaction values transferred to the general ledger by GL batch.

§ Material Account Distribution Summary Report

Use the Material Account Distribution Summary report to review inventory accounting activity. If you detect unusual accounts or amounts, use the Material Account Distribution Detail report to print the transaction in detail.

Use the Material Account Distribution Summary Report to verify inventory account activity against inventory valuation increases or decreases for the accounting period. Finally, use this report to reconcile an account across several periods.

SQL Query to find details from PO till Cheque payment

SQL Query to find details from PO till Cheque payment

The following five components of P2P Cycle Query are

1. Requisition Detail

2. Purchase Order Details

3. Receiving Details

4. Invoicing Detail

5. Payment Details

SELECT
A.ORG_ID "ORG ID",
E.VENDOR_NAME "VENDOR NAME",
UPPER(E.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
F.VENDOR_SITE_CODE "VENDOR SITE",
F.ADDRESS_LINE1 "ADDRESS",
F.CITY "CITY",
F.COUNTRY "COUNTRY",
TO_CHAR(TRUNC(D.CREATION_DATE)) "PO DATE",
D.SEGMENT1 "PO NUMBER",
D.TYPE_LOOKUP_CODE "PO TYPE",
C.QUANTITY_ORDERED "QTY ORDERED",
C.QUANTITY_CANCELLED "QTY CANCALLED",
G.ITEM_DESCRIPTION "ITEM DESCRIPTION",
G.UNIT_PRICE "UNIT PRICE",
(NVL(C.QUANTITY_ORDERED,0)-NVL(C.QUANTITY_CANCELLED,0))*NVL(G.UNIT_PRICE,0) "PO Line Amount",
(SELECT
DECODE(PH.APPROVED_FLAG, 'Y', 'Approved')
FROM PO.PO_HEADERS_ALL PH
WHERE PH.PO_HEADER_ID = D.PO_HEADER_ID) "PO STATUS",
A.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
A.INVOICE_AMOUNT "INVOICE AMOUNT",
TO_CHAR(TRUNC(A.INVOICE_DATE)) "INVOICE DATE",
A.INVOICE_NUM "INVOICE NUMBER",
(SELECT
DECODE(X.MATCH_STATUS_FLAG, 'A', 'Approved')
FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL X
WHERE X.INVOICE_DISTRIBUTION_ID = B.INVOICE_DISTRIBUTION_ID)"Invoice Approved?",
A.AMOUNT_PAID,
H.AMOUNT,
I.CHECK_NUMBER "CHEQUE NUMBER",
TO_CHAR(TRUNC(I.CHECK_DATE)) "PAYMENT DATE"
FROM AP.AP_INVOICES_ALL A,
AP.AP_INVOICE_DISTRIBUTIONS_ALL B,
PO.PO_DISTRIBUTIONS_ALL C,
PO.PO_HEADERS_ALL D,
PO.PO_VENDORS E,
PO.PO_VENDOR_SITES_ALL F,
PO.PO_LINES_ALL G,
AP.AP_INVOICE_PAYMENTS_ALL H,
AP.AP_CHECKS_ALL I
WHERE A.INVOICE_ID = B.INVOICE_ID
AND B.PO_DISTRIBUTION_ID = C. PO_DISTRIBUTION_ID (+)
AND C.PO_HEADER_ID = D.PO_HEADER_ID (+)
AND E.VENDOR_ID (+) = D.VENDOR_ID
AND F.VENDOR_SITE_ID (+) = D.VENDOR_SITE_ID
AND D.PO_HEADER_ID = G.PO_HEADER_ID
AND C.PO_LINE_ID = G.PO_LINE_ID
AND A.INVOICE_ID = H.INVOICE_ID
AND H.CHECK_ID = I.CHECK_ID
AND F.VENDOR_SITE_ID = I.VENDOR_SITE_ID
AND C.PO_HEADER_ID IS NOT NULL
AND A.PAYMENT_STATUS_FLAG = 'Y'
AND D.TYPE_LOOKUP_CODE != 'BLANKET';

The important section which cover in the query output is as:

1. Information for Supplier

2.Purchase Order details

3. Receiving Items Details

4.Invoice Details

5.Payment Details

Fusion HCM - Templates for Loading Work Structures using HDL

Grade :

Location :

Job :

Position :

Organization :

Fusion HCM - Template for Loading Workers using HDL

GL Account analysis for PA Expenditure & Event query

/* 1. GL Account analysis for PA Expenditure */
SELECT 'Expenditure' revenue_type,
hca.cust_account_id customer_id,
hp.party_name customer_name,
v225332622.project_id project_id,
v225332622.project_number project_number,
okha.contract_number contract_number,
v225332622.project_name project_name,
haou.NAME project_organization,
hca.customer_class_code industry,
hp.state state, hp.city city,
hp.country country, gcc.segment1 company,
gcc.segment2 ACCOUNT,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name = 'Account'
AND fv.flex_value = gcc.segment2)
account_name,
gcc.segment3 department,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name = 'Department'
AND fv.flex_value = gcc.segment3)department_name,
gcc.segment4 region, gcc.segment5 FUNCTION,
SUM
(project_curr_revenue_amt
) project_curr_revenue_amt,
trns_currency_code bill_transaction_currency,
SUM
(trns_curr_revenue_amt
) bill_transaction_currency_rev,
contract_currency_code,
SUM
(NVL (cont_curr_revenue_amt, 0)
) cont_curr_revenue_amt_sum,
revenue_currency_code, ledger_currency_code,
SUM
(ledger_curr_revenue_amt
) ledger_curr_revenue_amt,
SUM
(v144634978.revenue_curr_amt
) revenue_curr_amt,
SUM
(DECODE
('USD',
v144634978.ledger_currency_code, NVL
(v144634978.ledger_curr_revenue_amt,
0
),
( NVL
(v144634978.ledger_curr_revenue_amt,
0
)
* (SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code =
'USD'
AND s.currency_code =
v144634978.ledger_currency_code
AND UPPER (r.period_name) =
UPPER
(v144634978.gl_period
)
AND ROWNUM = 1)
)
)
) usd_revenue_amount,
v225332622.project_currency_code
project_currency_code,
SUM
(v144634978.project_curr_revenue_amt
) project_revenue_amount,
NVL
(pei.projfunc_currency_code,
v144634978.ledger_currency_code
) projfunc_currency_code,
SUM
(NVL (v144634978.ledger_curr_revenue_amt, 0)
) projfunc_revenue_amount,
v144634978.gl_period gl_period,
gsb.NAME gl_set_of_books_name,
NULL gl_line_dff_prj_num, NULL global_region,
(SELECT class_code
FROM pjf_project_classes prc,
pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = v225332622.project_id)region_code,
(SELECT NAME
FROM hr_operating_units
WHERE organization_id =
v225332622.org_id)business_unit,
(SELECT b.document_name
FROM pjf_txn_sources_tl a,
pjf_txn_document_tl b
WHERE a.transaction_source_id =
pei.transaction_source_id
AND b.document_id = pei.document_id) SOURCE,
(SELECT ho1.NAME
FROM pjf_projects_all_b pa,
hr_all_organization_units ho1
WHERE ho1.organization_id =
carrying_out_organization_id
AND pa.project_id = v225332622.project_id)project_organization1,
v225332622.project_name source_details,
pei.expenditure_item_id AS expenditure_item_id,
v144634978.transaction_project_id AS transaction_project_id,
gl.NAME ledger_name, xda.code_combination_id,
(SELECT hou1.NAME
FROM hr_all_organization_units hou1
WHERE okha.owning_org_id =hou1.organization_id)contract_org,
(SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code = 'USD'
AND s.currency_code =
v144634978.ledger_currency_code
AND UPPER (r.period_name) =
UPPER (v144634978.gl_period)
AND ROWNUM = 1) period_avg_rate,
(SELECT NAME
FROM per_jobs_f_vl pjb
WHERE pjb.job_id = pei.person_job_id
AND SYSDATE
BETWEEN NVL
(pjb.effective_start_date,
SYSDATE - 1
)
AND NVL (pjb.effective_end_date,
SYSDATE + 1
)
AND active_status = 'A') job_name
FROM
(sELECT revenuedistributionpeo.bill_transaction_type_code,
revenuedistributionpeo.rev_distribution_id,
revenuedistributionpeo.transaction_id,
revenuedistributionpeo.transaction_project_id,
revenuedistributionpeo.revenue_curr_amt,
revenuedistributionpeo.trns_curr_revenue_amt,
gl_period_name gl_period, contract_id,
major_version, project_curr_revenue_amt,
gl_date, reversed_flag,
line_num_reversed,
revenue_currency_code,
ledger_currency_code,
ledger_curr_revenue_amt,
contract_currency_code,
cont_curr_revenue_amt,
project_currency_code,
trns_currency_code,
(CASE
WHEN bill_transaction_type_code =
'EI'
THEN transaction_id
END
) AS expenditure_item_id
FROM pjb_rev_distributions revenuedistributionpeo
WHERE 1 = 1) v144634978,
(SELECT projectbasepeo.project_id,
projectbasepeo.segment1
AS project_number,
ppt.NAME project_name,
projectbasepeo.org_id,
project_currency_code,
projectbasepeo.attribute10 region_code,
projectbasepeo.pm_product_code
pm_product_code
FROM pjf_projects_all_b projectbasepeo,
pjf_projects_all_tl ppt
WHERE projectbasepeo.project_id =
ppt.project_id) v225332622,
pjc_exp_items_all pei,
okc_k_headers_all_b okha,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units haou,
fun_names_business_units_v fnbu,
gl_ledgers gl,
gl_sets_of_books gsb,
gl_code_combinations gcc,
(SELECT xda2.event_id,
xda2.source_distribution_id_num_1,
xda2.rounding_class_code,
xda2.source_distribution_id_num_2,
xda2.ae_line_num, xda2.ae_header_id,
ref_ae_header_id, ref_ae_line_num,
SIGN
(NVL ( (-1)
* (xda2.unrounded_entered_dr),
xda2.unrounded_entered_cr
)
) xla_amt,
xal2.code_combination_id
FROM xla_distribution_links xda2,
xla_ae_lines xal2
WHERE 1 = 1
AND xda2.source_distribution_type ='Revenue - Expenditure Revenue'
AND xal2.ae_header_id = xda2.ae_header_id
AND xal2.ae_line_num = xda2.ae_line_num
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links xda1,
xla_ae_lines xla1
WHERE xda1.ref_temp_line_num =
xda2.temp_line_num
AND xda1.ref_ae_header_id =
xda2.ae_header_id
AND xda1.ae_line_num =
xla1.ae_line_num
AND xda1.ae_header_id =
xla1.ae_header_id
AND xla1.override_reason IS NOT NULL)) xda
WHERE v144634978.transaction_project_id =
v225332622.project_id
AND v225332622.project_id = pei.project_id
AND v144634978.expenditure_item_id =
pei.expenditure_item_id
AND v144634978.contract_id = okha.ID
AND v144634978.major_version = okha.major_version
AND okha.bill_to_acct_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND haou.organization_id = v225332622.org_id
AND fnbu.bu_id = v225332622.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND gl.ledger_id = gsb.set_of_books_id
AND v144634978.rev_distribution_id =
xda.source_distribution_id_num_1
AND SIGN (xda.xla_amt) =SIGN (v144634978.revenue_curr_amt)
AND xda.code_combination_id =gcc.code_combination_id
AND v144634978.bill_transaction_type_code = 'EI'
AND UPPER (v144634978.gl_period) IN (:p_gl_period)
GROUP BY hca.cust_account_id,
hp.party_name,
v225332622.project_id,
v225332622.project_number,
v225332622.project_name,
haou.NAME,
hca.customer_class_code,
hp.state,
hp.city,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
v144634978.gl_period,
gl.ledger_id,
v225332622.project_currency_code,
NVL (pei.projfunc_currency_code,
v144634978.ledger_currency_code
),
v144634978.gl_date,
gl.period_set_name,
v144634978.gl_period,
gsb.NAME,
region_code,
v225332622.org_id,
pei.expenditure_item_id,
v144634978.transaction_project_id,
xda.code_combination_id,
v225332622.pm_product_code,
pei.transaction_source_id,
pei.document_id,
hp.country,
okha.contract_number,
okha.owning_org_id,
gl.NAME,
v144634978.trns_currency_code,
v144634978.contract_currency_code,
v144634978.revenue_currency_code,
v144634978.ledger_currency_code,
pei.person_job_id

/*2. GL Account analysis for PA Events */
SELECT 'Event' revenue_type,
hca.cust_account_id customer_id,
hp.party_name customer_name,
ppa.project_id project_id,
ppa.segment1 project_number,
okh.contract_number contract_number,
ppa.NAME project_name,
hou.NAME project_organization,
hca.customer_class_code industry,
hp.state state, hp.city city,
hp.country country, gcc.segment1 company,
gcc.segment2 ACCOUNT,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name = 'Account'
AND fv.flex_value = gcc.segment2)
account_name,
gcc.segment3 department,
(SELECT fv.description
FROM fnd_flex_values_vl fv,
fnd_flex_value_sets fvs
WHERE fv.flex_value_set_id =
fvs.flex_value_set_id
AND fvs.flex_value_set_name ='Department'
AND fv.flex_value = gcc.segment3)
department_name,
gcc.segment4 region, gcc.segment5 FUNCTION,
SUM
(prd.project_curr_revenue_amt
) project_curr_revenue_amt,
pe.bill_trns_currency_code
bill_transaction_currency,
SUM
(NVL (prd.trns_curr_revenue_amt, 0)
) bill_transaction_currency_rev,
pe.contract_curr_code contract_currency_code,
SUM
(NVL (prd.cont_curr_revenue_amt, 0)
) cont_curr_revenue_amt_sum,
pe.revenue_currency_code,
pe.ledger_currency_code,
SUM
(NVL (prd.ledger_curr_revenue_amt, 0)
) ledger_curr_revenue_amt,
SUM
(NVL (prd.revenue_curr_amt, 0)
) revenue_curr_amt,
SUM
(DECODE
('USD',
pe.ledger_currency_code, prd.ledger_curr_revenue_amt,
prd.ledger_curr_revenue_amt
* NVL
((SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code =
'USD'
AND s.currency_code =
pe.ledger_currency_code
AND UPPER
(r.period_name) =
UPPER
(prd.gl_period_name
)),
1
)
)
) usd_revenue_amount,
pe.project_currency_code project_currency_code,
SUM(prd.project_curr_revenue_amt) project_revenue_amount,
ppa.projfunc_currency_code
projfunc_currency_code,
SUM(ledger_curr_revenue_amt) projfunc_revenue_amount,
prd.gl_period_name gl_period,
gsb.NAME gl_set_of_books_name,
NULL gl_line_dff_prj_num, NULL global_region,
(SELECT class_code
FROM pjf_project_classes prc,
pjf_class_codes_tl pct
WHERE prc.class_code_id = pct.class_code_id
AND prc.project_id = ppa.project_id)
region_code,
(SELECT NAME
FROM hr_operating_units
WHERE organization_id =
ppa.org_id)
business_unit,
'PA' SOURCE,
(SELECT ho1.NAME
FROM pjf_projects_all_b pa,
hr_all_organization_units ho1
WHERE ho1.organization_id =
carrying_out_organization_id
AND pa.project_id = ppa.project_id)
project_organization1,
ppa.segment1 source_details,
pe.event_id expenditure_item_id,
ppa.project_id transaction_project_id,
gl.NAME ledger_name,
xda.code_combination_id,
(SELECT hou1.NAME
FROM hr_all_organization_units hou1
WHERE okh.owning_org_id = hou1.organization_id)
contract_org,
(SELECT DISTINCT r.avg_rate
FROM gl_translation_rates r,
gl_sets_of_books s
WHERE s.set_of_books_id =
r.set_of_books_id
AND r.to_currency_code = 'USD'
AND s.currency_code =
pe.ledger_currency_code
AND UPPER (r.period_name) =
UPPER (prd.gl_period_name))
period_avg_rate
FROM pjf_projects_all_vl ppa,
pjf_project_types_vl ppt,
pjb_rev_distributions prd,
pjb_billing_events pe,
okc_k_headers_all_b okh,
hz_cust_accounts hca,
hz_parties hp,
hr_all_organization_units hou,
fun_names_business_units_v fnbu,
(SELECT xda2.event_id,
xda2.source_distribution_id_num_1,
xda2.rounding_class_code,
xda2.source_distribution_id_num_2,
xda2.ae_line_num, xda2.ae_header_id,
ref_ae_header_id, ref_ae_line_num,
SIGN
(NVL ( (-1)
* (xda2.unrounded_entered_dr),
xda2.unrounded_entered_cr
)
) xla_amt,
xal2.code_combination_id
FROM xla_distribution_links xda2,
xla_ae_lines xal2
WHERE 1 = 1
AND xda2.source_distribution_type =
'Revenue - Event Revenue'
AND xal2.ae_header_id = xda2.ae_header_id
AND xal2.ae_line_num = xda2.ae_line_num
AND NOT EXISTS (
SELECT 1
FROM xla_distribution_links xda1,
xla_ae_lines xla1
WHERE xda1.ref_temp_line_num =
xda2.temp_line_num
AND xda1.ref_ae_header_id =
xda2.ae_header_id
AND xda1.ae_line_num =
xla1.ae_line_num
AND xda1.ae_header_id =
xla1.ae_header_id
AND xla1.override_reason IS NOT NULL)) xda,
gl_code_combinations gcc,
gl_ledgers gl,
gl_sets_of_books gsb,
(SELECT pcl.project_id, pcl.contract_id
FROM pjb_cntrct_proj_links pcl
WHERE pcl.version_type = 'C'
GROUP BY pcl.project_id, pcl.contract_id) cont_link
WHERE 1 = 1
AND ppa.project_type_id = ppt.project_type_id
AND bill_transaction_type_code = 'EVT'
AND cont_link.project_id = ppa.project_id
AND prd.transaction_id = pe.event_id
AND pe.contract_id = cont_link.contract_id
AND okh.ID = pe.contract_id
AND okh.version_type = 'C'
AND okh.bill_to_acct_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND hou.organization_id =ppa.org_id
AND fnbu.bu_id = ppa.org_id
AND fnbu.primary_ledger_id = gl.ledger_id
AND gl.ledger_id = gsb.set_of_books_id
AND xda.source_distribution_id_num_1 =prd.rev_distribution_id
AND SIGN (xda.xla_amt) = SIGN (prd.revenue_curr_amt)
AND gcc.code_combination_id =xda.code_combination_id
AND UPPER (prd.gl_period_name) IN (:p_gl_period)
GROUP BY hca.cust_account_id,
hp.party_name,
ppa.project_id,
ppa.segment1,
okh.contract_number,
okh.owning_org_id,
ppa.NAME,
hou.NAME,
hca.customer_class_code,
hp.state,
hp.city,
hp.country,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
pe.bill_trns_currency_code,
pe.contract_curr_code,
pe.revenue_currency_code,
pe.ledger_currency_code,
pe.project_currency_code,
ppa.projfunc_currency_code,
projfunc_currency_code,
prd.gl_date,
gl.period_set_name,
prd.gl_period_name,
gsb.NAME,
ppa.org_id,
pe.event_id,
ppa.project_id,
gl.NAME,
xda.code_combination_id