Oracle Apps Technical: 2019

CREATE OR REPLACE PACKAGE APPS.XXSC_CREDIT_CARD_SURCHARGE_PKG
IS
/*+=====================================================================================+
| Lumentum Operations LLC All rights reserved. |
+=====================================================================================+
| |
| Procedure Name : XXSC_APPLY_CC_CHARGES |
| Created By : Brajabandhu Behera |
| Created Date : May 25, 2018 |
| Description : This Procedure is created for Call to Modify Credit Card Charges |
at sales order line level |
| |
| |
| Modification History |
| |
| Ver Date Name Description |
| === ========= ============= ===========================================|
| 1.0 25-May-18 Brajabandhu Behera Initial Development. |
| |
| |
+=====================================================================================+*/
PROCEDURE XXSC_INSERT_PRICE_UPDATE(p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_unit_price IN NUMBER);
PROCEDURE XXSC_APPLY_CC_CHARGES (p_header_id IN NUMBER,
p_line_id IN NUMBER);

PROCEDURE XXSC_UPDATE_CC_CHARGES;

END;
/

CREATE OR REPLACE PACKAGE BODY XXSC_CREDIT_CARD_SURCHARGE_PKG
IS

PROCEDURE XXSC_INSERT_PRICE_UPDATE(p_header_id IN NUMBER,
p_line_id IN NUMBER,
p_unit_price IN NUMBER)
IS
/*+=====================================================================================+
| Lumentum Operations LLC All rights reserved. |
+=====================================================================================+
| |
| Procedure Name : XXSC_INSERT_PRICE_UPDATE |
| Created By : Brajabandhu Behera |
| Created Date : May 25, 2018 |
| Description : This Procedure is Insert the Line id into custome table |
| for any Unit Price change at sales order line level |
| |
| Modification History |
| |
| Ver Date Name Description |
| === ========= ============= ===========================================|
| 1.0 25-May-18 Brajabandhu Behera Initial Development. |
| |
| |
+=====================================================================================+*/

v_line_id NUMBER;
v_header_id NUMBER;
v_line_number NUMBER;
v_order_number NUMBER;
v_created_by NUMBER;
v_last_updated_by NUMBER;
v_last_update_date DATE;

v_unit_price NUMBER;
vl_line_id NUMBER;
l_adjustment_id NUMBER;

BEGIN
--checking the Modifier Applied or not
--************************************--

BEGIN
SELECT DISTINCT opa.price_adjustment_id
INTO l_adjustment_id
FROM QPFV_MODIFIER_HEADERS hdr,
QPFV_MODIFIER_LINES lin,
OE_PRICE_ADJUSTMENtS opa
WHERE 1=1
and lin.list_header_id = hdr.list_header_id
AND lin.list_line_type_code ='FREIGHT_CHARGE'
AND lin.modifier_level_code = 'LINE'
AND hdr.name = 'Credit Card Surcharge'
AND opa.list_header_id = hdr.list_header_id
AND opa.list_line_id = lin.list_line_id
AND lin.end_date_active IS NULL
AND hdr.end_date_active IS NULL
AND opa.line_id =TO_NUMBEr(p_line_id);
EXCEPTION
WHEN OTHERS THEN
l_adjustment_id :=NULL;
END;

IF l_adjustment_id IS NOT NULL THEN

BEGIN

SELECT ooh.order_number,
ool.unit_selling_price,
ool.header_id,
ool.line_id,
ool.line_number,
ool.created_by,
ool.last_updated_by,
ool.last_update_date
INTO v_order_number,
v_unit_price,
v_header_id,
v_line_id,
v_line_number,
v_created_by,
v_last_updated_by,
v_last_update_date
FROM OE_ORDER_HEADERS_ALL ooh,
OE_ORDER_LINES_ALL ool
WHERE 1=1
AND ooh.header_id = ool.header_id
AND ooh.header_id = to_number(p_header_id)
AND ool.line_id =to_number(p_line_id);
EXCEPTION
WHEN OTHERS THEN
v_unit_price :=NULL;
END;

--Chcek the Line id Exists in the custom table to Avaoid inser new Line
BEGIN
SELECT DISTINCT line_id
INTO vl_line_id
FROM XXSC.XXSC_CREDIT_CARD_SURCHARGE
WHERE HEADER_ID = v_header_id
AND LINE_ID =v_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vl_line_id :=NULL;
END;

DBMS_OUTPUT.PUT_LINE ('Check the records '||p_header_id);
DBMS_OUTPUT.PUT_LINE ('Check the records '||p_line_id);
DBMS_OUTPUT.PUT_LINE ('Check the records '||vl_line_id);

IF vl_line_id IS NOT NULL THEN

IF p_unit_price <> v_unit_price THEN

UPDATE XXSC.XXSC_CREDIT_CARD_SURCHARGE
SET NEW_SELLING_PRICE =p_unit_price,
OLD_UNIT_SELLING_PRICE =p_unit_price,
PROCESS_FLAG='NEW'
WHERE header_id = v_header_id
AND line_id =v_line_id;

COMMIT;
END IF;

ELSIF vl_line_id IS NULL THEN

IF p_unit_price <> v_unit_price THEN

--Insert the records in custom table first time

BEGIN

INSERT INTO XXSC.XXSC_CREDIT_CARD_SURCHARGE
( ORDER_NUMBER
,LINE_NUMBER
,HEADER_ID
,LINE_ID
,OLD_UNIT_SELLING_PRICE
--,NEW_SELLING_PRICE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,PROCESS_FLAG )
VALUES
(v_order_number
,v_line_number
,v_header_id
,v_line_id
,p_unit_price--OLL UNOT SELLNNG PRICE
,v_created_by
,sysdate
,v_last_updated_by
,v_last_update_date
,'NEW');

COMMIT;
END;
END IF;

END IF;
END IF;
END;

--***************************************************************************************
--**************************************************************************************
PROCEDURE XXSC_APPLY_CC_CHARGES (p_header_id IN NUMBER,
p_line_id IN NUMBER)
IS
/*+=====================================================================================+
| Lumentum Operations LLC All rights reserved. |
+=====================================================================================+
| |
| Procedure Name : XXSC_APPLY_CC_CHARGES |
| Created By : Brajabandhu Behera |
| Created Date : May 25, 2018 |
| Description : This Procedure is created for Apply Credit Card Charges |
at sales order line level |
| |
| |
| Modification History |
| |
| Ver Date Name Description |
| === ========= ============= ===========================================|
| 1.0 25-May-18 Brajabandhu Behera Initial Development. |
| |
| |
+=====================================================================================+*/
--DECLARE
v_api_version_number NUMBER := 1;
v_return_status VARCHAR2 (2000);
v_msg_count NUMBER;
v_msg_data VARCHAR2 (2000);
-- IN Variables --
v_header_rec OE_ORDER_PUB.header_rec_type;
v_line_tbl OE_ORDER_PUB.line_tbl_type;
v_action_request_tbl OE_ORDER_PUB.request_tbl_type;
v_line_adj_tbl OE_ORDER_PUB.line_adj_tbl_type;
v_hdr_adj_tbl OE_ORDER_PUB.header_adj_tbl_type;
v_list_header_id NUMBER;
v_list_line_id NUMBER;
v_operand VARCHAR2 (10);
v_ln_type_code VARCHAR2 (10);
v_operator VARCHAR2 (10);
v_phase_id NUMBER;
v_mod_level_code VARCHAR2 (10);
t_line_id NUMBER;
t_header_id NUMBER;
t_org_id NUMBER;
l_pricing_quantity NUMBER;
l_unit_selling_price NUMBER;
l_attribute1 NUMBER;
l_header_id NUMBER;
l_unit_price NUMBER;
l_operand NUMBER;
-- OUT Variables --
v_header_rec_out OE_ORDER_PUB.header_rec_type;
v_header_val_rec_out OE_ORDER_PUB.header_val_rec_type;
v_header_adj_tbl_out OE_ORDER_PUB.header_adj_tbl_type;
v_header_adj_val_tbl_out OE_ORDER_PUB.header_adj_val_tbl_type;
v_header_price_att_tbl_out OE_ORDER_PUB.header_price_att_tbl_type;
v_header_adj_att_tbl_out OE_ORDER_PUB.header_adj_att_tbl_type;
v_header_adj_assoc_tbl_out OE_ORDER_PUB.header_adj_assoc_tbl_type;
v_header_scredit_tbl_out OE_ORDER_PUB.header_scredit_tbl_type;
v_header_scredit_val_tbl_out OE_ORDER_PUB.header_scredit_val_tbl_type;
v_line_tbl_out OE_ORDER_PUB.line_tbl_type;
v_line_val_tbl_out OE_ORDER_PUB.line_val_tbl_type;
v_line_adj_tbl_out OE_ORDER_PUB.line_adj_tbl_type;
v_line_adj_val_tbl_out OE_ORDER_PUB.line_adj_val_tbl_type;
v_line_price_att_tbl_out OE_ORDER_PUB.line_price_att_tbl_type;
v_line_adj_att_tbl_out OE_ORDER_PUB.line_adj_att_tbl_type;
v_line_adj_assoc_tbl_out OE_ORDER_PUB.line_adj_assoc_tbl_type;
v_line_scredit_tbl_out OE_ORDER_PUB.line_scredit_tbl_type;
v_line_scredit_val_tbl_out OE_ORDER_PUB.line_scredit_val_tbl_type;
v_lot_serial_tbl_out OE_ORDER_PUB.lot_serial_tbl_type;
v_lot_serial_val_tbl_out OE_ORDER_PUB.lot_serial_val_tbl_type;
v_action_request_tbl_out OE_ORDER_PUB.request_tbl_type;
v_msg_index NUMBER;
v_data VARCHAR2 (2000);
v_loop_count NUMBER;
v_debug_file VARCHAR2 (200);
b_return_status VARCHAR2 (200);
b_msg_count NUMBER;
b_msg_data VARCHAR2 (2000);

l_org_id NUMBER;
l_application_id NUMBER;
l_responsibility_id NUMBER;
l_user_id NUMBER;
l_charges NUMBER(10,2);
l_adjustment_id NUMBER;
v_err_msg VARCHAR2(2000);
v_percent VARCHAR2(100);
vl_payment_term_id NUMBER;
vl_term_name VARCHAR2(100);

BEGIN
DBMS_OUTPUT.put_line ('Starting of script');

l_org_id := FND_PROFILE.VALUE('ORG_ID');--82
l_user_id :=FND_PROFILE.VALUE('USER_ID');---7699;
l_responsibility_id :=FND_PROFILE.VALUE('RESPONSIBILITY_ID');--21623;
l_application_id := FND_PROFILE.VALUE('APPLICATION_ID');--660;

FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id);

MO_GLOBAL.INIT('ONT');
MO_GLOBAL.SET_POLICY_CONTEXT('S',82);

v_line_tbl(1) := OE_Order_PUB.G_MISS_LINE_REC;
v_line_tbl(1).header_id := p_header_id; --c_order.header_id;
v_line_tbl(1).line_id :=p_line_id; --c_order.line_id;

dbms_output.put_line ('Order_header_id =>'||v_line_tbl(1).header_id);
dbms_output.put_line ('Order_line_id =>'||v_line_tbl(1).line_id);

/* Get the Payment Term id */
/****************************/
BEGIN
SELECT rtt.term_id,rtt.name
INTO vl_payment_term_id,vl_term_name
FROM apps.RA_TERMS_TL rtt,
apps.OE_ORDER_HEADERS_ALL ooh
WHERE 1=1
--and rtt.NAME ='CREDIT CARD'
AND rtt.term_id =ooh.payment_term_id
AND ooh.header_id = p_header_id;
EXCEPTION
WHEN OTHERS THEN
vl_payment_term_id :=NULL;
vl_term_name :=NULL;
END;

DBMS_OUTPUT.PUT_LINE ('VL_PAYMENT_TERM_ID => '||VL_PAYMENT_TERM_ID);
FND_FILE.put_line(FND_FILE.output,'Payment Term Name ::' ||vl_term_name);

--Get the Percent from Order Management Quick code --
/**************************************************/
BEGIN
SELECT meaning
INTO v_percent
FROM fnd_lookup_values
WHERE lookup_type ='CREDIT_CARD_SURCHARGE';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_percent:=3;
END;

/*Checking the Modifier Name and List Header and List Line Id for Credit Card Sales order
/*********************************************************************************/
IF UPPER(vl_term_name) ='CREDIT CARD' THEN

DBMS_OUTPUT.PUT_LINE ('VL_PAYMENT_TERM_ID FOR CREDIT CARD ORDER=> '||VL_PAYMENT_TERM_ID);
BEGIN
SELECT qa2.list_header_id,qa2.list_line_id
INTO v_list_header_id, v_list_line_id
FROM apps.QP_SECU_LIST_HEADERS_VL qa1,
apps.QP_MODIFIER_SUMMARY_V qa2,
apps.QP_QUALIFIERS_V qa3
WHERE QA1.NAME ='Credit Card Charge'
AND qa1.list_header_id = qa2.list_header_id
AND qa2.PRICING_PHASE ='All Lines Adjustment'
AND qa2.MODIFIER_LEVEL ='Line'
AND qa2.LIST_LINE_TYPE ='Freight/Special Charge'
AND qa2.CHARGE_NAME ='Credit Card Surcharge'
AND qa2.FORMULA ='XXSC: Credit Card Surcharge Calculation'
AND qa2.AUTOMATIC_FLAG ='Y'
AND qa2.end_date_active IS NULL
AND qa1.end_date_active IS NULL
AND qa2.list_line_id =qa3.list_line_id
AND qa3.COMPARISION_OPERATOR_CODE ='='
AND qa3.QUALIFIER_CONTEXT ='TERMS';
EXCEPTION
WHEN OTHERS THEN
v_list_header_id :=NULL;
v_list_line_id :=NULL;
END;

ELSIF UPPER(vl_term_name) <> 'CREDIT CARD' THEN

BEGIN
SELECT qa2.list_header_id,qa2.list_line_id
INTO v_list_header_id, v_list_line_id
FROM APPS.QP_SECU_LIST_HEADERS_VL qa1,
APPS.QP_MODIFIER_SUMMARY_V qa2,
APPS.QP_QUALIFIERS_V qa3
WHERE QA1.NAME ='Credit Card Charge'
AND qa1.list_header_id = qa2.list_header_id
AND qa2.PRICING_PHASE ='All Lines Adjustment'
AND qa2.MODIFIER_LEVEL ='Line'
AND qa2.LIST_LINE_TYPE ='Freight/Special Charge'
AND qa2.CHARGE_NAME ='Credit Card Surcharge'
AND qa2.FORMULA ='XXSC: Credit Card Surcharge Calculation'
AND qa2.AUTOMATIC_FLAG ='N'
AND qa2.end_date_active IS NULL
AND qa1.end_date_active IS NULL
AND qa2.list_line_id =qa3.list_line_id
AND qa3.COMPARISION_OPERATOR_CODE ='NOT ='
AND qa3.QUALIFIER_CONTEXT ='TERMS';
EXCEPTION
WHEN OTHERS THEN
v_list_header_id :=NULL;
v_list_line_id :=NULL;
END;
END IF;

DBMS_OUTPUT.PUT_LINE ('list_header_id =>'||v_list_header_id);
DBMS_OUTPUT.PUT_LINE ('list_line id =>'||v_list_line_id);
FND_FILE.put_line(FND_FILE.output,'Modifier Header id ::' ||v_list_header_id);
FND_FILE.put_line(FND_FILE.output,'Modifier Line id ::' ||v_list_line_id);

/*Check the Credit Card Charge Already Applied for Credit Card Order*/
/********************************************************************/

DBMS_OUTPUT.PUT_LINE ('Modifien Number '||v_list_line_id);
BEGIN
SELECT opa.price_adjustment_id
INTO l_adjustment_id
FROM QPFV_MODIFIER_HEADERS hdr,
QPFV_MODIFIER_LINES lin,
OE_PRICE_ADJUSTMENtS opa
WHERE 1=1
and lin.list_header_id = hdr.list_header_id
AND lin.list_line_type_code ='FREIGHT_CHARGE'
AND lin.modifier_level_code = 'LINE'
AND hdr.name = 'Credit Card Surcharge'
AND opa.list_header_id = hdr.list_header_id
AND opa.list_line_id = lin.list_line_id
AND opa.line_id =v_line_tbl(1).line_id-- cp_order.line_id;
AND lin.end_date_active IS NULL
AND hdr.end_date_active IS NULL
AND lin.list_line_no =v_list_line_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_adjustment_id :=NULL;

END;

l_charges :=0;

SELECT ROUND(((oola.unit_selling_price /100)* to_number(v_percent)),2)
INTO l_charges
FROM oe_order_lines_all oola
WHERE line_id = v_line_tbl(1).line_id;

DBMS_OUTPUT.PUT_LINE('l_charges:'||l_charges );

FND_FILE.put_line(FND_FILE.output,'Update Credit Card Surcharge Amount on Sales Order Line Level:: '||l_charges);

IF l_adjustment_id IS NULL THEN

-- Adjustment Variables
v_Line_Adj_Tbl(1) := APPS.OE_ORDER_PUB.G_MISS_LINE_ADJ_REC;
v_Line_Adj_Tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
v_Line_Adj_Tbl(1).header_id := v_line_tbl(1).header_id; --header_id of the sales order --p_header_id
v_Line_Adj_Tbl(1).line_id := v_line_tbl(1).line_id; --line_id of the sales order line --p_header_id
v_Line_Adj_Tbl(1).price_adjustment_id := oe_price_adjustments_s.NEXTVAL;
v_Line_Adj_Tbl(1).automatic_flag := 'N';
v_Line_Adj_Tbl(1).applied_flag := 'Y';
v_Line_Adj_Tbl(1).updated_flag := 'Y'; --Optional, this is the fixed flag.
v_Line_Adj_Tbl(1).list_header_id := v_list_header_id; --list_header_id of the adjustment
v_Line_Adj_Tbl(1).list_line_id := v_list_line_id; --list_line_id of the adjustment
v_line_adj_tbl(1).list_line_type_code := 'FREIGHT_CHARGE';
v_line_adj_tbl(1).change_reason_code := 'MANUAL';
v_line_adj_tbl(1).change_reason_text := 'Manually applied adjustments';
-- l_Line_Adj_Tbl(1).percent:= 3;
v_Line_Adj_Tbl(1).arithmetic_operator:='%';
v_Line_Adj_Tbl(1).operand := v_percent;--3;
v_Line_Adj_Tbl(1).adjusted_amount_per_pqty :=l_charges;
v_Line_Adj_Tbl(1).adjusted_amount :=l_charges;
v_Line_Adj_Tbl(1).operand_per_pqty :=v_percent;--3;

DBMS_OUTPUT.put_line ('Starting of API');
-- Calling the API to update the header details of an existing Order --
OE_ORDER_PUB.PROCESS_ORDER
(p_api_version_number => v_api_version_number,
-- p_header_rec => v_header_rec,
p_line_tbl => v_line_tbl,
--p_header_adj_tbl => v_hdr_adj_tbl,
p_line_adj_tbl => v_line_adj_tbl,
-- OUT variables
x_header_rec => v_header_rec_out,
x_header_val_rec => v_header_val_rec_out,
x_header_adj_tbl => v_header_adj_tbl_out,
x_header_adj_val_tbl => v_header_adj_val_tbl_out,
x_header_price_att_tbl => v_header_price_att_tbl_out,
x_header_adj_att_tbl => v_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => v_header_adj_assoc_tbl_out,
x_header_scredit_tbl => v_header_scredit_tbl_out,
x_header_scredit_val_tbl => v_header_scredit_val_tbl_out,
x_line_tbl => v_line_tbl_out,
x_line_val_tbl => v_line_val_tbl_out,
x_line_adj_tbl => v_line_adj_tbl_out,
x_line_adj_val_tbl => v_line_adj_val_tbl_out,
x_line_price_att_tbl => v_line_price_att_tbl_out,
x_line_adj_att_tbl => v_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => v_line_adj_assoc_tbl_out,
x_line_scredit_tbl => v_line_scredit_tbl_out,
x_line_scredit_val_tbl => v_line_scredit_val_tbl_out,
x_lot_serial_tbl => v_lot_serial_tbl_out,
x_lot_serial_val_tbl => v_lot_serial_val_tbl_out,
x_action_request_tbl => v_action_request_tbl_out,
x_return_status => v_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data
);

DBMS_OUTPUT.put_line ('Completion of API');

IF v_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'Order Header Updation Success : '
|| v_header_rec_out.header_id
);
ELSE
DBMS_OUTPUT.put_line ('Order Header Updation failed:' || v_msg_data);
ROLLBACK;

FOR i IN 1 .. v_msg_count
LOOP
v_msg_data := oe_msg_pub.get (p_msg_index => i, p_encoded => 'F');
DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
END LOOP;
END IF;
END IF; --END OF ADJUSTMENT ID

END;
--******************************************************************************--
--******************************************************************************--
PROCEDURE XXSC_UPDATE_CC_CHARGES
IS
/*+=====================================================================================+
| Lumentum Operations LLC All rights reserved. |
+=====================================================================================+
| |
| Procedure Name : XXSC_UPDATE_CC_CHARGES |
| Created By : Brajabandhu Behera |
| Created Date : May 25, 2018 |
| Description : This Procedure is created for Call to Modify Credit Card Charges |
at sales order line level |
| |
| |
| Modification History |
| |
| Ver Date Name Description |
| === ========= ============= ===========================================|
| 1.0 25-May-18 Brajabandhu Behera Initial Development. |
| |
| |
+=====================================================================================+*/

l_msg_index NUMBER;
l_data VARCHAR2(2000);

l_header_rec OE_ORDER_PUB.Header_Rec_Type;
l_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
l_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
l_header_adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
l_header_price_att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
l_header_adj_att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
l_header_adj_assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
l_header_scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
l_header_scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
l_Header_Payment_tbl OE_ORDER_PUB.Header_Payment_Tbl_Type;
l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
l_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
l_line_adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
l_line_adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
l_line_price_att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
l_line_adj_att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
l_line_adj_assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
l_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
l_line_scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
l_lot_serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
l_lot_serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
o_header_rec OE_ORDER_PUB.Header_Rec_Type;
o_header_val_rec OE_ORDER_PUB.Header_Val_Rec_Type;
o_header_adj_tbl OE_ORDER_PUB.Header_Adj_Tbl_Type;
o_header_adj_val_tbl OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;
o_header_price_att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
o_header_adj_att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
o_header_adj_assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
o_header_scredit_tbl OE_ORDER_PUB.Header_Scredit_Tbl_Type;
o_header_scredit_val_tbl OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;
o_Header_Payment_Tbl OE_ORDER_PUB.Header_Payment_Tbl_Type;
o_Header_Payment_Val_Tbl OE_ORDER_PUB.Header_Payment_Val_Tbl_Type;
o_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
o_line_val_tbl OE_ORDER_PUB.Line_Val_Tbl_Type;
o_line_adj_tbl OE_ORDER_PUB.Line_Adj_Tbl_Type;
o_line_adj_val_tbl OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;
o_line_price_att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
o_line_adj_att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
o_line_adj_assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
o_line_scredit_tbl OE_ORDER_PUB.Line_Scredit_Tbl_Type;
o_line_scredit_val_tbl OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;
o_lot_serial_tbl OE_ORDER_PUB.Lot_Serial_Tbl_Type;
o_lot_serial_val_tbl OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;
o_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
o_Line_Payment_tbl OE_ORDER_PUB.Line_Payment_Tbl_Type;
o_Line_Payment_val_tbl OE_ORDER_PUB.Line_Payment_Val_Tbl_Type;

l_adjustment_id NUMBER;
l_session_id NUMBER;
l_count NUMBER;
l_msg_count NUMBER := 0;
x_cnt NUMBER := 3;
l_return_status VARCHAR2(1);
l_msg_data VARCHAR2(2000);
x_msg_data VARCHAR2(2000);
x_msg_details VARCHAR2(2000);
x_msg_count VARCHAR2(2000);
msg_text VARCHAR2(2000) DEFAULT NULL;
l_charges NUMBER(10,2);
v_err_msg VARCHAR2(2000);

l_org_id NUMBER;
l_application_id NUMBER;
l_responsibility_id NUMBER;
l_user_id NUMBER;

v_list_header_id NUMBER;
v_list_line_id NUMBER;
vl_header_id NUMBER;
vl_line_id NUMBER;
vl_line_number NUMBER;
v_percent VARCHAR2(20);

CURSOR c_order is
SELECT DISTINCT header_id, line_id
FROM XXSC.XXSC_CREDIT_CARD_SURCHARGE
WHERE PROCESS_FLAG='NEW';

cp_order c_order%ROWTYPE;

BEGIN

l_org_id := FND_PROFILE.VALUE('ORG_ID');--82
l_user_id :=FND_PROFILE.VALUE('USER_ID');---7699;
l_responsibility_id := FND_PROFILE.VALUE('RESPONSIBILITY_ID');--21623;
l_application_id := FND_PROFILE.VALUE('APPLICATION_ID');--660; */

FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_responsibility_id, l_application_id);

MO_GLOBAL.INIT('ONT');
MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);

OPEN c_order;
LOOP

FETCH c_order into cp_order;
EXIT WHEN c_order%NOTFOUND;

IF c_order%NOTFOUND THEN
FND_FILE.PUT_LINE(FND_FILE.output,'No Records found for Update from custom table::');
FND_FILE.PUT_LINE(FND_FILE.log,'No Records found for Update from custom table::');

END IF;

IF c_order%FOUND THEN
-- FND_FILE.put_line(FND_FILE.output,'No Records found to Updated ::');

-- Populate line record
l_line_tbl(1) := apps.OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).header_id := cp_order.header_id;
l_line_tbl(1).line_id :=cp_order.line_id;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;

DBMS_OUTPUT.PUT_LINE ('Order_header_id =>'||l_line_tbl(1).header_id);
DBMS_OUTPUT.PUT_LINE ('Order_line_id =>'||l_line_tbl(1).line_id);

--Check the Modifier List Header id and List Line Id
/*******************************************************/
BEGIN
SELECT hdr.list_header_id,
lin.list_line_id,
opa.price_adjustment_id
INTO v_list_header_id,
v_list_line_id,
l_adjustment_id
FROM QPFV_MODIFIER_HEADERS hdr,
QPFV_MODIFIER_LINES lin,
OE_PRICE_ADJUSTMENtS opa
WHERE 1=1
and lin.list_header_id = hdr.list_header_id
AND lin.list_line_type_code ='FREIGHT_CHARGE'
AND lin.modifier_level_code = 'LINE'
AND hdr.name = 'Credit Card Surcharge'
AND opa.list_header_id = hdr.list_header_id
AND opa.list_line_id = lin.list_line_id
AND lin.end_date_active IS NULL
AND hdr.end_date_active IS NULL
AND opa.line_id =l_line_tbl(1).line_id;-- cp_order.line_id;
EXCEPTION
WHEN OTHERS THEN
v_err_msg :='No Modifier found for the sales Order Number'||l_line_tbl(1).header_id;
FND_FILE.put_line(FND_FILE.output,'No Modifier found for the sales Order Number ::');
END;

dbms_output.put_line ('list_header_id =>'||v_list_header_id);
dbms_output.put_line ('list_line id =>'||v_list_line_id);
FND_FILE.put_line(FND_FILE.output,'Modifier Header id ::' ||v_list_header_id);
FND_FILE.put_line(FND_FILE.output,'Modifier Line id ::' ||v_list_line_id);

--Get the Percent from Order Management Quick code
/*************************************************/
BEGIN
SELECT meaning
INTO v_percent
FROM fnd_lookup_values
WHERE lookup_type ='CREDIT_CARD_SURCHARGE';
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_percent:=3;
END;

--Calculate the 3% Credit Card Surcharge on the Line Unit Selling Price
/**********************************************************************/

l_charges :=0;

SELECT ROUND(((oola.unit_selling_price /100)* TO_NUMBER(v_percent) ),2)
INTO l_charges
FROM oe_order_lines_all oola
WHERE line_id = l_line_tbl(1).line_id;

DBMS_OUTPUT.PUT_LINE('l_adjustment_id:'||l_adjustment_id );
DBMS_OUTPUT.PUT_LINE('l_charges:'||l_charges );

FND_FILE.put_line(FND_FILE.output,'Updated Credit Card Surcharge Amount:: '||l_charges);
FND_FILE.put_line(FND_FILE.output,'Record Processed for Adjustment id:: '||l_adjustment_id);

/* IF v_err_msg IS NOT NULL THEN
FND_FILE.put_line(FND_FILE.output,'The Sales Order line do not have any Credit Card Surcharge Applied Earlier:: ');
FND_FILE.put_line(FND_FILE.output,'***********************************************************************');

UPDATE XXSC_CREDIT_CARD_SURCHARGE
SET PROCESS_FLAG='ERROR'
WHERE HEADER_ID = l_Line_Adj_Tbl(1).header_id
AND LINE_ID =l_Line_Adj_Tbl(1).line_id;

COMMIT;

END IF; */

IF v_err_msg IS NULL THEN

l_Line_Adj_Tbl(1) := APPS.OE_ORDER_PUB.G_MISS_LINE_ADJ_REC;
l_Line_Adj_Tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
l_Line_Adj_Tbl(1).header_id := l_line_tbl(1).header_id; --header_id of the sales order --p_header_id
l_Line_Adj_Tbl(1).line_id := l_line_tbl(1).line_id; --line_id of the sales order line --p_header_id
l_Line_Adj_Tbl(1).price_adjustment_id := l_adjustment_id;
l_Line_Adj_Tbl(1).automatic_flag := 'N';
l_Line_Adj_Tbl(1).applied_flag := 'Y';
l_Line_Adj_Tbl(1).updated_flag := 'Y'; --Optional, this is the fixed flag.
l_Line_Adj_Tbl(1).list_header_id := v_list_header_id; --list_header_id of the adjustment
l_Line_Adj_Tbl(1).list_line_id := v_list_line_id; --list_line_id of the adjustment
l_line_adj_tbl(1).list_line_type_code := 'FREIGHT_CHARGE';
l_line_adj_tbl(1).change_reason_code := 'MANUAL';
l_line_adj_tbl(1).change_reason_text := 'Manually applied adjustments';
-- l_Line_Adj_Tbl(1).percent:= 3;
l_Line_Adj_Tbl(1).arithmetic_operator:='%';
l_Line_Adj_Tbl(1).operand := v_percent; --3
l_Line_Adj_Tbl(1).adjusted_amount_per_pqty :=l_charges;
l_Line_Adj_Tbl(1).adjusted_amount :=l_charges;
l_Line_Adj_Tbl(1).operand_per_pqty :=v_percent; --3

DBMS_OUTPUT.PUT_LINE('Just before calling Process_Order API l_index:' );
FND_FILE.put_line(FND_FILE.output,'Just before calling Process_Order API:: ');

--==================================================================================================
-- Process Order API to Apply the Credit Card Surcharge Modifier on Sales Order Line level
--==================================================================================================
OE_ORDER_PUB.PROCESS_ORDER(
p_api_version_number => 1.0
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
--,p_header_rec => l_header_rec
,p_line_tbl => l_line_tbl
,p_line_adj_tbl => l_line_adj_tbl
,p_Header_Payment_tbl => l_Header_Payment_tbl
,p_action_request_tbl => l_action_request_tbl
--out parameter
,x_header_rec => o_header_rec
,x_header_val_rec => o_header_val_rec
,x_header_adj_tbl => o_header_adj_tbl
,x_header_adj_val_tbl => o_header_adj_val_tbl
,x_header_price_att_tbl => o_header_price_att_tbl
,x_header_adj_att_tbl => o_header_adj_att_tbl
,x_header_adj_assoc_tbl => o_header_adj_assoc_tbl
,x_header_scredit_tbl => o_header_scredit_tbl
,x_header_scredit_val_tbl => o_header_scredit_val_tbl
,x_Header_Payment_tbl => o_Header_Payment_tbl
,x_Header_Payment_val_tbl => o_Header_Payment_val_tbl
,x_line_tbl => o_line_tbl
,x_line_val_tbl => o_line_val_tbl
,x_line_adj_tbl => o_line_adj_tbl
,x_line_adj_val_tbl => o_line_adj_val_tbl
,x_line_price_att_tbl => o_line_price_att_tbl
,x_line_adj_att_tbl => o_line_adj_att_tbl
,x_line_adj_assoc_tbl => o_line_adj_assoc_tbl
,x_line_scredit_tbl => o_line_scredit_tbl
,x_line_scredit_val_tbl => o_line_scredit_val_tbl
,x_lot_serial_tbl => o_lot_serial_tbl
,x_lot_serial_val_tbl => o_lot_serial_val_tbl
,x_action_request_tbl => o_action_request_tbl
,x_Line_Payment_tbl => o_Line_Payment_tbl
,x_Line_Payment_val_tbl => o_Line_Payment_val_tbl
);

DBMS_OUTPUT.PUT_LINE( 'l_msg_count :'||nvl(l_msg_count,0));
IF l_msg_count > 0 THEN
FOR I IN 1..l_msg_count LOOP
l_msg_data := OE_MSG_PUB.get(
p_msg_index => i,
p_encoded => 'F');
DBMS_OUTPUT.PUT_LINE('MESSAGE : '||substrb( l_msg_data,1,200));
FND_FILE.put_line(FND_FILE.output,'*************************************');

FND_FILE.put_line(FND_FILE.output,'MESSAGE : '||substrb( l_msg_data,1,200));
FND_FILE.put_line(FND_FILE.output,'***************************************');
END LOOP ;
END IF ;

IF l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('**********************************************');
dbms_output.put_line('SUCCESS');
-- FND_FILE.put_line(FND_FILE.output,'*********************************************************');
FND_FILE.put_line(FND_FILE.output,'Line Charges Updates 3% SUCCESS');
l_count := o_line_tbl.LAST;
IF l_count > 0 THEN
FOR l_index IN 1..l_count LOOP
dbms_output.put_line('***************************');
dbms_output.put_line('LINE_ID('||l_index||') : ' || o_line_tbl(l_index).LINE_ID);
dbms_output.put_line('Adjust Operand('||l_index||') : ' || o_line_adj_tbl(l_index).operand);
FND_FILE.put_line(FND_FILE.output,'LINE_ID('||l_index||') : ' || o_line_tbl(l_index).LINE_ID);
FND_FILE.put_line(FND_FILE.output,'Adjust Operand('||l_index||') : ' || o_line_adj_tbl(l_index).operand);
FND_FILE.put_line(FND_FILE.output,'adjusted_amount_per_pqty('||l_index||') : ' || o_line_adj_tbl(l_index).adjusted_amount_per_pqty);
FND_FILE.put_line(FND_FILE.output,'adjusted_amount('||l_index||') : ' || o_line_adj_tbl(l_index).adjusted_amount);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('***** COMMIT *****');

FND_FILE.put_line(FND_FILE.output,'**************************************************************');
COMMIT;

UPDATE XXSC_CREDIT_CARD_SURCHARGE
SET PROCESS_FLAG='PROCESS'
WHERE HEADER_ID = l_Line_Adj_Tbl(1).header_id
AND LINE_ID =l_Line_Adj_Tbl(1).line_id;

COMMIT;
-- dbms_output.put_line('OM Debug File Name :'||dbg_file);
ELSE

dbms_output.put_line( 'FAILURE' );
FND_FILE.put_line(FND_FILE.output,'The API not able to Process the Records ');
dbms_output.put_line('RETURN STATUS = '||l_return_status);
FND_FILE.put_line(FND_FILE.output,'Line Charges Can not Update FAILURE');
dbms_output.put_line('***** ROLLBACK *****');
FND_FILE.put_line(FND_FILE.output,'ROLLBACK');
ROLLBACK;
END IF;
END IF;

v_err_msg :=NULL;
END IF;

END LOOP;
CLOSE c_order;

END;

END;
/

Oracle Apps Technical

Tuesday 29 January 2019

Order Management API to add Surcharge Modifier in the sales order Line

About Me