Scribd
Upload
3 views5 pages

Tariff Calculation Functions in PL/SQL

The document contains PL/SQL code defining three functions: Tariff_percent, Tariff_amt, and COO_US_QTY1, which calculate tariff percentages, amounts, and quantities based on order line IDs. Each function includes error handling to return zero or null in case of exceptions. The code is part of changes made by Praveen Kumar under the identifier GC-124527.

Uploaded by

nnwfzjy8qd
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views5 pages

Tariff Calculation Functions in PL/SQL

The document contains PL/SQL code defining three functions: Tariff_percent, Tariff_amt, and COO_US_QTY1, which calculate tariff percentages, amounts, and quantities based on order line IDs. Each function includes error handling to return zero or null in case of exceptions. The code is part of changes made by Praveen Kumar under the identifier GC-124527.

Uploaded by

nnwfzjy8qd
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

-- Start Changes made Praveen Kumar as part of GC-124527--

FUNCTION Tariff_percent (
p_line_id IN NUMBER
) RETURN NUMBER
IS
l_percent NUMBER := 0;
BEGIN
SELECT NVL(SUM(tariff_percentage), 0)
INTO l_percent
FROM (
SELECT tariff_percentage
FROM (
SELECT trk.TARIFF_ACCRUAL_RATE * 100 as tariff_percentage,
DENSE_RANK() OVER (
PARTITION BY src_doc_id
ORDER BY creation_date DESC, tariff_trk_id DESC
) AS rnk
FROM xxfin.xxfin_gbl_tariff_trk_dtl trk
WHERE calling_source = 'ORDER'
AND calling_source_context = 'ACTUAL'
AND display_flag = 'Y'
AND src_doc_id = p_line_id
)
WHERE rnk = 1
);

RETURN l_percent;

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN 0;
END Tariff_percent;

FUNCTION Tariff_amt (p_line_id IN NUMBER, p_in_accrual_price NUMBER)


RETURN NUMBER
IS
l_Tariff_amt NUMBER;
BEGIN
SELECT NVL (
SUM (
( dat.shipped_quantity
* p_in_accrual_price /*For China flow accrual price is the
transfer price and for non-Bonded scenario it will be PO price*/
* dat.tariff_percentage
/ 100)),
0)
INTO l_Tariff_amt
FROM (WITH
a1
AS
(SELECT *
FROM (SELECT DENSE_RANK ()
OVER (
PARTITION BY SRC_DOC_ID
ORDER BY
creation_date DESC,
tariff_trk_id DESC)
AS rnk,
trk.tariff_code,
trk.country_of_origin,
trk.attribute3,
trk.TARIFF_ACCRUAL_RATE * 100 as
tariff_percentage,
trk.display_flag,
trk.tariff_currency,
trk.src_doc_id,
trk.calling_source
FROM XXFIN.XXFIN_GBL_TARIFF_TRK_DTL trk
WHERE calling_source = 'ORDER'
AND calling_source_context = 'ACTUAL'
AND display_flag = 'Y'
AND trk.src_doc_id = p_line_id) trk1
WHERE [Link] = 1)
SELECT a1.attribute3 AS excluded_countries,
DECODE (
INSTR (NVL (a1.attribute3, '#'),
attr.COUNTRY_OF_ORIGIN),
0, 'No',
'Yes') AS excluded_due_to_country_exclusion,
SUM ([Link]) AS shipped_quantity,
a1.tariff_percentage,
a1.COUNTRY_OF_ORIGIN trk_coo
FROM apps.xxdis_gbl_pick_whs_lot_attr attr,
apps.oe_order_lines_all ool,
apps.mtl_parameters mp,
a1
WHERE ( attr.COUNTRY_OF_ORIGIN = a1.COUNTRY_OF_ORIGIN
OR a1.COUNTRY_OF_ORIGIN = 'ALL')
AND attr.order_line_id = ool.line_id
AND attr.order_header_id = ool.header_id
AND ool.line_id = a1.src_doc_id
AND mp.organization_id = ool.ship_from_org_id
AND mp.attribute6 = 'WMS'
GROUP BY a1.COUNTRY_OF_ORIGIN,
a1.attribute3,
attr.COUNTRY_OF_ORIGIN,
a1.tariff_percentage) dat
WHERE dat.excluded_due_to_country_exclusion = 'No';

IF NVL (l_Tariff_amt, 0) = 0
THEN
SELECT NVL (
SUM (
( dat.shipped_quantity
* p_in_accrual_price /*For China flow accrual price is the
transfer price and for non-Bonded scenario it will be PO price*/
* dat.tariff_percentage
/ 100)),
0)
INTO l_Tariff_amt
FROM (WITH
a1
AS
(SELECT *
FROM (SELECT DENSE_RANK ()
OVER (
PARTITION BY SRC_DOC_ID
ORDER BY
creation_date DESC,
tariff_trk_id DESC)
AS rnk,
trk.tariff_code,
trk.country_of_origin,
trk.attribute3,
trk.TARIFF_ACCRUAL_RATE * 100 as
tariff_percentage,
trk.display_flag,
trk.tariff_currency,
trk.src_doc_id,
trk.calling_source
FROM XXFIN.XXFIN_GBL_TARIFF_TRK_DTL trk
WHERE calling_source = 'ORDER'
AND calling_source_context = 'ACTUAL'
AND display_flag = 'Y'
AND trk.src_doc_id = p_line_id) trk1
WHERE [Link] = 1)
SELECT a1.attribute3
AS excluded_countries,
DECODE (
INSTR (NVL (a1.attribute3, '#'),
mmt2.attribute4),
0, 'No',
'Yes')
AS excluded_due_to_country_exclusion,
SUM (ABS (NVL (MMT2.transaction_quantity, 0)))
AS shipped_quantity,
a1.tariff_percentage,
a1.COUNTRY_OF_ORIGIN
trk_coo
FROM apps.oe_order_lines_all ool,
apps.mtl_parameters mp,
a1,
APPS.MTL_MATERIAL_TRANSACTIONS MMT2,
apps.wsh_delivery_details wdd,
apps.mtl_transaction_types mtt
WHERE ( mmt2.attribute4 = a1.COUNTRY_OF_ORIGIN
OR a1.COUNTRY_OF_ORIGIN = 'ALL')
AND mmt2.transaction_id = wdd.transaction_id
AND wdd.source_header_id = ool.header_id
AND wdd.source_line_id = ool.line_id
AND ool.line_id = a1.src_doc_id
AND mp.organization_id = ool.ship_from_org_id
AND mmt2.transaction_id = wdd.transaction_id
AND wdd.source_header_id = ool.header_id
AND wdd.source_line_id = ool.line_id
AND mmt2.transaction_type_id = mtt.transaction_type_id
AND mtt.TRANSACTION_TYPE_NAME = 'Sales Order Pick'
AND mp.attribute6 <> 'WMS'
GROUP BY a1.COUNTRY_OF_ORIGIN,
a1.attribute3,
mmt2.attribute4,
a1.tariff_percentage) dat
WHERE dat.excluded_due_to_country_exclusion = 'No';
END IF;
RETURN l_Tariff_amt;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END Tariff_amt;

FUNCTION COO_US_QTY1 (p_line_id IN NUMBER)


RETURN VARCHAR2
IS
--l_percent VARCHAR2;
v_COO_US_QTY VARCHAR2 (4000);
BEGIN
SELECT (NVL (
( SELECT LISTAGG (
TO_NUMBER (SUM (quantity))
|| ' ('
|| country_of_origin
|| ' )',
', ')
WITHIN GROUP (ORDER BY country_of_origin)
FROM xxdis_gbl_pick_whs_lot_attr attr,
apps.oe_order_lines_all ool,
apps.mtl_parameters mp --,
apps.xxdis_gbl_cost_and_markup xgcm
WHERE attr.order_line_id = ool.line_id
AND attr.order_header_id = ool.header_id
AND ool.line_id = p_line_id
AND EXISTS
(SELECT 1
FROM XXFIN.XXFIN_GBL_TARIFF_TRK_DTL trk
WHERE trk.calling_source = 'ORDER'
AND trk.calling_source_context = 'ACTUAL'
AND trk.display_flag = 'Y'
AND trk.src_doc_id = ool.line_id
AND ( trk.country_of_origin = 'ALL'
OR (trk.country_of_origin =
attr.country_of_origin)))
AND mp.attribute6 = 'WMS'
AND mp.organization_id = ool.ship_from_org_id
--and xgcm.so_line_id = ool.line_id

GROUP BY country_of_origin),
( SELECT LISTAGG (
TO_NUMBER (SUM (picked_qty))
|| '( '
|| country_of_origin
|| ') ',
', ')
WITHIN GROUP (ORDER BY country_of_origin)
FROM (SELECT ABS (NVL (mmt2.transaction_quantity, 0))
AS picked_qty,
mmt2.attribute4
AS country_of_origin
FROM apps.mtl_material_transactions mmt2,
apps.wsh_delivery_details wdd,
apps.mtl_transaction_types mtt,
apps.oe_order_lines_all ool,
apps.mtl_parameters mp --,
apps.xxdis_gbl_cost_and_markup xgcm
WHERE mmt2.transaction_id = wdd.transaction_id
AND wdd.source_header_id = ool.header_id
AND wdd.source_line_id = ool.line_id
AND ool.line_id = p_line_id
--and xgcm.so_line_id = ool.line_id

AND EXISTS
(SELECT 1
FROM XXFIN.XXFIN_GBL_TARIFF_TRK_DTL trk ,
xxdis_gbl_pick_whs_lot_attr attr
WHERE trk.calling_source = 'ORDER'
AND trk.calling_source_context =
'ACTUAL'
AND trk.display_flag = 'Y'
AND trk.src_doc_id = ool.line_id
AND ( trk.country_of_origin =
'ALL'
OR (trk.country_of_origin =
attr.country_of_origin)))
AND mmt2.transaction_type_id =
mtt.transaction_type_id
AND mp.organization_id = ool.ship_from_org_id
AND mtt.transaction_type_name =
'Sales Order Pick'
AND mp.attribute6 <> 'WMS')
GROUP BY country_of_origin)))
INTO v_COO_US_QTY
FROM DUAL;

RETURN v_COO_US_QTY;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END COO_US_QTY1;

-- End Changes made Praveen Kumar as part of GC-124527--

You might also like