RMA

RMA Orders

There are three ways to create RMA’s within Order Management.

• First, identify a sales order to be returned and query the order lines. After you have selected the sales order  or order lines, use the Copy function in the Actions list to generate the return order or line by specifying an RMA line type.
• Second, reference a sales order, invoice, PO number or serial number of an item directly in the Return Reference field within the Line Items tab of the Sales Order form.
• Lastly, for return without originating sales order line, manually enter return line information and choose the appropriate return line type in the Sales Order form.

Business Processes of RMA

Ø  RMA with credit only
Some Company issues a credit without the customer returning the product.

Accept a returns for credit by applying credits to original invoices or creating on account credits.

Ø  RMA with receipt and credit
Customer returns a product and receives credit.

Ø  RMA with receipt and no credit
Customer returns a product we sent to them on a trial basis or at no charge

RMA Cycle

 

• Create an RMA having a single line whose originating transaction is unknown
• Book the RMA
• Receive the RMA using the Receipts form of Oracle Purchasing
• Check the on-hand quantity of the item in Inventory to verify that correct quantity was received
• Fulfill RMA line
• Generate a credit memo
• View the Credit Memo in Order Management
• Check the Shipped and Fulfilled quantity on the RMA line

ITEM Attributes For RMA

Order Management Tab: Returnable,

Shippable and Transactable = Yes,

RMA Inspection Required = Yes or No

Receiving Tab: Receipt Routing = Inspection (if required)

Invoicing Tab: Invoicable Item = Yes or No, Invoice Enabled = Yes or No

Concurrent Program Processing time and details Query

The below script will help to get the processing time of any concurrent program. This will help to those who are monitoring the program in production having a performance issues.

SELECT FCR.REQUEST_ID ,
  FR.RESPONSIBILITY_NAME,
  FPT.USER_CONCURRENT_PROGRAM_NAME USER_CONCURRENT_PROGRAM_NAME ,
  FCR.ACTUAL_START_DATE ACTUAL_START_DATE ,
  FCR.ACTUAL_COMPLETION_DATE ACTUAL_COMPLETION_DATE,
  FLOOR(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60*60)/3600)
  || ' Hrs. '
  || FLOOR((((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60*60) - floor(((fcr.actual_completion_date-fcr.actual_start_date)*24*60*60)/3600)*3600)/60)
  || ' Min. '
  || ROUND((((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60*60) - FLOOR(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60*60)/3600)*3600 - (FLOOR((((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60*60) - FLOOR(((FCR.ACTUAL_COMPLETION_DATE-FCR.ACTUAL_START_DATE)*24*60*60)/3600)*3600)/60)*60) ))
  || ' Secs. ' PROCESSING_TIME ,
  DECODE(FCP.CONCURRENT_PROGRAM_NAME,'ALECDC',FCP.CONCURRENT_PROGRAM_NAME
  ||'['
  ||FCR.DESCRIPTION
  ||']',FCP.CONCURRENT_PROGRAM_NAME) CONCURRENT_PROGRAM_NAME ,
  DECODE(FCR.PHASE_CODE,'R','Running','C','Completed','P','Pending','I','Inactive',FCR.PHASE_CODE) PHASE ,
  DECODE(FCR.STATUS_CODE,'A','Waiting','B','Resuming','C','Normal','D','Cancelled','E','Error','F','Scheduled','G','Warning','H','On Hold','I','Normal','M','No Manager','Q','Standby','R','Normal','S','Suspended','T','Terminating','U','Disabled','W','Paused','X','TERMINATED','Z','Waiting',FCR.STATUS_CODE) STATUS,
  FCR.COMPLETION_TEXT COMPLETION_STATUS
FROM APPS.FND_CONCURRENT_PROGRAMS FCP ,
  APPS.FND_CONCURRENT_PROGRAMS_TL FPT ,
  APPS.FND_CONCURRENT_REQUESTS FCR,
  APPS.FND_RESPONSIBILITY_TL FR,
  APPS.FND_RESPONSIBILITY F
WHERE FCR.CONCURRENT_PROGRAM_ID     = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID      = FCP.APPLICATION_ID
AND FCR.CONCURRENT_PROGRAM_ID       = FPT.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID      = FPT.APPLICATION_ID
AND FPT.LANGUAGE                    = USERENV('Lang')
AND FCR.ACTUAL_START_DATE          IS NOT NULL
AND FCR.RESPONSIBILITY_ID=F.RESPONSIBILITY_ID
AND FR.RESPONSIBILITY_ID=F.RESPONSIBILITY_ID
AND FR.LANGUAGE                     = USERENV('Lang')
AND FPT.USER_CONCURRENT_PROGRAM_NAME='XX CONC Program NAME'
ORDER BY FCR.LAST_UPDATE_DATE DESC;

Order with Customer(Billto & Shipto) Script

SELECT ooh.header_id,'BILL' SiteUse,
  ooh.sold_to_org_id,--ooh.sold_from_org_id,
  ooh.INVOICE_TO_ORG_ID,
  bill_party.party_name bill_customer,
  bill_cus.account_number customer_number,
  hcs_bill.location,
  hl_bill.address1,
  hl_bill.address2,
  hl_bill.city
  ||', '
  ||hl_bill.state
  ||' '
  ||hl_bill.postal_code
  ||' '
  ||hl_bill.country
FROM oe_order_headers_all ooh,
  hz_cust_accounts bill_cus,
  hz_parties bill_party,
  HZ_CUST_SITE_USES_ALL hcs_bill,
  HZ_CUST_ACCT_SITES_ALL hca_bill,
  hz_party_sites hps_bill,
  hz_locations hl_bill
WHERE ooh.header_id         =252047
AND ooh.SOLD_TO_ORG_ID   =bill_cus.cust_account_id
AND bill_party.party_id       =bill_cus.party_id
AND ooh.INVOICE_TO_ORG_ID   =hcs_bill.site_use_id
AND hcs_bill.site_use_code    ='BILL_TO'
AND hca_bill.cust_acct_site_id=hcs_bill.cust_acct_site_id
AND hps_bill.party_site_id    =hca_bill.party_site_id
AND hl_bill.location_id       =hps_bill.location_id
union
SELECT ooh.header_id,'SHIP' SiteUse,
  --rct.bill_to_customer_id,
  ooh.SHIP_TO_ORG_ID,
  ooh.SOLD_TO_ORG_ID,
  Sold_party.party_name Sold_customer,
  sold_cus.account_number customer_number,
  hcs_ship.location,
  hl_ship.address1,
  hl_ship.address2,
  hl_ship.city
  ||', '
  ||hl_ship.state
  ||' '
  ||hl_ship.postal_code
  ||' '
  ||hl_ship.country
FROM oe_order_headers_all ooh,
  hz_cust_accounts sold_cus,
  hz_parties sold_party,
  HZ_CUST_SITE_USES_ALL hcs_ship,
  HZ_CUST_ACCT_SITES_ALL hca_ship,
  hz_party_sites hps_ship,
  hz_locations hl_ship
WHERE ooh.header_id         =252047
AND ooh.SOLD_TO_ORG_ID   =sold_cus.cust_account_id
AND sold_party.party_id       =sold_cus.party_id
AND ooh.SHIP_TO_ORG_ID   =hcs_ship.site_use_id
AND hcs_ship.site_use_code    ='SHIP_TO'
AND hca_ship.cust_acct_site_id=hcs_ship.cust_acct_site_id
AND hps_ship.party_site_id    =hca_ship.party_site_id
AND hl_ship.location_id       =hps_ship.location_id;

Join between OM, WSH, AR Tables

SELECT ooh.order_number
              ,ool.line_id
              ,ool.ordered_quantity
              ,ool.shipped_quantity
              ,ool.invoiced_quantity
              ,wdd.delivery_detail_id
              ,wnd.delivery_id
              ,rctl.interface_line_attribute1
              ,rctl.interface_line_attribute3
              ,rctl.interface_line_attribute6
              ,rct.org_id
              ,rct.creation_date
              ,trx_number
              ,rctl.quantity_ordered
              ,rct.interface_header_context
  FROM oe_order_headers_all ooh
             ,oe_order_lines_all ool
             ,wsh_delivery_details wdd
             ,wsh_new_deliveries wnd
             ,wsh_delivery_assignments wda
             ,ra_customer_trx_all rct
             ,ra_customer_trx_lines_all rctl
 WHERE ooh.header_Id=ool.header_id
      AND wdd.source_header_id=ooh.header_id
      AND wdd.delivery_detail_Id=wda.delivery_detail_id
      AND wda.delivery_id=wnd.delivery_id
      AND rctl.interface_line_attribute1=to_char(ooh.order_number)
      AND rctl.interface_line_attribute6=to_char(ool.line_id)
      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)
      AND rctl.customer_trx_id=rct.customer_trx_id
      AND rct.interface_header_context='ORDER ENTRY'

Join of Shipping Tables
________________________
WSH_NEW_DELIVERIES
WSH_DELIVERY_DETAILS
WSH_DELIVERY_ASSIGNMENTS
WSH_DELIVERY_DETAILS
SOURCE_HEADER_ID = OE_ORDER_HEADERS_ALL.HEDAER_ID
SOURCE_HEADER_NUMBER = OE_ORDER_HEADERS_ALL.ORDER_NUMBER
SOURCE_LINE_ID = OE_ORDER_LINES_ALL.LINE_ID
WSH_DELIVERY_ASSIGNMENTS
DELIVERY_DETAIL_ID = WSH_DELIVERY_DETAILS. DELIVERY_DETAIL_ID
WSH_NEW_DELIVERIES
WSH_NEW_DELIVERIES.DELIVERY_ID = WSH_DELIVERY_ASSIGNMENTS.DELIVERY_ID

AR Bill To and SHip To Customer Scripts

AR Bill To Customer Query

SELECT rct.customer_trx_id,
  rct.bill_to_customer_id,
  rct.bill_to_site_use_id,
  bill_party.party_name bill_customer,
  bill_cus.account_number customer_number,
  hcs_bill.location,
  hl_bill.address1,
  hl_bill.address2,
  hl_bill.city
  ||', '
  ||hl_bill.state
  ||' '
  ||hl_bill.postal_code
  ||' '
  ||hl_bill.country
FROM ra_customer_trx_all rct,
  hz_cust_accounts bill_cus,
  hz_parties bill_party,
  HZ_CUST_SITE_USES_ALL hcs_bill,
  HZ_CUST_ACCT_SITES_ALL hca_bill,
  hz_party_sites hps_bill,
  hz_locations hl_bill
WHERE customer_trx_id         =79364
AND rct.bill_to_customer_id   =bill_cus.cust_account_id
AND bill_party.party_id       =bill_cus.party_id
AND rct.bill_to_site_use_id   =hcs_bill.site_use_id
AND hcs_bill.site_use_code    ='BILL_TO'
AND hca_bill.cust_acct_site_id=hcs_bill.cust_acct_site_id
AND hps_bill.party_site_id    =hca_bill.party_site_id
AND hl_bill.location_id       =hps_bill.location_id;

AR Ship To Customer Query

SELECT rct.customer_trx_id,
  rct.ship_to_customer_id,
  rct.ship_to_site_use_id,
  ship_party.party_name bill_customer,
  ship_cus.account_number customer_number,
  hcs_ship.location,
  hl_ship.address1,
  hl_ship.address2,
  hl_ship.city
  ||', '
  ||hl_ship.state
  ||' '
  ||hl_ship.postal_code
  ||' '
  ||hl_ship.country
  FROM ra_customer_trx_all rct,
  hz_cust_accounts ship_cus,
  hz_parties ship_party,
  HZ_CUST_SITE_USES_ALL hcs_ship,
  HZ_CUST_ACCT_SITES_ALL hca_ship,
  hz_party_sites hps_ship,
  hz_locations hl_ship
WHERE customer_trx_id         =79364
AND rct.ship_to_customer_id   =ship_cus.cust_account_id
AND ship_party.party_id       =ship_cus.party_id
AND rct.ship_to_site_use_id   =hcs_ship.site_use_id
AND hcs_ship.site_use_code    ='SHIP_TO'
AND hca_ship.cust_acct_site_id=hcs_ship.cust_acct_site_id
AND hps_ship.party_site_id    =hca_ship.party_site_id
AND hl_ship.location_id       =hps_ship.location_id;

List of Responsibility for a User

SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
FND_USER_RESP_GROUPS_ALL FURGA,
FND_USER FU,
FND_USER FUCB,
FND_USER FULUB,
FND_USER FULUL,
FND_APPLICATION FA,
FND_RESPONSIBILITY_TL FRTL,
FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND fu.user_name='USERNAME'
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = 'US'
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;

How to add responsibility using PLSQL

BEGIN
        fnd_user_pkg.addresp ('MYUSERID','APPL_SHORT_NAME','REPONSIBILITY_SHORT_NAME','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,'');
        COMMIT;
        DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
        WHEN OTHERS
        THEN
        DBMS_OUTPUT.put_line (   ' Responsibility is not added due to '|| SQLCODE||' - '|| SUBSTR (SQLERRM, 1, 100));
        ROLLBACK;
END;
/

Use of Account Set Flag in AR

ACCOUNT_CLASS defines which type of distribution row you are on. The ACCOUNT_CLASS REC represents the receivable account and is for the total amount of the invoice. There can be at most two REC rows. One that has a ACCOUNT_SET_FLAG set to Y and the other has ACCOUNT_SET_FLAG set to N. Use LATEST_REC_FLAG to join to the later of the two rows. ACCOUNT_SET_FLAG is Y if this row is part of an account set. An account set is a set of rows that represent a model distribution. Account sets are used for invoices with rules. The rows represent how the actual distribution rows should be created and what percentage of the actual distribution should be allocated to each account. For invoices with rules, the distributions are not created when the invoice is initially created. Instead, the invoices are created when the Revenue Recognition program is run.

AR Links to GL

Link between GL to AR

 When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns that is then pushed the detailes in GL_JE_LINES table. Here are the details for Refrence columns.

Adjustments

• REFERENCE21 :posting_control_id
• REFERENCE22 :adjustment_id
• REFERENCE23 :line_id
• REFERENCE24 :trx_number
• REFERENCE25 :adjustment_number
• REFERENCE26 :cust_trx_type
• REFERENCE27 :bill_to_customer_id
• REFERENCE28 :ADJ
• REFERENCE29 :source_type prefixed by ‘ADJ’
• REFERENCE30 :AR_ADJUSTMENTS

Transactions

• REFERENCE21 :posting_control_id
• REFERENCE22 :customer_trx_id
• REFERENCE23 :cust_trx_line_gl_dist_id
• REFERENCE24 :trx_number
• REFERENCE25 :cust.account_number
• REFERENCE26 :CUSTOMER
• REFERENCE27 :bill_to_customer_id
• REFERENCE28 :type(CM/DM/CB/INV)
• REFERENCE29 :type||account_class
• REFERENCE30 :RA_CUST_TRX_LINE_GL_DIST

Applications

• REFERENCE21 :posting_control_id
• REFERENCE22 :cash_receipt_id||receivable_application_id for CASH /receivable_application_id for CM
• REFERENCE23 :line_id
• REFERENCE24 :receipt_number for CASH / trx_number for CM
• REFERENCE25 :trx_number if status = ¿APP¿ / NULL for unapplied records
• REFERENCE26 :cust_trx_type
• REFERENCE27 :pay_from_customer for CASH / bill_to_customer_id for CM
• REFERENCE28 :application_type (TRADE or CCURR for CASH / CMAPP for CM)
• REFERENCE29 :application_type||source_type
• REFERENCE30 :AR_RECEIVABLE_APPLICATIONS

Bills Receivable

• REFERENCE21 :posting_control_id
• REFERENCE22 :transaction_history_id
• REFERENCE23 :line_id
• REFERENCE24 :trx_number
• REFERENCE25 :customer_Trx_id
• REFERENCE26 :cust_trx_type
• REFERENCE27 :drawee_id
• REFERENCE28 :cust_trx_type
• REFERENCE29 :BR_||source_type
• REFERENCE30 :AR_TRANSACTION_HISTORY

Key Tables

In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down information.

• ra_customer_trx_all: Transactions accounting
• ra_cust_trx_line_gl_dist_all: Transactions accounting
• ar_adjustments_all:Adjustments accounting
• ar_distributions_all:Adjustments accounting
• ar_cash_receipt_history_all:Receitps accounting
• ar_distributions_all: Receitps accounting
• ar_receivable_applications_all: Receipt applications accounting
• ar_distributions_all:Receipt applications accounting & misc receipts accounting
• ar_misc_cash_distributions_all:Misc receipts accounting

Oracle AOL Object Migration Scripts

Download Concurrent Program:
---------------------------
FNDLOAD apps/simple4u@DCMEMI O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <ldt name>.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XXXX"  -- Short name

FNDLOAD apps/simple4u@DCMEMI O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct <ldt name>.ldt PROGRAM CONCURRENT_PROGRAM_NAME="XXXX" -- Short name

Download Request Set:
--------------------
FNDLOAD apps/simple4u@DCMEMI O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct <ldt name>.ldt REQ_SET REQUEST_SET_NAME="XXXX" -- Short name

Download Request Set Link:
-------------------------
FNDLOAD apps/simple4u@DCMEMI O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct C<ldt name>.ldt REQ_SET_LINKS REQUEST_SET_NAME="XXXX" -- Short name

Download Request Set Group:
--------------------------
FNDLOAD apps/simple4u@DCMEMI O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct <ldt name>.ldt REQUEST_GROUP REQUEST_GROUP_NAME="Request Group Name"


Download Profiles:
--------------------------
FNDLOAD apps/simple4u@DCMEMI 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct <ldt name>.ldt PROFILE PROFILE_NAME=<profile name>


Download Value sets:
-----------------
FNDLOAD apps/simple4u@DCMEMI O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct <ldt name>.ldt VALUE_SET FLEX_VALUE_SET_NAME="XXXX"  -- value set name


 for uploading value set:
------------------------
$FND_TOP/bin/FNDLOAD $APPSID 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct $HOME_PATH/<ldt name>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE


 for uploading profile:
------------------------
$FND_TOP/bin/FNDLOAD $APPSID 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct $HOME_PATH/<ldt name>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE


 for uploading Concurrent Program:
---------------------------------
$FND_TOP/bin/FNDLOAD $APPSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $HOME_PATH/<ldt name>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE


for uploading Request Set:
------------------------
$FND_TOP/bin/FNDLOAD $APPSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct $HOME_PATH/<ldt name>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE


for uploading Request Set Link:
------------------------------
$FND_TOP/bin/FNDLOAD $APPSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct $HOME_PATH/<ldt name>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE



for uploading Request group
----------------------------
$FND_TOP/bin/FNDLOAD $APPSID 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct $HOME_PATH/<ldt name>.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

API to upload Lookup Code

The below API can be used to upload only specific lookup codes.

FND_LOOKUP_VALUES_PKG.INSERT_ROW

This is good in case you are migrating only 1 or two lookup codes.