Script 1:(Works in Both 11i & R12)
SELECT gcc.segment1
||’.’
||gcc.segment2
||’.’
||gcc.segment3 ACCOUNT,
A1.DESCRIPTION
||’-‘
||A2.DESCRIPTION
||’-‘
|| A3.DESCRIPTION DECS
FROM fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
gl_code_combinations gcc
WHERE a1.flex_value =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT1′
)
AND a2.flex_value =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT2′
)
AND a3.flex_value =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT3′
);
Script 2: ( Applicable in R12)
SELECT gcc.CONCATENATED_SEGMENTS,
gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations_kfv gcc;
How to Create RMA in R12 Order Management
I am taking the case of creation of Return Sales Order with Receipt and Credit Memo.
1.Create New Sales Order with Order Type = “Return Only . ( we can take Mixed or any other that allow return line)
2.Since Header Order Type is “Return Only”, based on the transaction Type Setup , System will default Line Type .In transaction type set the Default RMA = “Return (Receipt)” , with this Setup system will populate the Line Type = “Return (Receipt)” , enter other details like
Item #
Qty etc.
3.Go to Return tab ( Lines) and Enter Return Reason (There is LOV for Return Reason select any relevent value from LOV ).
4.Book the RMA.
5.Requery the RMA , check the Line Status , It should be “Awaiting Return”.
6.Select Receipt (Purchasing Responsibility)
7.Goto Customer Tab and Enter the RMA Num .
8.Press Find Button.
9.Ignore the receipt Header , and go to Receipts form
10.Select the line
11.Press Save,System will ask for the Sub Inventory.Best bet is enter “Stores” ( Here I am taking the case Routing = Direct Delivery)
12.Press Save Button. Save action will automatically submit the “Receiving Transaction Processor (RTP) concurrent Program.
13.Check Order line status , it should be “Returned”.
14.Go to View > request . Submit Concurrent Program “Workflow background Process”
with Item Type = OM Order Line
Process Deferred = Yes
Process Timeout = No.
Query Order line again , Status Should be “Closed”
15.Step # 14 also submit “Autoinvoice” concurrent Program.
16.Goto Transaction (Receivable Responsibility)
Go to Find Window
Enter Sales Order # in Sales Order Number Field
Press Find
System should display your Invoice (Credit memo)
Examine the Invoice and you are good to go.
Share this:
Oracle R12 – RFQ to PO Receipt Cycle
High -Lights of this Cycle is to Create:
Once RFQ is created , Submit concurrent job “Request to Print the RFQ” for a supplier. On completion this job will increment print count for that supplier as shown below.
Once we print the RFQ , status of RFQ become Printed , and print count will increment for all suppliers .Since we got response from the Office Supplier , Inc Site – OFFICESUPPLIER , Responded field populated for this supplier only .
Share this:
How to Get GL Code Combination Description
SELECT gcc.segment1
||’.’
||gcc.segment2
||’.’
||gcc.segment3 ACCOUNT,
A1.DESCRIPTION
||’-‘
||A2.DESCRIPTION
||’-‘
|| A3.DESCRIPTION DECS
FROM fnd_flex_values_vl A1,
fnd_flex_values_vl A2,
fnd_flex_values_vl A3,
gl_code_combinations gcc
WHERE a1.flex_value =gcc.segment1
AND a1.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT1′
)
AND a2.flex_value =gcc.segment2
AND a2.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT2′
)
AND a3.flex_value =gcc.segment3
AND a3.FLEX_VALUE_SET_ID IN
(SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = ‘GL#’
AND enabled_flag = ‘Y’
AND application_column_name=’SEGMENT3′
);
Script 2: ( Applicable in R12)
SELECT gcc.CONCATENATED_SEGMENTS,
gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id, gcc.code_combination_id) acc_description
FROM gl_code_combinations_kfv gcc;
Share this:
Important OPM Process Execution & Inventory Tables in R12
OPM PROCESS EXECUTION TABLES R12
Share this:
How to Stop/Block Work Flow Mailer Send Mail from the Cloned Instance
Here is the solution for the Work flow mailer to stop sending older mails in the Test Instance
UPDATE WF_NOTIFICATIONS
Check this Steps:
Application Manager — > Application Dashboard ->
Application System– > Dev–> service Components –> Component Details
Set override Address : Dev: Work Flow Notification Mailer
Enter the Over Ride Address : *******. Mail .Com
Update the scripts:
If this is Entered he/She will receive all the notification mails which has been triggered in the DEV or UAT
Step 1Login to “Workflow Administrator Web Applications”
![Image]( "Image")
Step 2
![Image]( "Image")
Ensure that Notification Mailer is running, and then click on icon as below
Step 3
![Image]( "Image")
Click on “View Details”
Step 4.
![Image]( "Image")
Click on “Set Override Address”
Step 5.
![Image]( "Image")
Finally you can change the email address here. Please read the instructions in red carefully.
Share this: