Introduction
This Query will help to Extract the Credit Memo Data in Oracle Apps.
SQL Query:
SELECT CASE rcta.interface_header_context
WHEN ‘CLAIM’
THEN ‘TRADE MANAGEMENT’
WHEN ‘ORDER ENTRY’
THEN ‘Order Entry Sales Order’
ELSE ‘MANUAL-CREDIT’
END SOURCE,
rcta.trx_number AS “Transaction Number”,
rc.customer_name AS “Customer Name”,
rc.customer_number AS “Customer Number”,
rsu.site_use_id AS “Site ID”, rcta.trx_date AS “Transaction Date”,
rctlgda.gl_date AS “GL Date”,
TRUNC (rcta.creation_date) AS “Creation Date”, rctta.NAME AS “Type”,
NVL (flv.meaning, NULL) AS “Meaning”,
rctlgda.acctd_amount “Extended Amount”, jrs.NAME AS “Rep Group”,
jrs.salesrep_number AS “Rep Number”,
gcc.segment2 AS “Prime Account Number”,
ffvv.description AS “Account Description”,
rpsa.exchange_rate AS “Exchange Rate”,
CASE gcc.segment4
WHEN ‘000’
THEN ‘Default’
WHEN ‘010’
THEN ‘Domestic’
WHEN ‘020’
THEN ‘Specialty Retail’
WHEN ‘030’
THEN ‘Corporate Markets’
WHEN ‘040’
THEN ‘International’
WHEN ‘050’
THEN ‘Intercompany’
WHEN ‘060’
THEN ‘Commercial’
ELSE ‘NEW DIVISION?’
END division,
CASE gcc.segment5
WHEN ‘000’
THEN ‘Default’
WHEN ‘004’
THEN ‘Tritan’
WHEN ‘005’
THEN ‘Steel Vacuumware’
WHEN ‘006’
THEN ‘Jugs and Coolers’
WHEN ‘007’
THEN ‘Lunch Kits’
WHEN ‘008’
THEN ‘Glass Vacuumware’
WHEN ‘009’
THEN ‘Carafes’
WHEN ‘010’
THEN ‘Nissan’
WHEN ‘011’
THEN ‘Thermocafe’
ELSE ‘NEW PRODUCT LINE?’
END productline,
rctlgda.account_class
FROM ra_customers rc,
ra_addresses_all raa,
ra_site_uses_all rsu,
ra_customer_trx_all rcta,
ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctlgda,
gl_code_combinations gcc,
ra_cust_trx_types_all rctta,
fnd_lookup_values flv,
fnd_flex_values_vl ffvv,
jtf_rs_salesreps jrs,
ar_payment_schedules_all rpsa
WHERE rc.customer_id = raa.customer_id
AND raa.address_id = rsu.address_id
AND rsu.site_use_id = rcta.bill_to_site_use_id
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.customer_trx_line_id = rctlgda.customer_trx_line_id
AND rctlgda.code_combination_id = gcc.code_combination_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND gcc.segment2 = ffvv.flex_value
AND rc.customer_id = rpsa.customer_id
AND rpsa.cust_trx_type_id = rctta.cust_trx_type_id(+)
AND rpsa.customer_trx_id = rcta.customer_trx_id(+)
AND rcta.primary_salesrep_id = jrs.salesrep_id(+)
AND rcta.org_id = jrs.org_id(+)
AND rsu.site_use_code = ‘BILL_TO’
AND rsu.org_id = 82
AND rctlgda.gl_date BETWEEN ’01-AUG-2021′ AND ’31-AUG-2021′
AND rctlgda.gl_date IS NOT NULL
AND rctta.TYPE = ‘CM’
AND rcta.reason_code = flv.lookup_code(+)
AND flv.lookup_type(+) = ‘CREDIT_MEMO_REASON’
AND ffvv.flex_value_set_id = 1005946
–and rcta.trx_number = ‘2905’
AND rcta.complete_flag = ‘Y’
ORDER BY rcta.trx_number
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your question
