1.Overview
This document talks about getting the report of detailed sales margin by customer by invoice by item (both Invoice and credit memo).
2.Technologies and Tools Used
The following technologies have been used to achieve the expected output.
- SQL Code
3.Use Case
This report is used on a monthly basis by the customer to check the sales details of the items by invoice (both Invoice and credit memo)
This document gives you the SQL query to fetch the details.
4.Architecture
Column Descriptions
Step 1:
Run this package:
create or replace PACKAGE pkgutil AS
FUNCTION fntranactualcost (
p_line_id oe_order_lines_all.line_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgetexchangerate (
p_trx_number ra_customer_trx_all.trx_number%TYPE
) RETURN NUMBER
RESULT_CACHE;
FUNCTION sfgetpartyname (
p_cust_accounts hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetcustcode (
p_cust_account_id hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE;
FUNCTION sfgetoperating_ut (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN NUMBER
RESULT_CACHE;
END pkgutil;
/
create or replace PACKAGE BODY pkgutil AS
————————————————————
/*To get Transaction actual cost */
————————————————————
FUNCTION fntranactualcost (
p_line_id oe_order_lines_all.line_id%TYPE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
mmt.actual_cost
INTO lv_result
FROM
wsh_delivery_details wdd
LEFT JOIN mtl_material_transactions mmt ON ( wdd.delivery_detail_id = mmt.picking_line_id )
LEFT JOIN mtl_transaction_accounts mta ON ( mmt.transaction_id = mta.transaction_id )
WHERE
wdd.source_line_id = p_line_id
AND mta.primary_quantity > 0;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END fntranactualcost;
————————————————————
/*To get exchange rate */
————————————————————
FUNCTION sfgetexchangerate (
p_trx_number ra_customer_trx_all.trx_number%TYPE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT DISTINCT
(exchange_rate )
INTO lv_result
— , o.operating_unit
FROM
ra_customer_trx_all
WHERE
trx_number = p_trx_number
AND exchange_rate IS NOT NULL;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END sfgetexchangerate;
————————————————————
/*To get Operating Unit */
————————————————————
FUNCTION sfgetoperating_ut (
p_organization_id org_organization_definitions.organization_id%TYPE
) RETURN NUMBER
RESULT_CACHE
IS
lv_result NUMBER;
BEGIN
SELECT
operating_unit
INTO lv_result
FROM
org_organization_definitions
WHERE
organization_id = p_organization_id;
RETURN lv_result;
EXCEPTION
WHEN OTHERS THEN
RETURN p_organization_id;
END sfgetoperating_ut;
————————————————————
/*To get Party name */
————————————————————
FUNCTION sfgetpartyname (
p_cust_accounts hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(100);
BEGIN
SELECT
hzp.party_name
INTO lv_result
FROM
apps.hz_parties hzp,
hz_cust_accounts hca
WHERE
hzp.party_id = hca.party_id
AND hca.cust_account_id = p_cust_accounts;
RETURN nvl(lv_result, p_cust_accounts);
EXCEPTION
WHEN OTHERS THEN
RETURN p_cust_accounts;
END sfgetpartyname;
————————————————————
/*To get customer code */
————————————————————
FUNCTION sfgetcustcode (
p_cust_account_id hz_cust_accounts.cust_account_id%TYPE
) RETURN VARCHAR2
RESULT_CACHE
AS
lv_result VARCHAR2(100);
BEGIN
SELECT
hca.account_number
INTO lv_result
FROM
apps.hz_parties hzp,
hz_cust_accounts hca
WHERE
hzp.party_id = hca.party_id
AND hca.cust_account_id = p_cust_account_id;
RETURN nvl(lv_result, p_cust_account_id);
EXCEPTION
WHEN OTHERS THEN
RETURN p_cust_account_id;
END sfgetcustcode;
End pkgutil;
Step 2:
SQL Report Code:
SELECT
pkgutil.sfgetpartyname(x.sold_to_org_id) customer_name,
pkgutil.sfgetcustcode(x.sold_to_org_id) customer_code,
nvl(x.description, msi.description) description,
msi.segment1 item,
msi.primary_uom_code uom,
( x.unit_price ) unit_price,
nvl(pkgutil.fntranactualcost(line_id), cic.item_cost) unit_cost,
CASE
WHEN cust_trx_type_id = 12027 THEN — ID 12027 is Invoiced Id changes this according to your setup
nvl(x.sales_quantity, 0)
WHEN cust_trx_type_id = 12026 THEN — ID 12026 is Credit memo, changes this according to your setup
– 1 * nvl(x.return_quantity, 0)
ELSE
nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0)
END qty_sold,
CASE
WHEN cust_trx_type_id = 12027 THEN
round(nvl(x.sales_amount, 0), 2)
WHEN cust_trx_type_id = 12026 THEN
round(- 1 * nvl(x.return_amount, 0), 2)
ELSE
round(nvl(x.sales_amount, 0) – nvl(x.return_amount, 0), 2)
END revenue,
CASE
WHEN cust_trx_type_id = 12027 THEN
round(nvl(x.sales_quantity, 0) * nvl(pkgutil.fntranactualcost(line_id), cic.item_cost), 2)
WHEN cust_trx_type_id = 12026 THEN
– 1 * round(nvl(x.return_quantity, 0) * nvl(pkgutil.fntranactualcost(line_id), cic.item_cost), 2)
ELSE
round((nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0)) * nvl(pkgutil.fntranactualcost(line_id), cic.item_cost),
2)
END “Cost of Sales”,
CASE
WHEN ( ( ( x.sales_amount – nvl(x.return_amount, 0) >= 0 )
AND ( x.sales_amount – nvl(x.return_amount, 0) < 1 ) )
OR ( nvl(pkgutil.fntranactualcost(line_id), cic.item_cost) IS NULL
OR nvl(pkgutil.fntranactualcost(line_id), cic.item_cost) = 0 )
OR ( x.unit_price IS NULL
OR x.unit_price = 0 )
OR ( nvl(x.sales_quantity, 0) = 0 ) ) THEN
‘0’
WHEN cust_trx_type_id = 12026 THEN
( 100 * round(1 -((nvl(x.return_quantity, 0) * nvl(pkgutil.fntranactualcost(line_id), cic.item_cost)) / nvl(x.return_amount,
0)), 2) )
|| ‘%’
WHEN cust_trx_type_id = 12027 THEN
( 100 * ( 1 – ( ( nvl(x.sales_quantity, 0) * nvl(pkgutil.fntranactualcost(line_id), cic.item_cost) ) / nvl(x.sales_amount,
0) ) ) )
|| ‘%’
WHEN ( pkgutil.sfgetexchangerate(x.document) IS NULL
AND msi.segment1 IS NOT NULL ) THEN
( 100 * ( 1 – ( ( ( nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0) ) * nvl(pkgutil.fntranactualcost(line_id), cic.
item_cost) ) / ( x.sales_amount – nvl(x.return_amount, 0) ) ) ) )
|| ‘%’
ELSE
( 100 * ( 1 – ( ( ( nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0) ) * nvl(pkgutil.fntranactualcost(line_id), cic.
item_cost) ) / ( x.unit_price * nvl(pkgutil.sfgetexchangerate(x.document), 1) * ( nvl(x.sales_quantity, 0) – nvl(x.return_quantity,
0) ) ) ) ) )
|| ‘%’
END margin_percentage,
x.document,
( x.document_date ) document_date,
pkgutil.sfgetcurrate(x.document, ‘CUR’) doc_currency,
nvl(pkgutil.sfgetexchangerate(x.document), 1) exchange_rate,
( x.unit_price * nvl(pkgutil.sfgetexchangerate(x.document), 1) ) unitprice_loc,
— round(x.unit_price * nvl(pkgutil.sfgetexchangerate(x.document), 1) * nvl(x.sales_quantity, 0) – nvl(x.return_quantity, 0), 2)
CASE
WHEN cust_trx_type_id = 12027 THEN
round(nvl(pkgutil.sfgetexchangerate(x.document), 1) * nvl(x.sales_amount, 0), 2)
WHEN cust_trx_type_id = 12026 THEN
round(- 1 * nvl(x.return_amount, 0) * nvl(pkgutil.sfgetexchangerate(x.document), 1), 2)
ELSE
round(nvl(pkgutil.sfgetexchangerate(x.document), 1) *(nvl(x.sales_amount, 0) – nvl(x.return_amount, 0)), 2)
END revenue_loc,
order_number so_no
— cust_trx_type_id
FROM
(
SELECT DISTINCT
inventory_item_id,
description,
sold_to_org_id,
ship_from_org_id,
order_number,
trx_number document,
trx_date document_date,
unit_price,
SUM(shipped_quantity) sales_quantity,
SUM(return_quantity) return_quantity,
SUM(c_extended_amount) sales_amount,
SUM(r_extended_amount) return_amount,
line_id,
cust_trx_type_id
FROM
(
/*From this block we are fetching invoice and CM from OE tables- direct method*/
SELECT
oola.inventory_item_id,
NULL description,
ooha.sold_to_org_id,
oola.ship_from_org_id,
ooha.order_number order_number,
rt.trx_number trx_number,
oola.unit_selling_price unit_price,
rt.trx_date,
oola.shipped_quantity,
nvl(rsl.quantity_received, 0) return_quantity,
oola.shipped_quantity * oola.unit_selling_price c_extended_amount,
nvl(rsl.quantity_received, 0) * oola.unit_selling_price r_extended_amount,
oola.line_id,
rt.cust_trx_type_id
FROM
apps.oe_order_headers_all ooha
JOIN apps.oe_order_lines_all oola ON ( ooha.header_id = oola.header_id )
LEFT JOIN apps.rcv_shipment_lines rsl ON ( ooha.header_id = rsl.oe_order_header_id
AND rsl.oe_order_line_id = oola.line_id )
LEFT JOIN apps.rcv_shipment_headers rsh ON ( rsh.shipment_header_id = rsl.shipment_header_id
AND source_document_code = ‘RMA’ )
LEFT JOIN ra_customer_trx_all rt ON ( ( rt.interface_header_attribute1 ) = to_char(ooha.order_number) )
LEFT JOIN ra_customer_trx_lines_all rl ON ( rt.customer_trx_id = rl.customer_trx_id
AND oola.inventory_item_id = rl.inventory_item_id
AND rl.line_type = ‘LINE’ )
WHERE
1 = 1
AND ( oola.shipped_quantity IS NOT NULL
— OR rl.quantity_invoiced IS NOT NULL
)
AND oola.ship_from_org_id = :p36_inventory_org
AND ooha.order_number IS NOT NULL
— AND mta.primary_quantity > 0
AND trunc(rt.trx_date) BETWEEN to_date(:p36_from_date, ‘DD/MM/YYYY’) AND to_date(:p36_to_date, ‘DD/MM/YYYY’)
UNION
SELECT
inventory_item_id,
description,
sold_to_customer_id,
to_number(:p36_inventory_org),
order_number,
trx_number,
unit_selling_price,
trx_date,
SUM(quantity_ordered) quantity_ordered,
– 1 * SUM(quantity_credited) return_quantity,
SUM(c_extended_amount) c_extended_amount,
– 1 * SUM(r_extended_amount) r_extended_amount,
to_number(interface_line_attribute6),
cust_trx_type_id
FROM
(
/*From this block we are fetching Manual CM from Transaction table*/
SELECT
rt.sold_to_customer_id,
rt.org_id,
rl.inventory_item_id,
rl.description,
rt.trx_date,
NULL order_number,
rt.trx_number,
rl.unit_selling_price,
SUM(nvl(
CASE
WHEN rl.quantity_ordered < 0 THEN
– 1 * rl.quantity_ordered
ELSE
rl.quantity_ordered
END, 0)) quantity_ordered,
SUM(nvl(rl.quantity_credited, 0)) quantity_credited,
0 c_extended_amount,
SUM(nvl(rl.extended_amount, 0)) r_extended_amount,
to_number(rl.interface_line_attribute6) interface_line_attribute6,
rt.cust_trx_type_id
FROM
ra_customer_trx_all rt,
ra_customer_trx_lines_all rl,
ra_cust_trx_line_gl_dist_all rcg
WHERE
line_type = ‘LINE’
AND rt.customer_trx_id = rl.customer_trx_id
AND rt.trx_number IS NOT NULL
AND rt.interface_header_attribute1 IS NULL
AND rl.customer_trx_line_id = rcg.customer_trx_line_id
AND rt.cust_trx_type_id = 12026
AND rl.quantity_credited IS NOT NULL
AND rt.org_id = pkgutil.sfgetoperating_ut(:p36_inventory_org)
AND trunc(rt.trx_date) BETWEEN to_date(:p36_from_date, ‘DD/MM/YYYY’) AND to_date(:p36_to_date, ‘DD/MM/YYYY’)
GROUP BY
rt.sold_to_customer_id,
rt.org_id,
rl.inventory_item_id,
rl.description,
rt.trx_date,
rt.trx_number,
rl.unit_selling_price,
— rl.interface_line_attribute6
to_number(rl.interface_line_attribute6),
rt.cust_trx_type_id
UNION
/*From this block we are fetching Manual Invoice from Transaction table*/
SELECT
rt.sold_to_customer_id,
rt.org_id,
rl.inventory_item_id,
rl.description,
rt.trx_date,
NULL order_number,
rt.trx_number,
rl.unit_selling_price,
SUM(nvl(
CASE
WHEN rl.quantity_invoiced < 0 THEN
– 1 * rl.quantity_invoiced
ELSE
rl.quantity_invoiced
END, 0)) quantity_ordered,
0 quantity_credited,
SUM(nvl(rl.extended_amount, 0)) c_extended_amount,
0 r_extended_amount,
to_number(rl.interface_line_attribute6) interface_line_attribute6,
rt.cust_trx_type_id
FROM
ra_customer_trx_all rt,
ra_customer_trx_lines_all rl,
ra_cust_trx_line_gl_dist_all rcg
WHERE
line_type = ‘LINE’
AND rt.customer_trx_id = rl.customer_trx_id
AND rl.customer_trx_line_id = rcg.customer_trx_line_id
AND rt.interface_header_attribute1 IS NULL
AND rt.cust_trx_type_id = 12027
AND rt.trx_number IS NOT NULL
AND rl.quantity_invoiced IS NOT NULL
AND rt.org_id = pkgutil.sfgetoperating_ut(:p36_inventory_org) — Check org ID here
AND trunc(rt.trx_date) BETWEEN to_date(:p36_from_date, ‘DD/MM/YYYY’) AND to_date(:p36_to_date, ‘DD/MM/YYYY’)
GROUP BY
rt.sold_to_customer_id,
rt.org_id,
rl.inventory_item_id,
rl.description,
rt.trx_date,
rt.trx_number,
rl.unit_selling_price,
to_number(rl.interface_line_attribute6),
rt.cust_trx_type_id
)
GROUP BY
inventory_item_id,
description,
sold_to_customer_id,
trx_date,
order_number,
trx_number,
unit_selling_price,
( interface_line_attribute6 ),
cust_trx_type_id
)
WHERE
1 = 1
GROUP BY
inventory_item_id,
description,
sold_to_org_id,
ship_from_org_id,
order_number,
trx_number,
trx_date,
unit_price,
line_id,
cust_trx_type_id
) x,
mtl_system_items_b msi,
cst_item_costs cic
WHERE
1 = 1
AND x.inventory_item_id = msi.inventory_item_id (+)
AND x.ship_from_org_id = msi.organization_id (+)
AND x.inventory_item_id = cic.inventory_item_id (+)
AND x.ship_from_org_id = cic.organization_id (+)
ORDER BY
x.sold_to_org_id,
x.document,
x.inventory_item_id
5.Screen Shot
Output:
This will be the output that we can generated using this document.
