PACKAGE xx_ont_credit_check
IS
PROCEDURE apply_checks(
p_sold_to_customer_id NUMBER,
p_header_id NUMBER,
p_order_amount NUMBER) ;
end;
PACKAGE BODY xx_ont_credit_check
IS
g_exec_seq NUMBER := 0;
g_exec_step NUMBER := 0;
g_trace_value NUMBER;
g_ndf CONSTANT NUMBER := -1;
TYPE tnumber
IS
TABLE OF NUMBER INDEX BY BINARY_INTEGER;
PROCEDURE add_trace(
p_token VARCHAR2,
p_token_description VARCHAR2)
IS
BEGIN
IF g_trace_value = 0 THEN
RETURN;
END IF;
IF g_exec_seq = 0 THEN
g_exec_seq := xx_trace_seq.NEXTVAL;
END IF;
g_exec_step := g_exec_step + 1;
INSERT
INTO
xx_cust_trace VALUES
(
g_exec_seq ,
g_exec_step ,
p_token ,
p_token_description ,
SYSDATE ,
fnd_global.user_id
);
--commit;
END;
FUNCTION apply_hold
(
p_header_id NUMBER,
p_hold_comment VARCHAR2
)
RETURN VARCHAR2
IS
l_msg_data VARCHAR2 (256);
l_msg_count NUMBER;
l_msg_index NUMBER;
l_data VARCHAR2 (2000);
l_hold_source_rec oe_holds_pvt.hold_source_rec_type;
l_hold_id NUMBER (10);
l_return_status VARCHAR2 (30);
BEGIN
--oe_debug_pub.initialize;
--oe_debug_pub.setdebuglevel (5);
--oe_msg_pub.initialize;
/*SELECT hold_id
INTO l_hold_id
FROM oe_hold_definitions
WHERE NAME = 'Credit Check Failure';*/
l_hold_id := 1001;
l_hold_source_rec := oe_holds_pvt.g_miss_hold_source_rec;
l_hold_source_rec.hold_id := l_hold_id; -- hold_id
l_hold_source_rec.hold_entity_code := 'O'; -- order level hold
l_hold_source_rec.hold_entity_id := p_header_id; --header_id of the order
l_hold_source_rec.header_id := p_header_id; --header_id of the order
l_hold_source_rec.hold_comment := p_hold_comment;
--
add_trace ('MESG', 'Just before calling OE_Holds_PUB.Apply_Holds:');
--
oe_holds_pub.apply_holds (p_api_version => 1.0 , p_commit => fnd_api.g_true ,
p_validation_level => fnd_api.g_valid_level_none , p_hold_source_rec =>
l_hold_source_rec ,
--p_hold_existing_flg => 'Y',
--p_hold_future_flg => 'Y',
x_return_status => l_return_status , x_msg_count => l_msg_count , x_msg_data
=> l_msg_data);
--
add_trace ('MESG', 'Just after calling OE_Holds_PUB.Apply_Holds:');
--
-- Check Return Status
IF l_return_status = fnd_api.g_ret_sts_success THEN
add_trace ('MESG', 'Hold Applied= SUCCESS');
COMMIT;
RETURN 'S';
ELSE
add_trace ('MESG', 'Hold Applied= FAILED');
add_trace ('MESG', 'l_msg_data');
--ROLLBACK;
RETURN 'F';
END IF;
END;
FUNCTION get_first_order
(
p_ordered_date DATE,
p_cust_list tnumber
)
RETURN NUMBER
IS
l_first_order NUMBER;
l_min_ordered_date DATE := TO_DATE ('31-Dec-4712', 'DD-Mon-RRRR');
l_ordered_date DATE := TO_DATE ('31-Dec-4712', 'DD-Mon-RRRR');
l_customer_id NUMBER;
BEGIN
add_trace ('MESG', 'Stepped in get_first_order()');
FOR i IN 1 .. p_cust_list.COUNT
LOOP
--
add_trace
(
'tab(' || i || ')=', p_cust_list (i)
)
;
--
SELECT
MIN (oha.ordered_date)
INTO
l_ordered_date
FROM
oe_order_headers_all oha,
oe_transaction_types_tl t,
oe_transaction_types_all o
WHERE
t.transaction_type_id = o.transaction_type_id
AND oha.order_type_id = o.transaction_type_id
AND NAME NOT LIKE '%Free_Claims'
AND ordered_date >= TO_DATE (TO_CHAR (p_ordered_date, 'Mon-YYYY'),
'Mon-YYYY')
AND sold_to_org_id = p_cust_list (i);
add_trace ('l_ordered_date =', TO_CHAR (l_ordered_date,
'DD-Mon-YYYY HH24:MM:SS'));
IF l_ordered_date < l_min_ordered_date THEN
l_min_ordered_date := l_ordered_date;
l_customer_id := p_cust_list (i);
END IF;
END LOOP;
add_trace ('l_min_ordered_date =', TO_CHAR (l_min_ordered_date,
'DD-Mon-YYYY HH24:MM:SS'));
-- condition added to improve performance
IF l_min_ordered_date <> TO_DATE ('31-Dec-4712', 'DD-Mon-RRRR') THEN
SELECT
MIN (header_id)
INTO
l_first_order
FROM
oe_order_headers_all
WHERE
ordered_date = l_min_ordered_date
AND sold_to_org_id = l_customer_id;
END IF;
IF l_first_order IS NULL THEN
l_first_order := g_ndf;
END IF;
--
add_trace ('MESG', 'Return Value=' || l_first_order);
add_trace ('MESG', 'Stepping out get_first_order()');
--
RETURN l_first_order;
END;
FUNCTION get_credit_balance(
p_cust_list tnumber)
RETURN NUMBER
IS
l_ledger_amount_due NUMBER := 0;
l_ifaced_amount_due NUMBER := 0;
l_non_ifaced_amt_due NUMBER := 0;
l_total_amt_due NUMBER := 0;
BEGIN
add_trace ('MESG', 'Stepped in get_credit_balance()');
FOR i IN 1 .. p_cust_list.COUNT
LOOP
add_trace ('tab(' || i || ')=', p_cust_list (i));
--
-- a negative value for amount due shows deposits / credit notes as they
-- cause the customer balance to decrease
--
SELECT
NVL (SUM (amount_due_remaining), 0)
INTO
l_ledger_amount_due
FROM
ar.ar_payment_schedules_all
WHERE
customer_id = p_cust_list (i);
--
-- report due amount from all operating units against the subject customer
--
SELECT
NVL (SUM (amount), 0)
INTO
l_ifaced_amount_due
FROM
ra_interface_lines_all ila
WHERE
ila.orig_system_sold_customer_id = p_cust_list (i)
AND ila.sales_order_source = 'ORDER ENTRY'
AND line_type = 'LINE';
--
-- report due amount from all operating units against the subject customer
--
SELECT
NVL (SUM (unit_selling_price * ordered_quantity), 0)
INTO
l_non_ifaced_amt_due
FROM
oe_order_lines_all
WHERE
sold_to_org_id = p_cust_list (i)
AND flow_status_code = 'AWAITING_SHIPPING';
l_total_amt_due := l_total_amt_due + l_ledger_amount_due +
l_ifaced_amount_due + l_non_ifaced_amt_due;
--
add_trace ('l_ledger_amount_due =', l_ledger_amount_due);
add_trace ('l_ifaced_amount_due =', l_ifaced_amount_due);
add_trace ('l_non_ifaced_amt_due =', l_non_ifaced_amt_due);
add_trace ('l_total_amt_due =', l_total_amt_due);
--
END LOOP;
add_trace ('MESG', 'Return Value=' || l_total_amt_due);
add_trace ('MESG', 'Stepping out get_credit_balance()');
RETURN l_total_amt_due;
END;
FUNCTION credit_check(
p_customer_id NUMBER,
p_amount_due NUMBER,
p_cmpr_type VARCHAR2)
RETURN VARCHAR2
IS
l_sbsd_customer_id NUMBER; -- subsidary customer
l_parent_customer_id NUMBER;
l_min_credit_limit NUMBER;
l_max_credit_limit NUMBER;
l_ret_status VARCHAR2 (1);
l_sold_to_customer_id NUMBER; -- represent customer on which limit is defined
BEGIN
add_trace ('MESG', 'Stepping in credit_check()');
add_trace ('p_customer_id = ', p_customer_id);
BEGIN
SELECT
min_credit_limit , --minimum_credit_limit,
max_credit_limit
INTO
l_min_credit_limit ,
l_max_credit_limit
FROM
ar.hz_cust_accounts hca,
apps.hz_cust_accounts_dfv hcav
WHERE
hca.ROWID = hcav.row_id
AND hca.cust_account_id = p_customer_id
AND hcav.CONTEXT IN ('Credit Limits for Child',
'Credit Limits for Parent');
EXCEPTION
WHEN NO_DATA_FOUND THEN
add_trace ('MESG', 'Stepped in Exception Block');
add_trace ('MESG', 'Return Value = ''S''');
RETURN 'S';
END;
add_trace ('l_min_credit_limit=', l_min_credit_limit);
add_trace ('l_max_credit_limit=', l_max_credit_limit);
IF p_cmpr_type = 'MIN' THEN
add_trace ('MESG', 'Checking MIN Limit');
IF (l_min_credit_limit IS NULL) OR
(
p_amount_due < l_min_credit_limit
)
THEN
l_ret_status := 'S';
ELSE
l_ret_status := 'F';
END IF;
ELSIF p_cmpr_type = 'MAX' THEN
add_trace ('MESG', 'Checking MAX Limit');
IF (l_max_credit_limit IS NULL) OR
(
p_amount_due < l_max_credit_limit
)
THEN
l_ret_status := 'S';
ELSE
l_ret_status := 'F';
END IF;
END IF;
add_trace ('MESG', 'Return Value=' || l_ret_status);
add_trace ('MESG', 'Stepping out credit_check() =' || l_ret_status);
RETURN l_ret_status;
END;
FUNCTION get_related_customers(
p_sold_to_customer_id NUMBER)
RETURN tnumber
IS
tab tnumber;
l_parent_customer_id NUMBER;
-- query to check if subject customer is of parent relation
CURSOR c1
IS
SELECT
hca.cust_account_id
FROM
hz_cust_accounts hca
WHERE
hca.cust_account_id = p_sold_to_customer_id
AND hca.attribute_category = 'Credit Limits for Parent';
-- query to retrieve all child customers of the parent
CURSOR c2 (p_customer_id NUMBER)
IS
SELECT
hca.cust_account_id
FROM
ar.hz_cust_accounts hca,
apps.hz_cust_accounts_dfv hcav
WHERE
hca.ROWID = hcav.row_id
AND hcav.CONTEXT = 'Credit Limits for Child'
AND hcav.parent_customer = p_customer_id;
-- query to retrieve parent customer for subject customer
CURSOR c3
IS
SELECT
hcav.parent_customer
FROM
ar.hz_cust_accounts hca,
apps.hz_cust_accounts_dfv hcav
WHERE
hca.ROWID = hcav.row_id
AND hcav.CONTEXT = 'Credit Limits for Child'
AND hca.cust_account_id = p_sold_to_customer_id;
BEGIN
add_trace ('MSG', 'Stepped in get_related_customers()');
OPEN c1;
FETCH
c1
INTO
l_parent_customer_id;
CLOSE c1;
-- if customer is of parent relation
IF l_parent_customer_id IS NOT NULL THEN
--
-- retrieve child customers
--
tab (1) := l_parent_customer_id;
DECLARE
i NUMBER := 2;
BEGIN
FOR r2 IN c2 (l_parent_customer_id)
LOOP
tab (i) := r2.cust_account_id;
i := i + 1;
END LOOP;
END;
-- if cust is of child relation
ELSIF l_parent_customer_id IS NULL THEN
-- retrieve child's parent
OPEN c3;
FETCH
c3
INTO
l_parent_customer_id;
CLOSE c3;
IF l_parent_customer_id IS NOT NULL THEN
--
-- retrieve child customers
--
tab (1) := l_parent_customer_id;
DECLARE
i NUMBER := 2;
BEGIN
FOR r2 IN c2 (l_parent_customer_id)
LOOP
tab (i) := r2.cust_account_id;
i := i + 1;
END LOOP;
END;
END IF;
END IF;
--
-- still if no parent is found then treat subject customer as parent customer
--
IF l_parent_customer_id IS NULL THEN
tab (1) := p_sold_to_customer_id;
END IF;
FOR i IN 1 .. tab.COUNT
LOOP
add_trace ('tab(' || i || ')=', tab (i));
END LOOP;
add_trace ('MSG', 'Stepping out get_related_customers()');
RETURN tab;
END;
PROCEDURE get_hold_attributes(
p_order_id NUMBER,
x_hold_id OUT NUMBER,
x_release_id OUT NUMBER)
IS
BEGIN
add_trace ('MESG', 'Stepping in Hold Exists');
SELECT
MAX (order_hold_id) ,
MAX (hold_release_id)
INTO
x_hold_id ,
x_release_id
FROM
oe_order_holds
WHERE
header_id = p_order_id;
add_trace ('MESG', 'x_hold_id = ' || x_hold_id);
add_trace ('MESG', 'x_release_id = ' || x_release_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
add_trace ('MESG', 'Stepped in Exception Block');
add_trace ('MESG', 'x_hold_id = ' || x_hold_id);
add_trace ('MESG', 'x_release_id = ' || x_release_id);
END;
PROCEDURE apply_checks(
p_sold_to_customer_id NUMBER,
p_header_id NUMBER,
p_order_amount NUMBER)
IS
l_credit_check VARCHAR2 (1);
l_amount_due NUMBER;
l_amt_due_inclusive NUMBER;
l_trace_value NUMBER := 0;
ret VARCHAR2 (1);
--
-- current order attributes
--
l_order_id NUMBER;
l_release_id NUMBER;
l_hold_id NUMBER;
--
-- first order attributes
--
l_first_order_id NUMBER;
l_fo_release_id NUMBER;
l_fo_hold_id NUMBER;
l_cust_list tnumber;
l_ordered_date DATE;
BEGIN
--
-- Reset Trace Execution Sequence
--
g_trace_value := 1; -- 0 means OFF, 1 means ON
g_exec_seq := 0;
g_exec_step := 0;
--
add_trace ('MESG', '****Printing Parameter Values****');
add_trace ('p_order_amount', p_order_amount);
add_trace ('p_header_id', p_header_id);
add_trace ('p_sold_to_customer_id', p_sold_to_customer_id);
--
-- Source Environment for PL/SQL Call
--fnd_global.apps_initialize (1131, 50624, 660);
--mo_global.init ('S', '85');
--
-- initialize variables
--
SELECT
ordered_date
INTO
l_ordered_date
FROM
oe_order_headers_all
WHERE
header_id = p_header_id;
l_order_id := p_header_id;
l_cust_list := get_related_customers (p_sold_to_customer_id);
l_amount_due := get_credit_balance (l_cust_list);
l_amt_due_inclusive := l_amount_due + p_order_amount;
l_first_order_id := get_first_order (l_ordered_date, l_cust_list);
--
add_trace ('MSG', 'Getting hold attributes for current order');
get_hold_attributes (l_order_id, l_hold_id, l_release_id);
--
add_trace ('MSG', 'Getting hold attributes for first order');
--
get_hold_attributes (l_first_order_id, l_fo_hold_id, l_fo_release_id);
--
add_trace ('l_order_id', l_order_id);
add_trace ('l_first_order_id', l_first_order_id);
add_trace ('l_amount_due', l_amount_due);
--
-- if no first order found then consider current order as the first order of
-- the month
IF l_first_order_id = g_ndf THEN
l_first_order_id := l_order_id;
END IF;
add_trace ('MESG', 'Stepping in Min Credit Limit Block');
--
-- validation logic for MIN Credit Limit
--
BEGIN
IF l_order_id = l_first_order_id THEN
add_trace ('MESG', 'Stepped in First Order Condition');
--
-- system will apply hold only once.
--
l_credit_check := credit_check (l_cust_list (1), l_amount_due, 'MIN');
IF l_credit_check = 'F' AND l_hold_id IS NULL THEN
ret := apply_hold (l_order_id,
'Minimum Credit Limit Violated');
END IF;
ELSIF NOT
(
l_order_id = l_first_order_id
)
THEN
--
add_trace ('MESG', 'Stepped in NOT First Order Condition');
--
-- system will apply hold only once.
--
add_trace ('l_fo_hold_id=', l_fo_hold_id);
add_trace ('l_fo_release_id=', l_fo_release_id);
add_trace ('l_hold_id=', l_hold_id);
IF (l_fo_hold_id IS NOT NULL AND l_fo_release_id IS NULL) AND
(
l_hold_id IS NULL
)
THEN
ret := apply_hold (l_order_id, 'Minimum Credit Limit Violated');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
add_trace ('MESG', 'Un-expected Error while validating MIN Credit Limit');
RAISE;
END;
--
-- validation logic for MAX Credit Limit
--
add_trace ('MESG', 'Stepping in MAX Credit Limit Block');
BEGIN
--
-- if hold already applied during min credit check limit then skip
--
IF (ret IS NULL OR ret = 'F') AND
(
l_hold_id IS NULL
)
THEN
l_credit_check := credit_check (l_cust_list (1), l_amt_due_inclusive,
'MAX');
IF l_credit_check = 'F' THEN
ret := apply_hold (l_order_id, 'Max Credit Limit Violated');
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
add_trace ('MESG', 'Un-expected Error while validating MAX Credit Limit');
RAISE;
END;
COMMIT;
END;
--
-- automatic procedure
--
BEGIN
NULL;
END;
No comments:
Post a Comment