Receipt at Risk

From Oracle user guide, it is said that

“In Receivables, you can apply a receipt to an open debit item before cash is actually received from the bank. Therefore, receipts with a Standard remittance method are considered receipts at risk if they have been confirmed, but not yet cleared. Receipts with a Factored remittance method are at risk if they have not yet been risk-eliminated.”

But when I only select  “At Risk” option as “Y” in receipt summary form, not only above two kinds of receipts are displayed. Some receipt with state of “Remitted” and with receipt class “not remittance” are also displayed. It seems that “All not cleared receipts are considered as “At Risk”“. Is it right?

In the last query, it shows

“SELECT …. FROM AR_CASH_RECEIPTS_V WHERE (AT_RISK=’Y’) order by RECEIPT_NUMBER”

Then I view the view definition of AR_CASH_RECEIPTS_V, the most important section is as

“…

DECODE ( NVL(CR.CONFIRMED_FLAG,’Y’), ‘Y’, DECODE( CR.REVERSAL_DATE, NULL, DECODE( CRH_CURRENT.STATUS, ‘REVERSED’,’N’, DECODE( CRH_CURRENT.FACTOR_FLAG, ‘Y’, DECODE( CRH_CURRENT.STATUS, ‘RISK_ELIMINATED’, ‘N’, ‘Y’), DECODE(CRH_CURRENT.STATUS,’CLEARED’, ‘N’,’Y’))), ‘N’), ‘N’) AT_RISK ,

…”

By the way, I refer the section of user guide to introduce the concept of “Include Receipts at Risk in Customer Balance”.

———————————————————————————————————————————————————–

“In Receivables, you can apply a receipt to an open debit item before cash is actually received from the bank. Therefore, receipts with a Standard remittance method are considered receipts at risk if they have been confirmed, but not yet cleared. Receipts with a Factored remittance method are at risk if they have not yet been risk-eliminated.

You can view the number and amount of receipts at risk and their effect on your customer’s open balance in the Receipts Summary window.

To include receipts at risk and receipts that were created by the Bills Receivables Remittance or Maturity and Risk program, select Yes in the appropriate areas in the Find Receipts window.

To display receipts at risk and include them when calculating a customer’s past due balance in the Account Details window, set the AR: Include Receipts at Risk in Customer Balance profile option to Yes. This profile option affects the Account Details window.

If this profile option is set to No, you can choose to include items at risk by performing the following:

  1. Choose the Include Receipts at Risk option from the Tools menu.
  2. Execute your query.

 Important: The AR: Include Receipts at Risk in Customer Balance profile option and the option on the Tools menu do not affect the customer balance calculation in any Receivables standard reports or listings. These options only affect whether Receivables displays receipts at risk and includes them in the open balance calculation for Account Details.”

Script to Initiatialize Application Context

DECLARE
v_user_id      NUMBER;
v_resp_id      NUMBER;
v_resp_appl_id NUMBER;
BEGIN
SELECT a.user_id,
a.responsibility_id,
a.responsibility_application_id
INTO v_user_id,
v_resp_id,
v_resp_appl_id
FROM FND_USER_RESP_GROUPS_DIRECT a,
fnd_user b,
FND_RESPONSIBILITY_VL c
WHERE a.user_id        =b.user_id
AND a.responsibility_id=c.responsibility_id
AND b.user_name        =    ‘&User_Name’
AND c.responsibility_name =    ‘&Responsibility_Name’;
fnd_global.APPS_INITIALIZE (v_user_id, v_resp_id, v_resp_appl_id);
END;

A SQL Script to Reconcile GL and Sub-ledger

A SQL Script to Reconcile GL and Sub-ledger: The script exact all of the journal lines for specific accounts, which is not imported from sub-ledger.

SELECT gla.*

FROM GL_JE_JOURNAL_LINES_V gla,

  GL_CODE_COMBINATIONS glc

WHERE gla.line_code_combination_id=glc.code_combination_id

AND glc.segment1                  ='xxx'

AND glc.segment4                  ='xxxxxxx'

AND gla.je_source <> 'Purchasing'

and gla.je_source <> 'Payables';

Remove Corrupt(Orphan) Pending Transaction/Move Order

Some days ago, a user reported that some quantity of the item is not available but he/she can’t find out the reservation records. Why? After investigating, there’s a pending transaction in the system while the related SO has been closed and all of the move orders for this SO are closed too. To remove the pending transaction and restore the availability of the lost goods, we find out below script from metalink. In below script, Oracle help you on below situation:

  • Move order released from SO, Move order in Pre-approved status; Move order has not transacted
  • Move order has been closed, but pending transaction records are still existing
  • Pending transaction has a move order line id, but you can not find it out in move order line table
  • Pending transaction has a reservation _id, you can not find out it in mtl_reservations
  • Source ID are different below move order and delivery details
  • Source ID are different between pending transaction and move order line

Below is the script and my comments:

REM $Header: INVCLRMO.sql 115.5 2002/03/09 16:23:25 kadavi noship $
REM
REM (c) Copyright Oracle Corporation 2001
REM All Rights Reserved
REM
REM HISTORY
REM This is script to clear Open move order lines which
REM are not linked to Delivery details ,clean Orphan suggestions
REM in Mtl_material_transactions_temp and remove reservations
REM link to Mtl_material_transactions_temp if reservations are
REM not existing.
REM
REM Also this script creates and drop temp tables so Ct
REM need to manually run this script.
REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single phase=dat \
REM dbdrv: checkfile:~PROD:~PATH:~FILE
WHENEVER SQLERROR CONTINUE ROLLBACK; –when an error occurred during the sql executing, rollback
prompt dropping tables -showing “droping tables”
DROP TABLE mtl_mmtt_backup; –clean/drop the previous backup table
DROP TABLE mtl_mtrl_backup;
DROP TABLE mtl_msnt_backup;
DROP TABLE mtl_mtlt_backup;
prompt create table for MMTT backup –start to create backup tables
CREATE TABLE mtl_mmtt_backup AS
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mmtt.move_order_line_id IS NOT NULL
AND mmtt.move_order_line_id    = mtrl.line_id
AND mtrl.line_status           = 7 –preapproved
AND mtrl.header_id             = mtrh.header_id
AND mtrh.move_order_type       = 3 –pick-wave
AND NOT EXISTS
(SELECT ‘Y’
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status      = ‘S’ –released to warehouse
)
)
/
–select to backup the lines whose linked move order line status is pre-approved, the move order is
–generated from realeasing SO, meantime there’s no line in the delivery details or there is
–but it is has been released to warehouse
prompt select allocation records for closed move order
INSERT
INTO mtl_mmtt_backup
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status          = 5
)
/ –select to backup the lines whose move order line status is closed
prompt select allocation records with missing move order
INSERT
INTO mtl_mmtt_backup
(SELECT mmtt.*
FROM mtl_material_transactions_temp mmtt
WHERE move_order_line_id IS NOT NULL
AND NOT EXISTS
(SELECT mtrl.line_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = mmtt.move_order_line_id
)
)
/ –select to backup the lines who has a move order link, but corresponding move order is not existing now.
prompt create backup table for move order lines
CREATE TABLE mtl_mtrl_backup AS
(SELECT mtrl.*
FROM mtl_txn_request_lines mtrl,
mtl_txn_request_headers mtrh
WHERE mtrl.line_status   = 7
AND mtrl.header_id       = mtrh.header_id
AND mtrh.move_order_type = 3
AND NOT EXISTS
(SELECT ‘Y’
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status      = ‘S’
)
)
/
–select to backup the move order lines whose status is pre-approved, the move order is
–generated from realeasing SO, meantime there’s no line in the delivery details or there is
–but it is has been released to warehouse
prompt create backup table for serial number allocations
CREATE TABLE mtl_msnt_backup AS
(SELECT msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mmtt_backup
)
)
/
–backup all of the series records under the backuped  mtl_material_transactions_temp
prompt create backup table for lot number allocations
CREATE TABLE mtl_mtlt_backup AS
(SELECT mtlt.*
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id IN
(SELECT transaction_temp_Id FROM mtl_mmtt_backup
)
)
/
–backup all of the lots records under the backuped mtl_material_transactions_temp
prompt select serial number allocations for lot controlled items
INSERT
INTO mtl_msnt_backup
(SELECT msnt.*
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT serial_transaction_temp_id FROM mtl_mtlt_backup
)
)
/
–backup all of the serials records for the lot and serial control items
prompt delete serial number allocations
DELETE
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_msnt_backup
) /
–delete all of the serials records which has been backuped
prompt delete lot number allocations
DELETE
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mtlt_backup
) /
–delete all of the lot records which has been backuped
prompt delete allocations
DELETE
FROM mtl_material_transactions_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id FROM mtl_mmtt_backup
) /
–delete all of the allocations (records in mmtt table)which has been backuped
prompt close move order lines
UPDATE mtl_txn_request_lines
SET quantity   = NVL(quantity_detailed, 0) ,
line_status  = 5
WHERE line_id IN
(SELECT line_id FROM mtl_mtrl_backup
) /
–close all of the move order which has been backuped in mtl_mtrl_backup
prompt update transaction source on the move order line
UPDATE mtl_txn_request_lines mtrl
SET mtrl.txn_source_line_id =
(SELECT DISTINCT(source_line_id)
FROM wsh_delivery_details
WHERE move_order_line_id = mtrl.line_id
AND released_status      = ‘S’
)
WHERE mtrl.line_status = 7
AND EXISTS
(SELECT delivery_detail_id
FROM wsh_delivery_details wdd
WHERE move_order_line_Id = mtrl.line_Id
AND wdd.source_line_id  <> mtrl.txn_source_line_id
AND wdd.source_line_id   > 0
AND wdd.released_status  = ‘S’
) /
–The move order has linked with delivery details and delivery details has been released to warehouse
–In the delivery details, there’s a source line id but the source line id is different with the one in move order
–And the move order is in the status of pre-approved.
–If above is true, change the source line id in move order line to the one in delivery details
prompt update transaction source on the allocation
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.trx_source_line_id =
(SELECT txn_source_line_id
FROM mtl_txn_request_lines
WHERE line_id = mmtt.move_order_line_id
)
WHERE mmtt.transaction_type_id IN (52, 53)
AND mmtt.move_order_line_id    IS NOT NULL
AND EXISTS
(SELECT line_id
FROM mtl_txn_request_lines
WHERE line_status       = 7
AND line_id             = mmtt.move_order_line_id
AND txn_source_line_id <> mmtt.trx_source_line_id
) /
–pending transaction is from internal order pick and sales order pick
–pending transaction is linked with move order, move order is in Pre-approved
–source line id in pending transaction is different with the one in move order
–If above is true, change the source line id in pending transaction to the one in move order
prompt update allocations for missing reservations
UPDATE mtl_material_transactions_temp mmtt
SET reservation_id         = NULL
WHERE mmtt.reservation_id IS NOT NULL
AND NOT EXISTS
(SELECT mr.reservation_id
FROM mtl_reservations mr
WHERE reservation_id = mmtt.reservation_id –there’s a reservation id in pending transaction,
) / COMMIT / EXIT; –but there’s no in reservation record; If above is true, change it to null.
/

Query a Specific User’s Authorized Concurrent Requests

Somebody always asked that if I have this report, which responsibility I can run this report in, what I can do in the system, etc. There’s no a separate form to show you these question. I prepared a simple report to answer these questions by SQL Developer and export it to an XML file. If it is helpful for your works, feel free to download, extract it and import to your SQL developer.

Download: User’s Requests

To import the xml file, from here!

Reference:  The previous post “Oracle SQL Developer Report“.

Search All of The Profile Option Setup on Different Level

Sometimes you need to know who (users or reponsibilities) have the overided profile option for having speicific authority. We can’t get it from front-end but can get it from below SQL script.

SELECT a.profile_option_id,
b.profile_option_name,
b.user_profile_option_name,
DECODE(c.level_id,10001,’Site’,10003,’Responsibility’,10004,’User’,10002, ‘Application’,’Other’) AS “PROFILE_LEVEL”,
DECODE(c.level_id,10001,’Site’,10003,(SELECT r.responsibility_name FROM fnd_responsibility_tl r WHERE r.responsibility_id=c.level_value AND r.language=’US’),10004,(SELECT u.user_name FROM fnd_user u WHERE u.user_id=c.level_value),1002,(SELECT d.application_name FROM fnd_application_tl d WHERE d.application_id=c.level_value),’Others’) as “LEVEL_VALUE”, c.profile_option_value
FROM fnd_profile_options a,
fnd_profile_options_tl b,
fnd_profile_option_values c
WHERE a.profile_option_id=c.profile_option_id
AND a.profile_option_name=b.profile_option_name
AND b.language =’US’
AND b.user_profile_option_name LIKE ‘&Profile’;