Release Hold API:
CREATE OR REPLACE PROCEDURE XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
vreturnstatus VARCHAR2(2);
vmsgcount NUMBER;
vmsgdata VARCHAR2(200);
l_order_tbl oe_holds_pvt.order_tbl_type;
i NUMBER;
BEGIN
l_order_tbl(1).header_id := P_HEADER_ID;
l_order_tbl(1).line_id := P_LINE_ID;
-- DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');
apps.oe_holds_pub.Release_Holds (
p_api_version =>1.0,
p_init_msg_list =>apps.fnd_api.g_false,
p_commit =>apps.fnd_api.g_false,
p_validation_level =>apps.fnd_api.g_valid_level_full,
p_order_tbl =>l_order_tbl,
p_hold_id => 1,
p_release_reason_code=> 'EXPIRE',
p_release_comment => 'TESTING' ,
x_return_status => vreturnstatus,
x_msg_count => vmsgcount,
x_msg_data => vmsgdata);
dbms_output.put_line (vreturnstatus);
FOR j IN 1..OE_MSG_PUB.count_msg
LOOP
OE_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => vmsgdata
, p_msg_index_out => i
);
dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
END LOOP;
END XXDHL_RELEASE_HOLD_PRC ;
CREATE OR REPLACE PACKAGE BODY XXDHL_RELEASE_HOLD_PKG AS
PROCEDURE XXDHL_RELEASE_HOLD_PRC( P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
vreturnstatus VARCHAR2(2);
vmsgcount NUMBER;
vmsgdata VARCHAR2(200);
l_order_tbl oe_holds_pvt.order_tbl_type;
i NUMBER;
BEGIN
l_order_tbl(1).header_id := P_HEADER_ID;
l_order_tbl(1).line_id := P_LINE_ID;
-- DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');
apps.oe_holds_pub.Release_Holds (
p_api_version =>1.0,
p_init_msg_list =>apps.fnd_api.g_false,
p_commit =>apps.fnd_api.g_false,
p_validation_level =>apps.fnd_api.g_valid_level_full,
p_order_tbl =>l_order_tbl,
p_hold_id => 1,
p_release_reason_code=> 'EXPIRE',
p_release_comment => 'TESTING' ,
x_return_status => vreturnstatus,
x_msg_count => vmsgcount,
x_msg_data => vmsgdata);
dbms_output.put_line (vreturnstatus);
FOR j IN 1..OE_MSG_PUB.count_msg
LOOP
OE_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => vmsgdata
, p_msg_index_out => i
);
dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
END LOOP;
END XXDHL_RELEASE_HOLD_PRC ;
FUNCTION xxdhl_credit_limit_order_prc (p_cust_id NUMBER,P_header_id NUMBER)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_api_return_status VARCHAR2 (1);
vholdid NUMBER;
vreturnstatus VARCHAR2(200);
vmsgcount NUMBER;
vmsgdata VARCHAR2(2000);
presultout VARCHAR2(2000);
preturnstatus VARCHAR2(100);
l_order_tbl oe_holds_pvt.order_tbl_type;
l_include_tax_flag VARCHAR2(1) := 'Y';
l_order_value NUMBER;
l_order_tax_value NUMBER;
l_credit_limit NUMBER;
l_total_amount NUMBER;
l_return VARCHAR2 (1);
l_hold_cnt NUMBER;
l_FLOW_STATUS_CODE VARCHAR2(40);
l_order_total NUMBER;
l_last_update_date VARCHAR2(100);
BEGIN
l_return := 'N';
SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';
/* select credit limit for the particular bill to customer id */
SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
FROM oe_order_lines_v ool
WHERE 1=1
AND ool.header_id =p_header_id);
/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;
/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL rct
WHERE 1=1
AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');
/* select line total for the particular line */
SELECT SUM(NVL(unit_selling_price,0)
* NVL(ordered_quantity,0))
INTO l_order_value
FROM OE_ORDER_LINES_ALL
WHERE 1=1
AND HEADER_ID = p_header_id;
/* select tax total for the particular line*/
SELECT SUM(NVL(tax_amount,0))
INTO l_order_tax_value
FROM JA_IN_SO_TAX_LINES
WHERE 1=1
AND HEADER_ID = p_header_id;
l_order_value :=l_order_value+l_order_tax_value;
l_total_amount :=l_total_amount+l_order_value+l_order_total;
/* If total amount is greater than credit limit then return Y*/
IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
THEN
l_return := 'Y';
ELSE
l_return := 'N';
END IF;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN l_return;
END;
END;
CREATE OR REPLACE PACKAGE XXDHL_RELEASE_HOLD_PKG AS
FUNCTION XXDHL_CREDIT_LIMIT_ORDER (P_CUST_ID NUMBER,P_HEADER_ID NUMBER);
PROCEDURE XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XXDHL_RELEASE_HOLD_PKG;
FUNCTION xxdhl_credit_limit_order (p_cust_id NUMBER,P_header_id NUMBER)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_api_return_status VARCHAR2 (1);
vholdid NUMBER;
vreturnstatus VARCHAR2(200);
vmsgcount NUMBER;
vmsgdata VARCHAR2(2000);
presultout VARCHAR2(2000);
preturnstatus VARCHAR2(100);
l_order_tbl oe_holds_pvt.order_tbl_type;
l_include_tax_flag VARCHAR2(1) := 'Y';
l_order_value NUMBER;
l_order_tax_value NUMBER;
l_credit_limit NUMBER;
l_total_amount NUMBER;
l_return VARCHAR2 (1);
l_hold_cnt NUMBER;
l_FLOW_STATUS_CODE VARCHAR2(40);
l_order_total NUMBER;
l_last_update_date VARCHAR2(100);
BEGIN
l_return := 'N';
SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';
/* select credit limit for the particular bill to customer id */
SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
FROM oe_order_lines_v ool
WHERE 1=1
AND ool.header_id =p_header_id);
/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;
/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL rct
WHERE 1=1
AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');
/* select line total for the particular line */
SELECT SUM(NVL(unit_selling_price,0)
* NVL(ordered_quantity,0))
INTO l_order_value
FROM OE_ORDER_LINES_ALL
WHERE 1=1
AND HEADER_ID = p_header_id;
/* select tax total for the particular line*/
SELECT SUM(NVL(tax_amount,0))
INTO l_order_tax_value
FROM JA_IN_SO_TAX_LINES
WHERE 1=1
AND HEADER_ID = p_header_id;
l_order_value :=l_order_value+l_order_tax_value;
l_total_amount :=l_total_amount+l_order_value+l_order_total;
/* If total amount is greater than credit limit then return Y*/
IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
THEN
l_return := 'Y';
ELSE
l_return := 'N';
END IF;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN l_return;
END;
/
CREATE OR REPLACE PACKAGE XXDHL_RELEASE_HOLD_PKG AS
FUNCTION XXDHL_CREDIT_LIMIT_ORDER_PRC (P_CUST_ID NUMBER,P_HEADER_ID NUMBER) RETURN VARCHAR2;
PROCEDURE XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XXDHL_RELEASE_HOLD_PKG;
/
CREATE OR REPLACE PROCEDURE XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
vreturnstatus VARCHAR2(2);
vmsgcount NUMBER;
vmsgdata VARCHAR2(200);
l_order_tbl oe_holds_pvt.order_tbl_type;
i NUMBER;
BEGIN
l_order_tbl(1).header_id := P_HEADER_ID;
l_order_tbl(1).line_id := P_LINE_ID;
-- DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');
apps.oe_holds_pub.Release_Holds (
p_api_version =>1.0,
p_init_msg_list =>apps.fnd_api.g_false,
p_commit =>apps.fnd_api.g_false,
p_validation_level =>apps.fnd_api.g_valid_level_full,
p_order_tbl =>l_order_tbl,
p_hold_id => 1,
p_release_reason_code=> 'EXPIRE',
p_release_comment => 'TESTING' ,
x_return_status => vreturnstatus,
x_msg_count => vmsgcount,
x_msg_data => vmsgdata);
dbms_output.put_line (vreturnstatus);
FOR j IN 1..OE_MSG_PUB.count_msg
LOOP
OE_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => vmsgdata
, p_msg_index_out => i
);
dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
END LOOP;
END XXDHL_RELEASE_HOLD_PRC ;
CREATE OR REPLACE PACKAGE BODY XXDHL_RELEASE_HOLD_PKG AS
PROCEDURE XXDHL_RELEASE_HOLD_PRC( P_HEADER_ID NUMBER , P_LINE_ID NUMBER) AS
vreturnstatus VARCHAR2(2);
vmsgcount NUMBER;
vmsgdata VARCHAR2(200);
l_order_tbl oe_holds_pvt.order_tbl_type;
i NUMBER;
BEGIN
l_order_tbl(1).header_id := P_HEADER_ID;
l_order_tbl(1).line_id := P_LINE_ID;
-- DBMS_APPLICATION_INFO.SET_CLIENT_INFO('534');
apps.oe_holds_pub.Release_Holds (
p_api_version =>1.0,
p_init_msg_list =>apps.fnd_api.g_false,
p_commit =>apps.fnd_api.g_false,
p_validation_level =>apps.fnd_api.g_valid_level_full,
p_order_tbl =>l_order_tbl,
p_hold_id => 1,
p_release_reason_code=> 'EXPIRE',
p_release_comment => 'TESTING' ,
x_return_status => vreturnstatus,
x_msg_count => vmsgcount,
x_msg_data => vmsgdata);
dbms_output.put_line (vreturnstatus);
FOR j IN 1..OE_MSG_PUB.count_msg
LOOP
OE_MSG_PUB.get
( p_msg_index => j
, p_encoded => 'F'
, p_data => vmsgdata
, p_msg_index_out => i
);
dbms_output.put_line('Error: ' || j || ':' || vmsgdata);
END LOOP;
END XXDHL_RELEASE_HOLD_PRC ;
FUNCTION xxdhl_credit_limit_order_prc (p_cust_id NUMBER,P_header_id NUMBER)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_api_return_status VARCHAR2 (1);
vholdid NUMBER;
vreturnstatus VARCHAR2(200);
vmsgcount NUMBER;
vmsgdata VARCHAR2(2000);
presultout VARCHAR2(2000);
preturnstatus VARCHAR2(100);
l_order_tbl oe_holds_pvt.order_tbl_type;
l_include_tax_flag VARCHAR2(1) := 'Y';
l_order_value NUMBER;
l_order_tax_value NUMBER;
l_credit_limit NUMBER;
l_total_amount NUMBER;
l_return VARCHAR2 (1);
l_hold_cnt NUMBER;
l_FLOW_STATUS_CODE VARCHAR2(40);
l_order_total NUMBER;
l_last_update_date VARCHAR2(100);
BEGIN
l_return := 'N';
SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';
/* select credit limit for the particular bill to customer id */
SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
FROM oe_order_lines_v ool
WHERE 1=1
AND ool.header_id =p_header_id);
/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;
/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL rct
WHERE 1=1
AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');
/* select line total for the particular line */
SELECT SUM(NVL(unit_selling_price,0)
* NVL(ordered_quantity,0))
INTO l_order_value
FROM OE_ORDER_LINES_ALL
WHERE 1=1
AND HEADER_ID = p_header_id;
/* select tax total for the particular line*/
SELECT SUM(NVL(tax_amount,0))
INTO l_order_tax_value
FROM JA_IN_SO_TAX_LINES
WHERE 1=1
AND HEADER_ID = p_header_id;
l_order_value :=l_order_value+l_order_tax_value;
l_total_amount :=l_total_amount+l_order_value+l_order_total;
/* If total amount is greater than credit limit then return Y*/
IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
THEN
l_return := 'Y';
ELSE
l_return := 'N';
END IF;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN l_return;
END;
END;
CREATE OR REPLACE PACKAGE XXDHL_RELEASE_HOLD_PKG AS
FUNCTION XXDHL_CREDIT_LIMIT_ORDER (P_CUST_ID NUMBER,P_HEADER_ID NUMBER);
PROCEDURE XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XXDHL_RELEASE_HOLD_PKG;
FUNCTION xxdhl_credit_limit_order (p_cust_id NUMBER,P_header_id NUMBER)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
v_api_return_status VARCHAR2 (1);
vholdid NUMBER;
vreturnstatus VARCHAR2(200);
vmsgcount NUMBER;
vmsgdata VARCHAR2(2000);
presultout VARCHAR2(2000);
preturnstatus VARCHAR2(100);
l_order_tbl oe_holds_pvt.order_tbl_type;
l_include_tax_flag VARCHAR2(1) := 'Y';
l_order_value NUMBER;
l_order_tax_value NUMBER;
l_credit_limit NUMBER;
l_total_amount NUMBER;
l_return VARCHAR2 (1);
l_hold_cnt NUMBER;
l_FLOW_STATUS_CODE VARCHAR2(40);
l_order_total NUMBER;
l_last_update_date VARCHAR2(100);
BEGIN
l_return := 'N';
SELECT COUNT(*)
INTO l_hold_cnt
FROM oe_order_holds_all ooh,OE_HOLD_SOURCES_ALL ohs,oe_hold_definitions ohd
WHERE 1=1
AND ooh.HOLD_SOURCE_ID = ohs.HOLD_SOURCE_ID
AND ohs.hold_id = ohd.hold_id
AND ooh.header_id =p_header_id
AND ohd.name ='Credit Check Failure';
--and ooh.RELEASED_FLAG ='N';
/* select credit limit for the particular bill to customer id */
SELECT NVL(MIN(hcp.OVERALL_CREDIT_LIMIT),0)
INTO l_credit_limit
FROM HZ_CUST_PROFILE_AMTS hcp
WHERE 1=1
AND hcp.OVERALL_CREDIT_LIMIT IS NOT NULL
AND hcp.CUST_ACCOUNT_ID =p_cust_id
AND hcp.SITE_USE_ID IN (SELECT ool.invoice_to_org_id
FROM oe_order_lines_v ool
WHERE 1=1
AND ool.header_id =p_header_id);
/* select status for the particular order in booked status */
SELECT DISTINCT ooha.FLOW_STATUS_CODE
INTO l_FLOW_STATUS_CODE
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select last_update_date for the particular order in booked status */
SELECT DISTINCT TO_CHAR(ooha.last_update_date,'mmddyyyyHH24MI')
INTO l_last_update_date
FROM oe_order_headers_all ooha
WHERE 1=1
AND ooha.header_id = p_header_id
AND ooha.FLOW_STATUS_CODE ='BOOKED';
/* select amount due for the particular bill to customer id */
SELECT NVL(SUM(amount_due_remaining),0)
INTO l_total_amount
FROM ar_payment_schedules_all
WHERE 1=1
AND CLASS ='INV'
AND status='OP'
AND AMOUNT_DUE_REMAINING > 0
AND customer_id = p_cust_id;
/* select line total + tax for the particular bill to customer id where line is closed or awaiting shipping*/
SELECT NVL(SUM(NVL(oola.UNIT_SELLING_PRICE,0)*NVL(ORDERED_QUANTITY,0)+NVL(jst.tax_amount,0)),0)
INTO l_order_total
FROM oe_order_headers_all ooha,oe_order_lines_all oola,JA_IN_SO_TAX_LINES jst
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.org_id = oola.org_id
AND ooha.header_id = jst.header_id
AND oola.line_id = jst.line_id
AND ooha.header_id != p_header_id
AND ooha.SOLD_TO_ORG_ID =p_cust_id
AND TO_CHAR(ooha.order_number) NOT IN
( SELECT rct.INTERFACE_HEADER_ATTRIBUTE1
FROM RA_CUSTOMER_TRX_ALL rct
WHERE 1=1
AND bill_to_customer_id =p_cust_id)
AND (oola.FLOW_STATUS_CODE ='AWAITING_SHIPPING' OR oola.FLOW_STATUS_CODE ='CLOSED');
/* select line total for the particular line */
SELECT SUM(NVL(unit_selling_price,0)
* NVL(ordered_quantity,0))
INTO l_order_value
FROM OE_ORDER_LINES_ALL
WHERE 1=1
AND HEADER_ID = p_header_id;
/* select tax total for the particular line*/
SELECT SUM(NVL(tax_amount,0))
INTO l_order_tax_value
FROM JA_IN_SO_TAX_LINES
WHERE 1=1
AND HEADER_ID = p_header_id;
l_order_value :=l_order_value+l_order_tax_value;
l_total_amount :=l_total_amount+l_order_value+l_order_total;
/* If total amount is greater than credit limit then return Y*/
IF l_total_amount >l_credit_limit + 1000 AND l_hold_cnt = 0 AND l_FLOW_STATUS_CODE ='BOOKED' --1000 Rs amount added by Vipul due to CR comes PF CSC_NOV_03.doc.
AND l_last_update_date >= TO_CHAR(SYSDATE,'mmddyyyyHH24')||LPAD(SUBSTR(TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI'),-2)-5,2,0)
THEN
l_return := 'Y';
ELSE
l_return := 'N';
END IF;
RETURN l_return;
EXCEPTION
WHEN OTHERS THEN
RETURN l_return;
END;
/
CREATE OR REPLACE PACKAGE XXDHL_RELEASE_HOLD_PKG AS
FUNCTION XXDHL_CREDIT_LIMIT_ORDER_PRC (P_CUST_ID NUMBER,P_HEADER_ID NUMBER) RETURN VARCHAR2;
PROCEDURE XXDHL_RELEASE_HOLD_PRC(P_HEADER_ID NUMBER , P_LINE_ID NUMBER);
END XXDHL_RELEASE_HOLD_PKG;
/
No comments:
Post a Comment