Friday, 23 November 2012

Use of WORKFLOW BACKGROUND PROCESS IN ORACLE APPS



Workflow Background Process is a concurrent program which is run for processing deferred activities, timed out activities, and stuck processes using the parameters specified. The background engine executes all activities that satisfy the given arguments at the time that the background engine is invoked. This procedure does not remain running long term, so you must restart this procedure periodically. Any activities that are newly deferred or timed out or processes that become stuck after the current background engine starts are processed by the next background engine that is invoked.

Workflow Background Process is run with the help of Workflow Background Engine which is PL/SQL Procedure which runs this concurrent program with specified parameters.


Workflow Background Process Parameters:

Item Type – Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any activity regardless of its item type.
Minimum Threshold – Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.
Maximum Threshold – Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second. By using Minimum Threshold and Maximum Threshold you can create multiple background engines to handle very specific types of activities. The default values for these arguments are null so that the background engine runs activities regardless of cost.
Process Deferred – Specify whether this background engine checks for deferred activities. Setting this parameter to Yes allows the engine to check for deferred activities.
Process Timeout – Specify whether this background engine checks for activities that have timed out. Setting this parameter to Yes allows the engine to check for timed out activities.
Process Struck -
Specify whether this background engine checks for struck process.




Monday, 5 November 2012


Financials and India tax accounting entries with general ledger identification querys

(Notes taken from metalink Doc Id--- How to Reconcile Financials for India Tax Accounting Entries with General Ledger [ID 790203.1]
      1.Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE'
     2.Query to identify total tax amount generated at the time of Receiving
      3.Query to identify total tax amount generated at the time of delivery
      4.Query to identify total modvat amount generated for CENVAT entries
      5.Query to identify multiple entries populated into mtl_transaction_accounts (Standard Costing Organization) when material is delivered
      6.Return to Receiving Entries: Query to identify multiple entries generated for Return to Receiving entries
      7.Query to identify total tax amount generated at the time of Return to Receipt
      8.Return to Vendor Entries: Query to identify multiple entries generated for Return to vendor entries
      9.Query to identify total tax amount generated at the time of Return to Vendor
      10.Query to find the Modvat entries which hit PLA for RTV when the PLA pref =1
      11.Query to identify multiple entries generated at time of Receipt for AP Accrual account
      12.Query to identify total tax amount genearted for AP Accrual account at the time of Receipt
      13.Query to identify total tax amount generated for AP Accrual at the time of Return to Vendor
      14.Sum of Credit not equal to Sum of Debit for Return To Vendor transactions
      15.Query to give total tax amount for AP Accrual where the tax entries are not generated
      16.Query to identify cases of discrepancy in invoice distribution and the corresponding tax amount in Receipt / PO
      17.Query to give total sum of the taxes based on transaction type from JAI_RCV_JOURNAL_ENTRIES (IL Subledger)
      18.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise
      19.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise
      20.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise
      21.Query to Find the Sum of Tax Amounts from JAI_RCV_JOURNAL_ENTRIES WHERE TRANSACTION_TYPE='RETURN TO VENDOR'
      22.Query to identify Receipts for which accounting entries are not generated
      23.Query to identify receipts where tax entries have been updated in IL subledger but missing in General Ledger
      24.Query to identify Receipt where accounting entries not generated for Deliver transactions
      25.Query to find Receipts for which RTR entries are not generated
      26.Query to Find (RTV) Returns for which accounting is not generated
      27.Query to identify Receipts not yet claimed
      Step 3


--------------------------------------------------------------------------------







This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.





Applies to: 
Oracle Financials for India - Version: 12.0.0 to 12.0.6 - Release: 12 to 12
Information in this document applies to any platform.
 Oracle Application - India Localization - Version: 12.0 to 12.0.6

Purpose
  This Note will be helpful to Reconcile Financials for India Tax Accounting Entries with General Ledger


Questions and Answers
 Reconciliation of the General Ledger with the subledger data provided by India Localization shall be done as per the following process:


Step 1

Import all the records pending in interface table and ensure that there are no pending transactions in the interface. Compare the
GL account balances with the India Localization sub-ledger balances to verify whether the balances are tallying. The following are the India
Localization reports that could be used for this purpose:

1. India Receiving account distrubution report.
2. India Receiving accounts report.
3. Accrual re-build reconcillation report.
4. India cenvat monthly return inputs and capital goods.
5. India cenvat monthly abstract report.


Step 2

If any differences are observed in these balances, following queries should be run to find out the exceptions.
The list of queries are given below:


Tax accounting entries generated at the time of Receipt:

1.Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE' 



select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and a.transaction_type ='RECEIVE'
and a.code_combination_id=&CCID and gcc.code_combination_id = a.code_combination_id
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr





2.Query to identify total tax amount generated at the time of Receiving


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RECEIVE'
AND TRUNC(E.CREATION_DATE)
BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



3.Query to identify total tax amount generated at the time of delivery

SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='DELIVER'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



4.Query to identify total modvat amount generated for CENVAT entries


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.ACCT_NATURE ='MODVAT'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



5.Query to identify multiple entries populated into mtl_transaction_accounts (Standard Costing Organization) when material is delivered


Select
b.transaction_id,b.reference_account,b.base_transaction_value,count(*),
nvl(sum(base_transaction_value),0) - nvl(avg(base_transaction_value),0) "Excess Amount Passed"
from mtl_transaction_accounts b, org_acct_periods c, mtl_material_transactions a
where 0 < (select count(1)
from mtl_transaction_accounts d
where b.transaction_id = d.transaction_id
and b.reference_account = d.reference_account
and b.organization_id = d.organization_id
and b.request_id is null
)
and a.transaction_id = b.transaction_id
and a.organization_id = b.organization_id
and b.reference_account = &code_id
and b.request_id is null
and a.acct_period_id = c.acct_period_id
and a.organization_id = c.organization_id
and c.period_name = '&PERIOD_NAME'
and c.organization_id = &org_id
group by b.transaction_id,b.reference_account,b.base_transaction_value
having nvl(sum(base_transaction_value),0) - nvl(avg(base_transaction_value),0) <> 0
order by b.transaction_id




6.Return to Receiving Entries: Query to identify multiple entries generated for Return to Receiving entries


select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.TRANSACTION_TYPE ='RETURN TO RECEIVING'
and trunc(a.creation_date) between '&from_date' and '&to_date'
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr




7.Query to identify total tax amount generated at the time of Return to Receipt


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO RECEIVING'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'




8.Return to Vendor Entries: Query to identify multiple entries generated for Return to vendor entries


select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.transaction_type ='RETURN TO VENDOR'
and a.code_combination_id=&CCID
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr



9.Query to identify total tax amount generated at the time of Return to Vendor


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



10.Query to find the Modvat entries which hit PLA for RTV when the PLA pref =1


select receive.organization_id "Organization",
recLine.inventory_item_id "Item Id",
itemMaster.item_class "Item Class",
receive.transaction_id "Receive Transaction id",
rtv.transaction_id "Return Transaction id",
receive.quantity "Receive Qty",
rtv.quantity "Returned Quantity",
pla.dr_basic_ed "PLA amount"
from
rcv_transactions receive,
rcv_transactions rtv,
JAI_RCV_LINES recLine,
JAI_INV_ITM_SETUPS itemMaster,
JAI_CMN_RG_PLA_TRXS pla
where
receive.transaction_type = 'RECEIVE'
and rtv.transaction_type = 'RETURN TO VENDOR'
and receive.shipment_line_id = recLine.shipment_line_id
and rtv.shipment_line_id = recLine.shipment_line_id
and receive.shipment_line_id = rtv.shipment_line_id
and receive.organization_id = rtv.organization_id
and rtv.organization_id = &organization_id
and recLine.inventory_item_id = itemMaster.inventory_item_id
and recLine.organization_id = itemMaster.organization_id
and rtv.transaction_date between '&date_start' and '&date_end'
and pla.ref_document_id = rtv.transaction_id
and pla.transaction_id = 19
order by 1, 2




11.Query to identify multiple entries generated at time of Receipt for AP Accrual account


select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.TRANSACTION_TYPE ='RECEIVE'
and a.code_combination_id = &CCID and trunc(a.creation_date) between '&p_date_from' and '&p_date_to'
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr





12.Query to identify total tax amount genearted for AP Accrual account at the time of Receipt


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RECEIVE'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'
AND E.CODE_COMBINATION_ID =&CCID




13.Query to identify total tax amount generated for AP Accrual at the time of Return to Vendor


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'
AND E.CODE_COMBINATION_ID =&CCID



14.Sum of Credit not equal to Sum of Debit for Return To Vendor transactions


SELECT NVL(SUM(ENTERED_CR),0), NVL(SUM(ENTERED_DR),0),RECEIPT_NUM
FROM JAI_RCV_JOURNAL_ENTRIES
WHERE TRANSACTION_TYPE='RETURN TO VENDOR'
GROUP BY RECEIPT_NUM
HAVING NVL(SUM(ENTERED_CR),0) <> NVL(SUM(ENTERED_CR),0)
ORDER BY RECEIPT_NUM



15.Query to give total tax amount for AP Accrual where the tax entries are not generated


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and acct_nature = 'Receiving'
)
and rct.transaction_id=rcv.rcv_transaction_id
and NVL(rcv.entered_cr,0) > 0
and rcv.code_combination_id=&CCID
group by rct.organization_id, jrl.receipt_num, jrtl.currency, rcv.code_combination_id
order by rct.organization_id,jrl.receipt_num,jrtl.currency, rcv.code_combination_id




16.Query to identify cases of discrepancy in invoice distribution and the corresponding tax amount in Receipt / PO


select f.segment1 "PO Number", g.receipt_num "Receipt Number",
b.transaction_id "Receipt tran id", a.po_distribution_id "PO Dist.Id",
e.invoice_num "Invoice Number ", a.invoice_id "Invoice_id",
a.distribution_line_number "Distribution Line No", a.posted_flag "Posted",
c.tax_amount "Receipt Tax Amount", a.amount "Invoice Distribution Amt.", (c.tax_amount - a.amount ) "Differential Amt"
from ap_invoice_distributions_all a,
rcv_transactions b,
JAI_RCV_LINE_TAXES c,
JAI_AP_MATCH_INV_TAXES d,
ap_invoices_all e,
po_headers_all f,
rcv_shipment_headers g
where a.rcv_transaction_id = b.transaction_id
and b.shipment_line_id = c.shipment_line_id
and a.invoice_id = d.invoice_id
and a.distribution_line_number = d.distribution_line_number
and c.tax_id = d.tax_id
and a.invoice_id = e.invoice_id
and b.po_header_id = f.po_header_id
and b.shipment_header_id = g.shipment_header_id
and b.transaction_type= 'RECEIVE'
and e.source = 'ERS'
and abs(c.tax_amount - a.amount ) >= 1
and b.transaction_date between '&from_date' and '&to_date'




17.Query to give total sum of the taxes based on transaction type from JAI_RCV_JOURNAL_ENTRIES (IL Subledger)


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RECEIVE'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




18.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='DELIVER'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




19.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RETURN TO RECEIVING'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




20.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




21.Query to Find the Sum of Tax Amounts from JAI_RCV_JOURNAL_ENTRIES WHERE TRANSACTION_TYPE='RETURN TO VENDOR'


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) > '&parameter_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'




22.Query to identify Receipts for which accounting entries are not generated


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RECEIVE'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
and NVL(rcv.entered_cr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



23.Query to identify receipts where tax entries have been updated in IL subledger but missing in General Ledger


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from gl_je_lines
where reference_5 = to_char(jrtl.transaction_id)
and description like 'India%'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
--and NVL(rcv.entered_Dr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



24.Query to identify Receipt where accounting entries not generated for Deliver transactions


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'DELIVER'
and nvl(jrtl.modvat_flag, 'N') = 'N'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'DELIVER'
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



25.Query to find Receipts for which RTR entries are not generated


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RETURN TO RECEIVING'
and nvl(jrtl.modvat_flag, 'N') = 'N'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RETURN TO RECEIVING'
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



26.Query to Find (RTV) Returns for which accounting is not generated


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RETURN TO VENDOR'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RETURN TO VENDOR'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
and NVL(rcv.entered_cr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



27.Query to identify Receipts not yet claimed


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
rcv_transactions rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and nvl(jrtl.modvat_flag, 'N') = 'Y'
and exists (select '1'
from JAI_RCV_CENVAT_CLAIMS
where shipment_line_id = jrtl.shipment_line_id
and cenvat_claimed_ptg <> 100
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



Sunday, 4 November 2012

Quick Review on MRP setups....




Introduction

Setting up the Oracle Master  Scheduling/MRP and Oracle Supply Chain Planning modules is easy.  Determining how your business will leverage the features,
is the hard part. This document can be used as a guide during your implementation.


Before you set up Oracle Supply Chain Planning, you must complete the setup for the following: products:

Oracle Inventory
Oracle Purchasing
Oracle Bills of Material
Oracle Work in Process
Oracle Project Manufacturing


Planning Setup Checklist

Required steps for System Administration, General Ledger, Inventory, Purchasing, Bills of Material, and WIP modules need to be completed before
Planning is setup.

The following steps need to be completed in the order shown:

Step 1:

Define your Master Scheduling/MRP Setup Parameters (Required)
NAV/Supply Chain Planning/Setup/Parameters or
NAV/Material Planning/Setup/Parameters

You can override the execution defaults, when defining the MPS, MRP or DRP for your organization(s).  
To have all past due demand (sales orders, forecast, etc.) reflected in your plans, select Null for Include MDS Days.
Entering a value will limit the past due information visible in the Plan.

Step 2: 

Define your Deliver-to Locations (Optional)
NAV/Inventory/Setup/Organizations/Locations

Step 3: 

Define your Employees (Optional)
NAV/Work In Progress/Setup/Employees
Note: Enter Employees through Human Resources, if installed.  
A required step when implementing planning recommendations from the Planner Workbench (Step 21).

Step 4: 

Set Profile Options (Required with Defaults)
NAV/Profiles/System/Find MRP

Note: Use the Sysadmin Responsibility. The value for Snapshot Workers can be increased depending on the number of items being planned.  Use the
following formula to determine how many Standard Managers are required to support the process.  The value for the Planning Manager Max Workers should
not be more than the number of Standard Managers.

(Snapshot workers value *2) + 4 = # of Standard Managers
(2*2) + 4 = 8 Standard Managers

Recommended initial values:

MRP:Environment variable to set path for MRP files Null/Blank
MRP:Planning Manager Max Workers                        6
MRP:Retain Dates within Calendar Boundary               Yes
MRP:Snapshot Workers                                    2
MRP:Use Direct Load Option                              No


Step 5: 

Define your Forecast Sets (Optional)
NAV/Supply Chain Planning/Forecast/Sets
NAV/Material Planning/Forecast/Sets

Step 6:

Define your Master Demand Schedules (MDS)(Optional)
NAV/Supply Chain Planning/MDS/Names
NAV/Material Planning/MDS/Names

Step 7:

Define your Master Production Schedules (MPS) (Optional)
NAV/Supply Chain Planning/MPS/Names
NAV/Material Planning/MPS/Names

Step 8: 

Define your Material Requirements Plans (MRP);
Define your Distribution Requirements Plans (DRP) (Optional)
NAV/Supply Chain Planning/MRP/Names
NAV/Supply Chain Planning/DRP/Names
NAV/Material Planning/MRP/Names

Step 10:

Define your Sourcing Rules or Bills of Distribution (BOD)
NAV/Supply Chain Planning/Sourcing
Note: When you select All Orgs for your Sourcing Rule, it can be used by all organizations when defining an Assignment Set.  A Local
Sourcing Rule would only have the Org selected, and can only be used by the defining organization.   The Planning Active box will only be checked
if the allocation % = 100.  The planning process will not use the rule, unless the Planning Active box is checked

Step 11: 

Create your Assignment Sets
NAV/Supply Chain Planning /Sourcing/AssignSourcing Rules/BOD
Note:  A Local Sourcing Rule assigned at the Item-Organization level, overrides all others for that item.

Step 12: 

Define your Inter-organization Shipping Network
NAV/Inventory/Setup/Organizations/Shipping Network

Step 13:

Define Shipping Methods and associated intransit lead times
NAV/Inventory/Setup/Organization/Shipping Methods
NAV/Inventory/Setup/Organization/Shipping Network
  Special Menu:  Shipping Methods  Intransit Times

Step 14: 

Run the Information Audit (Optional-highly recommended)
NAV/Supply Chain Planning/Reports/Audit Information Report
NAV/Material Planning/Reports/Audit Information Report

Step 15: 

Define your Planning Parameters (Required)
NOTE: this is a duplicate of Step 1.

Step 16: 

Start the Planning Manager (Required)
NAV/Supply Chain Planning/Setup/Planning Manager or
NAV/Material Planning/Setup/Planning Manager
(See screen shot below; the active box should be checked and you should see current messages)

Step 17:

Define your Planners (Optional)
NAV/Inventory/Setup/Planners

Step 18: 

Define your Planning Exception Sets (Optional)
NAV/Supply Chain Planning/Setup/Exception Sets
Or NAV/ Material Planning/Setup/Exception Sets

Step 19:

Define your Demand Classes (Optional)
NAV/Supply Chain Planning/Setup/Demand Classes
OR NAV/Material Planning/Setup/Demand Classes
Note: Selecting a Demand Class when defining your MDS or MPS,restricts data seen in the schedules.  Only Sales Orders with the
demand class entered at the line level will be included in your MDS.
Only Discrete Jobs with the demand class added,(NAV/WIP/Discrete/Discrete Jobs/More) will be included in your MPS.

Step 20: 

Create Source Lists (Optional)
NAV/Supply Chain Planning/Forecast or MDS or MPS /Source List
NAV/Material Planning/Forecast or MDS or MPS/Source List


Step 21: 

Set Up Planner Workbench (Required)
For instructions, see: Setting Up Planner Workbench:
Oracle Master Scheduling/MRP and Supply Chain Planning User's Guide Release 11: page 1-28.

Step 22: 

Set Up for Supplier Planned Inventories (Optional) For instructions on setting up Supplier Planned Inventories, see the prerequisites in
Oracle Master Scheduling/MRP and Oracle Supply Chain Planning User's Guide Release 11, page 5-8.
What's not in 11i and available in R12 short notes 

1- Sub Ledger accounting
2- Enhanced Reporting Currency
3- Deferec COGS and revenue matching
4- MOAC Multi Org Access
5- Unified Inventory ( OPM Inventory and Oracle inventory unified in single version so OPM also can use VMI
6- Fixed Component usage Material enhancement in BOM setup level irrespective of lot size 
7- Component Yield calculation methods
8- Professional buyers workcenter
9- Item Orderablity ( Which customer can order which item)
10- Cascading attributes ( that is if you change in header level that’s automatically applies in line leve)
11- Customer Credit check hold source support across operating unit

MIN-MAX Planning Short Notes 


1- Planning based on Minimum and maximum inventory level
2- Inventory Level for an item ( On Hand quantity + Supply ( either open po, Open Job Order) drops below the minimum Oracle Inventory suggests replenishment order
3- Inventory may suggest Purchase Requisition, Internal requisition, Move Order, Discrete job
4- Min- Max planning can be run in either Organization or Sub inventory level
5- When we run Organization level Min Max planning , system will consider demand as
a. Unreserved sales Order
b. Reserved Sales Order,
c. Account issue move orders
d. WIP component requirements
6- And org level planning will suggest PR , IR & unreleased WIP job.
7- For Sub inventory Level Min-max planning , Demand will be consider
a. Unreserved sales Order
b. Reserved sales order
c. Account issue move order
d. Sub inventory Move Order

8- And Sub inventory level Min-max planning will suggest PR,IR & Move Order

Note :
Subinventory level planning cannot generate jobs and does not
consider WIP jobs as supply or WIP components as demand.

9- For Organization Level planning
10- We have to set the Min & Max Inventory level in item attribute level, and Min & Max order quantity as optional as well as Lot multiplier for additional constraint on suggested Orders.
11- New Order suggection will be when Total available < Minimum Quantity
12- Order Quantity = Maximum Quantity-Total available

Nettable Quantity on Hand + On Order - Open Demand = Total Available, in which
• Nettable Quantity on Hand is the sum of the quantities on hand for the item
across all the nettable subinventories within your organization. Non-nettable
quantities may optionally be included.

• On Order is the sum of open purchase orders, requisitions, internal orders, and
work in process jobs scheduled for receipt on or before the supply cutoff date.

• Open Demand is the sum of unreserved sales orders, inventory reservations,
including reserved sales orders, account issue move orders, and WIP
component demand scheduled for issue on or before the demand cutoff date.

13- Oracle Inventory creates purchase requisitions for Buy items
when the item attribute Replenishment Source Type is set to Supplier.

14- OracleInventory creates internal requisitions for internal sales orders for Buy items when
the item attribute Replenishment Source Type is set to Inventory.

15- For internal  requisitions, Oracle Inventory uses the item attribute Source Organization to
determine the organization from which the internal requisition sources the item.

Simple Examble on Planning Calculation

The following example shows you how Oracle Inventory performs min-max planning.
Assume an item has the following quantity values and item attribute settings:

• Nettable quantity on hand = 25
• Open supply quantity = 50
• Open reserved sales order quantity = 90
• Inventory planning method = Min-max planning
• Min-max minimum quantity = 100
Min-max maximum quantity = 500
If you run the Min-Max Planning report, and specify No to Net Reserved Orders, Oracle
Inventory performs the following calculations:
• Total Available: 25 + 50 = 75
• We assume that all the supply is within the supply cutoff date, for a supply
total of 50.
• Total available quantity is 75.
• Below min check: 75 < 100
• The total available quantity is less than the min-max minimum quantity, so
Oracle Inventory plans a new order.
• Max quantity less total available: 500 - 75 = 425
• To bring the quantity available back to the min-max maximum, Oracle
Inventory will plan an order for 425.
If you run the Min-Max Planning report, and specify Yes to Net Reserved Orders,
Oracle Inventory performs the following calculations:
• Total Available: (25 + 50) - 90 = (-15)
• We assume that all the supply is within the supply cutoff date, for a supply
total of 50.
• We assume that all the demand is within the demand cutoff date, so open
reserved orders total to 90.
• Total available quantity is (-15).
Below min check: (-15) < 100
• The total available quantity is less than the min-max minimum quantity, so
Oracle Inventory plans a new order.
• Max quantity less total available: 500 - (-15) = 515
• To bring the quantity available back to the min-max maximum, Oracle
Inventory will plan an order for 515.
Note: To include inventory reservations from an external order
management system in the min-max calculation, create the
sales order in the MTL_SALES_ORDER table using the Sales
Order API, and then load the reservation using the
Create/Update/Delete reservation interface or the Reservation
APIs.

• INV:Minmax Reorder Approval profile option governs the approval status of
internal requisitions created by the Min-Max Planning Report. (Move Orders are
automatically approved.)







MRP FAQ for a quick reference

1. Why don't I see data up to the horizon date of my plan?

Sign on using the user that is having the problem.
Navigate: Material Planning -> MRP -> Workbench
Click 'Special' in the tool bar and select Preferences,
In the ALT region pick Supply/Demand.
Change the cutoff date to the plan horizon date and save.
You should see the data.
Refer notes Note 92130.1 , Note 109598.1

2. How do I copy an MPS, MRP, or DRP Plan?

After an MPS, MRP, or DRP plan has been generated, a copy can be made of the original plan. The copy can be used for training, what if scenarios or plan backup.
The plan copy can then be manipulated in any
way. The options and all of the data from original plan will be available in the copy.

Before a plan can be copied, a destination plan name of the same type as the source plan must defined. The user responsibility and the organization must be the same.

Example: Navigate to Material Planning -> MRP names.
Define a New Name and Description and Save. There is no need to Setup the Plan Options.

Copying the plan can be done Two ways.
Navigate: Material Planning -> MRP -> Copy Plan.Enter the destination and source names and submit.

OR

Navigate to Material Planning: MRP -> Workbench-> Tools Copy Plan

Refer Note 109618.1

3. Why don't I see dependent demand for make items?

For a single assembly or few assemblies problem Navigate: Inventory -> Items -> Organization Items -> Query an item in question and go to the Alternate Region 'General Planning'
and check the Make/Buy item attribute and the 'Source' zone. If the item is set to Buy or the source type is not NULL then the item will be considered a purchased item and will not explode.

If all or most of the make items are not exploding then a sourcing rule is set at a higher level.
Navigate: Inventory -> Setup -> Organization -> Parameters, go to the
Alternate Region 'ATP, Pick, Item-Sourcing' and check the Item-Sourcing Detail zone.

*If the Type is NOT set to NULL then a Sourcing Rule will be in effect for ALL items in that organization.

Another general method to check for a Sourcing Rule that is affecting items is to
Navigate: Supply Chain Planning- > Sourcing -> View Sourcing Hierarchy
Query an item in question, Query any Assignment Set and look for a Rule that is checked 'Active' and review the settings for that Rule.

Refer Note 100720.1

4. Why do I see Non-Nettable WIP components included in demand?
The problem arises when the method of running planning is MDS > MRP.
In other words, if you are not running MPS relief and the MRP profile MRP: Consume MPS is set to NO

5. Why don't I see my pegging info in the Object Navagator?
11.03 only - The problem is that the tables used by the Object Navagator have not been seeded properly during the Applications software installation.

Use the following Unix commands to seed the tables:

java oracle.apps.ak.akload APPS APPS UPLOAD aksflow.jlt UPDATE
java oracle.apps.ak.akload APPS APPS UPLOAD aks601.jlt UPDATE
java oracle.apps.ak.akload APPS APPS UPLOAD akssec.jlt UPDATE
java oracle.apps.ak.akload APPS APPS UPLOAD mrpregon.jlt UPDATE

These commands are executed from the $JAVA_TOP/oracle/apps/ak directory

After seeding the tables you should bounce the server and re-run the planning process.

NOTE: There are other products that use the Object Navagator and they have coresponding .jlt files that may need to be run also.

Refer Note 102565.1

6. Why don't I see Forecasted Demand in Planners Workbench?
Check the Item Attribute 'Demand Time Fence Days'. The Planner will NOT plan for an item inside of the Demand Time Fence Days.

Example:
An item has a 'Demand Time Fence Days' of 60 days, the item is Forecasted for due date of 01-APR and the plan anchor date is 01-MAR.
The Planner will not plan the item because the Demand Time Fence extends from 01-MAR to 01-MAY (60 days) and the due date of 01-APR is in the that window.
If the due date is moved out past 01-MAY then demand will show up.

Either reduce the 'Demand Time Fence Days' or move the due date out past the Time Fence or disable the plan option 'Demand Time Fence Control'.

There is also a remote possibility that there was a problem with the Planner and that bouncing the Planning Manager may clear the problem and demand will show up.

Refer Note 109655.1

7. Can I release MPS items in the MRP Planners Workbench?
Yes, However it is NOT recommended.

In the MRP Planners Workbench if an item is selected to release and a caution window appears with the following message:
"This plan order was generated in source plan or schedule. Do you still want to select it for release?"

Then this means the item is MPS planned and is already on the schedule used for this MRP.

It is recommended to go to the MPS Workbench to release.

Refer Note 1051093.6

8. Why does the Snapshot View differ from Current View for Resources? 
There have been no changes to the plan to indicate why a difference would exist.
Looking at Resources, from the Planner Workbench, and then viewing the Horizontal Plan, the Snapshot radio button does not show the same data as the Current radio button.

It is incorrect to assume that the current view and snapshot view should be the same after the plan is just run. Actually they will be very different unless the plan does not suggest
any rescheduling or cancellation.

The Reasons are:

1. The current view shows the resource requirements of the existing jobs as they are now.
2. The snapshot view shows the resource requirements based on the plan's suggestions for reschedules, cancellations, etc.
*If the users implement all the plan's recommendations for cancellation and rescheduling, then the current view and snapshot view will be the same.

Refer Note 1071508.6

9. How do I get the location field to default in the Planners Workbench?
When implementing planned orders to become purchase requisitions from the planners workbench, how do you get the system to default the location parameter automatically when you have more than
one ship-to location defined for an organization?

Define a default delivery location in the organization definition.

This is Done through Navigation:

Inventory -> Setup -> Organizations -> Organizations.

Refer Note 1067620.6

10. Memory Based Planner seems to hang
There are many possible setup issues that could be causing this problem but the first one to check is always the concurrent manager setup.

Concurrent Manager Setup
All MBP concurrent programs will run, by default, in the Standard concurrent manager. It is not required to set up a specialized concurrent manager to run these programs
unless requirements on the system dictate this need.

If you choose to run the MBP as a single-threaded process, that is,the profile MRP:Snapshot Workers is set to zero, then the MBP will require that the concurrent manager allow
at least four concurrent requests to run simultaneously:

1. Memory-Based Snapshot
2. Snapshot Monitor
3. Memory-Based Planner
4. (All other snapshot workers, loader workers, etc.)
Once the item information is written and loaded, the Snapshot Monitor will launch the Memory-Based Planner. In addition to those two processes, the Memory-Based Snapshot will be
in a running status, for a total of three running processes.

These requests cannot complete until all of the Loader Workers and Memory-Based Snapshot Workers complete their processing. If there are no more available concurrent manager slots
than these three which are already filled, the MBP will "hang" in this state, waiting until more concurrent manager slots become available.

To correct this situation, terminate the running processes and raise the number of target processes for that concurrent manager to something greater than three.

As a rule of thumb, use the following formula to determine a minimum value for the target processes of the concurrent manager:

Target processes = 4 + ( 2 * profile value )

To determine the total number of concurrent processes that will run during a given execution of the MBP, use the following guide:

Program # of Processes
Memory-Based Snapshot 1
Snapshot Monitor 1
Memory-Based Snapshot Worker profile
Snapshot Delete Worker profile
Loader Worker at least 1
Memory-Based Planner 1

One Loader Worker will be spawned for each type of snapshot data present. Therefore, there may be as many as 10 or more Loader Workers during a given planning run.
If the Snapshot Workers profile is set to four, this means there may be 20 or more concurrent processes which are launched during the planning process.

Unless a special concurrent manager has been defined to run the memory-based planner concurrent programs, this scenario is likely to cause the concurrent manager
slots to become filled with these requests.

To avoid such a situation, take one of the following three actions: lower the value of MRP:Snapshot Workers, raise the number of target processes for the concurrent manager, or
define a separate concurrent manager and define specialization rules such that only MBP-related programs can run in that manager.

11. MRP is missing PO's
Make sure that the Customer can see the jobs in the Supply/Demand screen in Inventory.

Inventory\On-Hand Availability\Item Supply/Demand

If they can see the Jobs here then a row has been inserted into MTL_SUPPLY

Perform the following steps

1. In MTL_SUPPLY make sure the MTL_SUPPLY_T trigger is enabled. To check this by performing the following sql statement


select trigger_name,description,status
from all_triggers
where trigger_name like 'MTL_SUPPLY_T'

TRIGGER_NAME DESCRIPTION STATUS

MTL_SUPPLY_T "APPS_APPDEMO".MTL_SUPPLY_T BEFORE INSERT OR
UPDATE OR DELETE on INVDEMO.MTL_SUP ENABLED

If this trigger is not enabled then you need to enable it. This will not change how the fact that MRP cannot see it though. In order for MRP to see them you
need for the trigger to fire. To accomplish this you need to change something on the Purchase Order and commit it. The easiest it to change the qty on the Purchase Order
and then commit. Then change the qty back on the Purchase Order and commit again.

Once a row has been inserted into MRP_RELIEF_INTERFACE check the PROCESS_STATUS

PROCESS_STATUS Description Action

2 Waiting to be Processed Make sure Planning MGR is running
3 In Process None
4 Error Check ERROR_MESSAGE in this table
5 Successful completion None
12. MRP is missing Sales orders
Make sure that the Customer can see the jobs in the Supply/Demand screen in Inventory

Inventory\On-Hand Availability\Item Supply/Demand

If they can see the Jobs here then a row has been inserted into MTL_DEMAND.

Perform the following steps

1. In MTL_DEMAND check for the status of the UPDATED_FLAG
UPDATED_FLAG Description Action
1 Not processed yet Make sure Planning Mgr. is running
2 Planning Mgr. has picked this up None

2. Check the following profile MRP:Compute Sales Order changes is set
3. Make sure the following columns are populated in MTL_DEMAND
CUSTOMER_ID
BILL_TO_SITE_USE_ID
SHIP_TO_SITE_USE_ID
INVENTORY_ITEM_ID

4. If the UPDATED_FLAG = 2 then proceed to MRP_SALES_ORDER_UPDATES table. Please check the PROCESS_STATUS
PROCESS_STATUS Description Action
2 Waiting to be Processed Make sure Planning Mgr. is running
3 In Process None
4 Error Check ERROR_MESSAGE in this table
5 Successful completion None

5. If records still fails in MRP_SALES_ORDER_UPDATES then check the following columns and make sure that they are NOT NULL. If these two columns are null then MRP will not know what qty's are available and when they are available.
             If these columns are null then most likely there is a problem in MRP_SALES_ORDER_UPDATES and you will need to check the exact error message.

MRP_QUANTITY
MRP_DATE

6. Make sure the item in question has the MRP/MPS attribute set to Consume and Derive
13. Why Resource Requirements are not visible in the MPS Workbench for Discrete Job order types but visible for Planned Orders?

This is as per functionality. For Discrete Jobs, resource requirements are not calculated in MPS. The reason being that when MPS is launched, all the components of the assemblies associated with the Discrete Jobs are not exploded to calculate correct aggregate resource requirements for the assemblies. Resource requirements for Discrete Jobs jobs will be visible in the MRP Workbench.

14.How to purge MRP plans? 

Purge MRP Plans by Naviating to Material Planning: MRP > Names > From the Menu > Edit > Delete
Then save and acknowledge message and concurrent request will launch to purge the plans.

15. Calculate Plan Performance Indicators request completed with Warning. Will it affect MRP results?

You can set this Profile  MRP: Calculate Plan Performance  to No and also you can ignore any warning or error in that request as this will not affect the MRP results

The related program. This was an optional program used to gather info from MRP and load into tables used by the old Business Intelligence programs. Additionally the BIS product has been replaced by the Supply Chain Intelligence (SCI) which does not use the program either. This progam is not used by MRP and is not required. To disable this program set the profile MRP: Calculate Plan Performance = No

16. Users have a subinventory that has recently been changed from non-nettable to nettable. The MRP plan has been rerun as well as the planning detail report.  However, none of the items that have on hand in the subinventory are being picked up by the plan run or the report.  How do we fix this issue?

Perform the following

1. Navigate Material Planner/MRP/Names - select the plan name having the problem - select the Options button - select the Subinventory Netting button - Find the subinventory having the problem and enable the Net checkbox - Save
2. Rerun the MRP plan to pick up the change

17. Why the planned orders generated don't have link with the sales orders demand?

What you are attempting is not available via the standard functionality in the product. The application does not allow users to link sales order or job when release from the planning module

18. MRP or ASCP are not taking precedence of Processing lead time provided in ASL Local attributes over Processing lead time provided in ASL global attributes. Why is this occurring?

Local ASL (Approved Supplier Lists)  are not used in planning  This is as per desgin.