[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_AuthInfo_for_MultiPmnts
( p_header_id IN NUMBER
, p_auth_amount IN NUMBER
, p_auth_code IN VARCHAR2
, p_auth_date IN DATE
, p_tangible_id IN VARCHAR2
, p_line_id IN NUMBER
, p_payment_number IN NUMBER
, p_msg_count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
);
SELECT line_number,
shipment_number,
option_number,
component_number,
service_number
INTO l_line_number,
l_shipment_number,
l_option_number,
l_component_number,
l_service_number
from oe_order_lines_all
where line_id = p_line_id;
SELECT NVL(MAX(H.HOLD_RELEASE_ID),0)
INTO l_hold_release_id
FROM OE_ORDER_HOLDS h,
OE_HOLD_SOURCES s
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_id
AND H.LINE_ID IS NULL
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = p_hold_id
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_id
AND S.RELEASED_FLAG ='Y';
SELECT NVL(MAX(H.HOLD_RELEASE_ID),0)
INTO l_hold_release_id
FROM OE_ORDER_HOLDS h,
OE_HOLD_SOURCES s
WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
AND H.HEADER_ID = p_header_id
AND H.LINE_ID = p_line_id
AND H.HOLD_RELEASE_ID IS NOT NULL
AND S.HOLD_ID = p_hold_id
AND S.HOLD_ENTITY_CODE = 'O'
AND S.HOLD_ENTITY_ID = p_header_id
AND S.RELEASED_FLAG ='Y';
select 'Y'
into l_manual_hold_exists
FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
AND CREATED_BY <> 1;
SELECT line_id
FROM oe_order_lines
WHERE header_id = p_header_id
AND invoice_to_org_id = nvl(p_invoice_to_org_id, invoice_to_org_id);
SELECT line_id, line_number
FROM oe_order_lines
WHERE header_id = p_header_id
AND invoice_to_org_id = p_invoice_to_org_id
AND open_flag = 'Y';
SELECT invoice_to_org_id
FROM oe_order_lines
where header_id = l_header_rec.header_id
group by invoice_to_org_id;
** itself is being updated to NULL
IF l_header_rec.credit_card_approval_code is NOT NULL THEN
**
-- Check approval date(instead of approval code) to find out
-- if we should really proceed with VOID. This is a must as
-- without this IF condition every time a delayed request is
-- logged application will try to void irrespective of the
-- fact that it is really required.Approval date will be not
-- null even if approval code has been updated to null.
IF l_header_rec.credit_card_approval_date is NOT NULL THEN
-- Call the Payment Request API
OE_DEBUG_PUB.ADD('OEXPVPMB: Before Calling Payment Request API For VOIDAUTHONLY');
SELECT COUNT(*)
INTO l_ship_rule_count
FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
WF_PROCESS_ACTIVITIES WPA,
OE_ORDER_LINES OOL,
OE_ORDER_HEADERS OOH
WHERE WIAS.ITEM_TYPE = 'OEOL'
AND WIAS.ACTIVITY_STATUS = 'COMPLETE'
AND WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
AND WPA.ACTIVITY_NAME = 'SHIP_LINE'
AND WIAS.ITEM_KEY = TO_CHAR(OOL.LINE_ID)
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOH.HEADER_ID = p_header_id;
SELECT 1 /* MOAC_SQL_CHANGE */
INTO l_ship_rule_count
FROM OE_ORDER_HEADERS_ALL OOH
WHERE OOH.HEADER_ID = p_header_id
AND EXISTS
(SELECT 'Line Shipped'
FROM OE_ORDER_LINES OOL
WHERE OOL.HEADER_ID = OOH.HEADER_ID
AND NVL(OOL.SHIPPED_QUANTITY, 0) > 0);
SELECT COUNT(*) + l_ship_rule_count
INTO l_ship_rule_count
FROM WF_ITEM_ACTIVITY_STATUSES WIAS,
WF_PROCESS_ACTIVITIES WPA,
OE_ORDER_LINES OOL,
OE_ORDER_HEADERS OOH
WHERE WIAS.ITEM_TYPE = 'OEOL'
AND WPA.ACTIVITY_NAME = 'PURCHASE RELEASE ELIGIBLE'
AND WPA.INSTANCE_ID = WIAS.PROCESS_ACTIVITY
AND WIAS.ACTIVITY_STATUS = 'COMPLETE'
AND WIAS.ITEM_KEY = TO_CHAR(OOL.LINE_ID)
AND OOH.PAYMENT_TYPE_CODE = 'CREDIT_CARD'
AND OOL.HEADER_ID = OOH.HEADER_ID
AND OOH.HEADER_ID = p_header_id;
SELECT 1 + l_ship_rule_count /* MOAC_SQL_CHANGE */
INTO l_ship_rule_count
FROM OE_ORDER_HEADERS_ALL OOH
WHERE OOH.PAYMENT_TYPE_CODE = 'CREDIT_CARD'
AND OOH.HEADER_ID = p_header_id
AND EXISTS
(SELECT 'Purchase Released'
FROM OE_ORDER_LINES OOL, OE_DROP_SHIP_SOURCES ODSS
WHERE OOL.HEADER_ID = OOH.HEADER_ID
AND ODSS.LINE_ID = OOL.LINE_ID
AND ODSS.HEADER_id = OOL.HEADER_ID);
oe_debug_pub.add( 'OEXPVPMB: SELECTING THE ENTRY RULE' ) ;
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM oe_order_types_v
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.add( 'OEXPVPMB: SELECTING THE SHIPPING RULE' ) ;
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.add( 'OEXPVPMB: SELECTING THE PACKING RULE' ) ;
SELECT NVL(PACKING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(PACKING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND PACKING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.add( 'OEXPVPMB: SELECTING THE PICKING RULE' ) ;
SELECT NVL(PICKING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V
WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
SELECT NVL(PICKING_CREDIT_CHECK_RULE_ID, -1)
INTO l_credit_check_rule_id
FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
AND PICKING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
oe_debug_pub.add( 'OEXPVPMB: CALLING UPDATE AUTH INFO TO UPDATE THE APPROVAL INFO TO NULL' ) ;
OE_Verify_Payment_PUB.Update_Authorization_Info
( p_header_rec.header_id
, NULL
, l_auth_code
, l_trxn_date
, l_msg_count
, l_msg_data
, l_return_status
);
l_sql_stmt := 'SELECT IT.TANGIBLEID, IT.AMOUNT
FROM IBY_TANGIBLE IT
WHERE IT.REFINFO = :ref_info
AND EXISTS
(SELECT ''Voice Auth Requested''
FROM IBY_TRANS_ALL_V A
WHERE A.REQTYPE = ''ORAPMTREQ''
AND A.STATUS = 21
AND A.TANGIBLEID = IT.TANGIBLEID)
AND NOT
EXISTS
(SELECT ''Voice Auth Successful''
FROM IBY_TRANS_ALL_V B
WHERE B.REQTYPE = ''ORAPMTREQ''
AND B.STATUS = 0
AND B.TANGIBLEID = IT.TANGIBLEID)';
SELECT
name
, merchant_ref
INTO
p_pay_method_name
, p_merchant_ref
FROM AR_RECEIPT_METHODS
WHERE RECEIPT_METHOD_ID = p_pay_method_id
AND SYSDATE >= NVL(START_DATE, SYSDATE)
AND SYSDATE <= NVL(END_DATE, SYSDATE)
AND PAYMENT_TYPE_CODE = 'CREDIT_CARD';
oe_debug_pub.add( 'OEXPVPMB: GOING DIRECTLY TO UPDATE AUTH' ) ;
goto UPDATE_AUTH;
SELECT TO_CHAR(OE_IPAYMENT_TANGIBLE_S.NEXTVAL)||'_ONT'
INTO l_tangible_id
FROM DUAL ;
SELECT ADDRESS_LINE_1
, ADDRESS_LINE_2
, ADDRESS_LINE_3
, TOWN_OR_CITY
, COUNTY
, STATE
, COUNTRY
, POSTAL_CODE
, CUSTOMER_ID
INTO l_payer_address1
, l_payer_address2
, l_payer_address3
, l_payer_city
, l_payer_county
, l_payer_state
, l_payer_country
, l_payer_postalcode
, l_payer_cust_id
FROM OE_INVOICE_TO_ORGS_V
WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
** Ship To Address is now selected from the table.
l_header_val_rec := OE_Header_Util.Get_Values( p_header_rec => p_header_rec );
SELECT ADDRESS_LINE_1
, ADDRESS_LINE_2
, ADDRESS_LINE_3
, TOWN_OR_CITY
, POSTAL_CODE
, COUNTRY
INTO l_ship_address1
, l_ship_address2
, l_ship_address3
, l_ship_city
, l_ship_postalcode
, l_ship_country
FROM OE_SHIP_TO_ORGS_V
WHERE ORGANIZATION_ID = p_header_rec.ship_to_org_id;
SELECT TO_CHAR(sysdate, 'HH24:MI')
INTO l_time_of_purchase
FROM DUAL ;
<>
-- Update Payment Amount and Authorization Code and DATE
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'OEXPVPMB: BEFORE UPDATING AUTHORIZATION INFO' ) ;
OE_Verify_Payment_PUB.Update_Authorization_Info
( p_header_rec.header_id
, l_order_value
, l_auth_code
, l_trxn_date
, l_msg_count
, l_msg_data
, l_return_status
);
SELECT receipt_method_id
INTO l_pay_method_id
FROM ra_cust_receipt_methods rm
WHERE rm.customer_id = p_header_rec.sold_to_org_id
AND rm.SITE_USE_ID = NVL( p_header_rec.invoice_to_org_id, -1)
AND sysdate BETWEEN rm.start_date AND NVL(rm.end_date, sysdate)
AND primary_flag = 'Y';
SELECT receipt_method_id
INTO l_pay_method_id
FROM ra_cust_receipt_methods rm
WHERE rm.customer_id = p_header_rec.sold_to_org_id
AND rm.SITE_USE_ID IS NULL
AND sysdate BETWEEN rm.start_date AND NVL(rm.end_date, sysdate)
AND primary_flag = 'Y';
SELECT
name
, null
INTO
p_pay_method_name
, p_merchant_id
FROM AR_RECEIPT_METHODS
WHERE RECEIPT_METHOD_ID = p_pay_method_id
AND SYSDATE >= NVL(START_DATE, SYSDATE)
AND SYSDATE <= NVL(END_DATE, SYSDATE)
AND PAYMENT_TYPE_CODE = 'CREDIT_CARD';
l_sql_stmt := 'SELECT ''Y'' FROM IBY_TRANS_ALL_V
WHERE TRANSACTIONID = :trxn_id
AND ((REQTYPE = ''ORAPMTCAPTURE'')
OR (REQTYPE = ''ORAPMTREQ'' AND AUTHTYPE = ''AUTHCAPTURE''))
AND NVL(AMOUNT, 0) > 0
AND ROWNUM = 1';
** updated on the order header due to some errors.
**
IF p_header_rec.credit_card_approval_code IS NULL
OR p_automatic_auth = 'N' THEN
-- Call Last Current Auth
OE_DEBUG_PUB.ADD('OEXPVPMB: Before calling Fetch Last Auth');
oe_debug_pub.add( 'OEXPVPMB: SQL STMT TO UPDATE REFINFO WITH HEADER_ID' ) ;
l_sql_stmt := 'UPDATE IBY_TANGIBLE IT
SET IT.REFINFO = :ref_info1
WHERE IT.TANGIBLEID IN
(SELECT DISTINCT ITAV.TANGIBLEID
FROM IBY_TRANS_ALL_V ITAV
WHERE ITAV.REQTYPE = ''ORAPMTREQ''
AND ITAV.ECAPPID != 660
AND ITAV.AUTHCODE = :auth_code
AND trunc(ITAV.UPDATEDATE) = trunc(:auth_date) --5932506
AND (ITAV.REFINFO IS NULL
OR ITAV.REFINFO != :ref_info2))';
/* UPDATE IBY_TANGIBLE IT
SET IT.REFINFO = l_ref_info
WHERE IT.TANGIBLEID IN
(SELECT DISTINCT ITAV.TANGIBLEID
FROM IBY_TRANS_ALL_V ITAV
WHERE ITAV.REQTYPE = 'ORAPMTREQ'
AND ITAV.ECAPPID <> 660
AND ITAV.AUTHCODE = l_credit_card_approval_code
AND trunc(ITAV.UPDATEDATE) = trunc(l_credit_card_approval_date) --5932506
AND (ITAV.REFINFO IS NULL
OR ITAV.REFINFO <> l_ref_info));
UPDATE IBY_TANGIBLE IT
SET IT.REFINFO = l_ref_info
WHERE IT.TANGIBLEID IN
(SELECT DISTINCT ITAV.TANGIBLEID
FROM IBY_TRANS_ALL_V ITAV
WHERE ITAV.REQTYPE = 'ORAPMTREQ'
AND ITAV.AUTHCODE = l_credit_card_approval_code
AND trunc(ITAV.UPDATEDATE) = trunc(l_credit_card_approval_date)
AND (( ITAV.ECAPPID = 697
AND ITAV.REFINFO = p_header_rec.source_document_id)
OR ( ITAV.ECAPPID NOT IN (660, 697, 222, 673)
AND NVL(ITAV.REFINFO, -99) <> l_ref_info)));
oe_debug_pub.add( 'OEXPVPMB: UPDATED '||SQL%ROWCOUNT||' ROWS.' ) ;
l_sql_stmt := 'SELECT TRANSACTIONID, TANGIBLEID
FROM (SELECT A.TRANSACTIONID, A.TANGIBLEID
FROM IBY_TRANS_ALL_V A
WHERE A.AUTHCODE = :auth_code
AND trunc(A.UPDATEDATE) = trunc(:auth_date) --5932506
AND A.REQTYPE = ''ORAPMTREQ''
AND A.STATUS = 0
AND A.REFINFO = :ref_info
AND NOT
EXISTS (SELECT ''Trxn Already Voided''
FROM IBY_TRANS_ALL_V B
WHERE B.TANGIBLEID = A.TANGIBLEID
AND B.REQTYPE = ''ORAPMTVOID''
AND B.STATUS = 0)
ORDER BY A.UPDATEDATE DESC)
WHERE ROWNUM = 1';
l_sql_stmt := 'SELECT TRANSACTIONID, TANGIBLEID, AUTHCODE, UPDATEDATE, AMOUNT
FROM (SELECT A.TRANSACTIONID, A.TANGIBLEID, A.AUTHCODE, A.UPDATEDATE, A.AMOUNT
FROM IBY_TRANS_ALL_V A
WHERE A.REFINFO = :ref_info
AND A.REQTYPE = ''ORAPMTREQ''
AND A.ECAPPID = 660
AND A.STATUS = 0
AND A.AUTHCODE IS NOT NULL
AND NOT
EXISTS (SELECT ''Trxn Already Captured or Voided''
FROM IBY_TRANS_ALL_V B
WHERE B.TANGIBLEID = A.TANGIBLEID
AND ((B.REQTYPE = ''ORAPMTVOID'')
OR (B.REQTYPE = ''ORAPMTCAPTURE'')
OR (B.REQTYPE = ''ORAPMTREQ''
AND B.AUTHTYPE = ''AUTHCAPTURE''))
AND B.STATUS = 0)
ORDER BY A.UPDATEDATE DESC)
WHERE ROWNUM = 1';
Updates Order Header with Authorized Amount, Authorization Code and DATE
----------------------------------------------------------------------*/
PROCEDURE Update_Authorization_Info
( p_header_id IN NUMBER
, p_auth_amount IN NUMBER
, p_auth_code IN VARCHAR2
, p_auth_date IN DATE
, p_msg_count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_index NUMBER := 1;
SAVEPOINT Update_Authorization_Info;
oe_debug_pub.add( 'OEXPVPMB: IN UPDATE AUTHORIZATION INFORMATION' ) ;
l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_header_rec.last_update_date := SYSDATE;
l_header_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
update oe_order_headers
set credit_card_approval_code = p_auth_code
, credit_card_approval_date = p_auth_date
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, lock_control = lock_control + 1
where header_id = p_header_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_header_rec => l_header_rec
, p_old_header_rec => l_old_header_rec
, p_header_id => l_header_rec.header_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_rec.last_updated_by:=l_header_rec.last_updated_by;
OE_ORDER_UTIL.g_header_rec.last_update_login:=l_header_rec.last_update_login;
OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
ROLLBACK TO Update_Authorization_Info;
ROLLBACK TO Update_Authorization_Info;
ROLLBACK TO Update_Authorization_Info;
, 'Update_Authorization_Info'
);
END Update_Authorization_Info ;
l_sql_stmt := 'SELECT NVL(SUM(AMOUNT), 0)
FROM IBY_TRANS_ALL_V
WHERE REFINFO = :ref_info
AND STATUS = 0
AND ((REQTYPE = ''ORAPMTCAPTURE'')
OR (REQTYPE = ''ORAPMTREQ'' AND AUTHTYPE = ''AUTHCAPTURE''))';
SELECT line_id
,trxn_extension_id
/*,credit_card_number --R12 CC Encryption
,credit_card_holder_name
,credit_card_expiration_date
,credit_card_approval_code
,credit_card_approval_date
,tangible_id*/
,receipt_method_id
,payment_number
,defer_payment_processing_flag
FROM oe_payments
WHERE payment_type_code = 'CREDIT_CARD'
AND header_id = p_header_id
AND line_id IS NOT NULL;
SELECT payment_number
,trxn_extension_id
/*,credit_card_number --R12 CC Encryption
,credit_card_holder_name
,credit_card_expiration_date
,credit_card_approval_code
,credit_card_approval_date
,tangible_id*/
,receipt_method_id
,defer_payment_processing_flag
FROM oe_payments
WHERE payment_type_code = 'CREDIT_CARD'
AND payment_collection_event = 'INVOICE'
AND line_id is null
AND header_id = p_header_id;
SELECT line_id
,trxn_extension_id
/*,credit_card_number --R12 CC Encryption
,credit_card_holder_name
,credit_card_expiration_date
,credit_card_approval_code
,credit_card_approval_date
,tangible_id*/
,receipt_method_id
,payment_number
,defer_payment_processing_flag
FROM oe_payments
WHERE payment_type_code = 'CREDIT_CARD'
AND line_id = p_line_id
AND header_id = p_header_id;
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND payment_type_code IS NOT NULL
AND payment_type_code <> 'COMMITMENT'
AND nvl(invoice_interface_status_code,'NO') <> 'YES';
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND nvl(invoice_interface_status_code,'NO') = 'YES';
SELECT invoice_interface_status_code
INTO l_inv_interface_status_code
FROM oe_order_lines_all
WHERE line_id = l_payments_tbl(I).line_id;
SELECT sum(nvl(payment_amount, 0))
INTO l_prepaid_total
FROM oe_payments
WHERE payment_collection_event = 'PREPAY'
AND header_id = p_header_rec.header_id;
SELECT invoice_to_org_id, ship_to_org_id
INTO l_invoice_to_org_id,l_ship_to_org_id
FROM oe_order_lines_all
WHERE line_id = l_payments_tbl(I).line_id;
SELECT invoice_to_org_id, ship_to_org_id
INTO l_invoice_to_org_id,l_ship_to_org_id
FROM oe_order_headers_all
WHERE header_id = p_header_rec.header_id;
goto Update_Verify_Hold;
SELECT receipt_method_id
INTO l_pay_method_id
FROM oe_payment_types_all
WHERE payment_type_code = 'CREDIT_CARD'
AND nvl(org_id, -99) = nvl(p_header_rec.org_id, -99);
goto Update_Verify_Hold;
goto Update_Verify_Hold;
SELECT invoice_to_org_id, ship_to_org_id
INTO l_invoice_to_org_id,l_ship_to_org_id
FROM oe_order_lines_all
WHERE line_id = l_payments_tbl(I).line_id;
SELECT invoice_to_org_id, ship_to_org_id
INTO l_invoice_to_org_id,l_ship_to_org_id
FROM oe_order_headers_all
WHERE header_id = p_header_rec.header_id;
select trxn_extension_id
into l_trxn_extension_id
FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_rec.header_id and line_id is null
AND nvl(payment_collection_event,'PREPAY') = 'INVOICE'; --bug 5020737
select trxn_extension_id
into l_trxn_extension_id
FROM OE_PAYMENTS
WHERE line_id = l_payments_tbl(I).line_id
AND header_id = p_header_rec.header_id
AND payment_type_code <> 'COMMITMENT';
SELECT invoice_to_org_id, ship_from_org_id, ship_to_org_id
INTO x_invoice_to_org_id, x_ship_from_org_id, x_ship_to_org_id
FROM oe_order_lines_all
WHERE line_id = l_payments_tbl(I).line_id;
goto Update_Verify_Hold;
goto Update_Verify_Hold;
goto Update_Verify_Hold;
goto Update_Verify_Hold;
SELECT TO_CHAR(sysdate, 'HH24:MI')
INTO l_time_of_purchase
FROM DUAL ;
/*Select party_site.party_id, acct_site.cust_account_id
Into l_party_id, l_cust_account_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_CUST_ACCT_SITES ACCT_SITE --Verify
Where SITE.SITE_USE_ID = p_header_rec.invoice_to_org_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
SELECT hca.party_id,acctsite.cust_account_id --acct_site. cust_account_id, site.cust_acct_site_id
INTO l_party_id, l_cust_account_id
FROM hz_cust_acct_sites_all acctsite, hz_cust_site_uses_all site, hz_cust_accounts_all hca
WHERE SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCTSITE.CUST_ACCT_SITE_ID
AND ACCTSITE.cust_account_id = HCA.cust_account_id
AND SITE.SITE_USE_ID = p_header_rec.invoice_to_org_id;
UPDATE oe_order_headers_all
SET payment_amount = l_amount.value
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, lock_control = lock_control + 1
WHERE header_id = p_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
UPDATE oe_order_headers_all
SET payment_amount = l_amount.value
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, lock_control = lock_control + 1
WHERE header_id = p_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
<>
-- Update Payment Amount and Authorization Code and DATE
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'OEXPVPMB: BEFORE UPDATING AUTHORIZATION INFO FOR MULTIPLE PAYMENT.' ) ;
OE_Verify_Payment_PUB.Update_AuthInfo_for_MultiPmnts
( p_header_rec.header_id
, l_amount_to_authorize
, l_auth_code
, l_trxn_date
, l_tangible_id
, l_payments_tbl(I).line_id
, l_payments_tbl(I).payment_number
, l_msg_count
, l_msg_data
, l_return_status
);
<>
-- To either release or apply the verify hold.
p_result_out := l_result_status;
SELECT nvl(settled_flag, 'N')
INTO l_settled_flag
FROM iby_trxn_extensions_v
WHERE trxn_extension_id = p_trxn_extension_id;
UPDATE oe_payments
SET trxn_extension_id = l_trxn_extension_id
WHERE trxn_extension_id = p_trxn_extension_id
AND header_id = p_header_id --bug 5209584
AND nvl(line_id,-1) = nvl(p_line_id,-1)
AND payment_number = p_payment_number;
SELECT effective_auth_amount
INTO l_effective_auth_amount
FROM iby_trxn_ext_auths_v
WHERE trxn_extension_id = p_trxn_extension_id
AND nvl(authorization_amount,0) > 0
AND authorization_status=0;
UPDATE oe_payments
SET trxn_extension_id = l_trxn_extension_id
WHERE trxn_extension_id = p_trxn_extension_id
AND header_id = p_header_id --bug 5209584
AND nvl(line_id,-1) = nvl(p_line_id,-1)
AND payment_number = p_payment_number;
SELECT nvl(ool.tax_value,0)
, nvl(ool.Ordered_Quantity,0) * ool.unit_selling_price
, header_id
, payment_term_id
INTO l_tax_value
, l_extended_price
, l_header_id
, l_payment_term_id
FROM oe_order_lines_all ool
WHERE ool.line_id = p_line_id
-- AND ool.open_flag = 'Y' --bug3225795
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(nvl(op.commitment_applied_amount,0))
INTO l_commitment_applied
FROM oe_payments op
WHERE op.line_id = p_line_id
AND op.header_id = p_header_id
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti, oe_order_lines_all ool
WHERE ool.line_id = op.line_id
AND mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price)
INTO l_commitment_applied
FROM oe_order_lines_all ool
WHERE ool.line_id = p_line_id
AND ool.commitment_id is not null
--AND ool.open_flag = 'Y'
--AND nvl(ool.invoice_interface_status_code,'NO') <> 'YES' --bug3225795
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,(-1) * (P.OPERAND)),
(-1) * (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
(L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
)
)
INTO l_charge_amount
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.LINE_ID = p_line_id
AND P.LINE_ID = L.LINE_ID
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y'
--Bug 6072691
--Uninvoiced and invoiced charges will be returned in the first two conditions
--Last condition will handle all the other amount types
AND ( (nvl(p_amount_type,'OTHERS') = 'UNINV_CHARGES'
AND nvl(p.invoiced_flag,'N') = 'N')
OR
(nvl(p_amount_type,'OTHERS') = 'INV_CHARGES'
AND nvl(p.invoiced_flag,'N') = 'Y')
OR
(nvl(p_amount_type,'OTHERS') NOT IN('UNINV_CHARGES','INV_CHARGES'))
);
SELECT nvl(ool.tax_value,0)
, nvl(ool.Ordered_Quantity,0) * ool.unit_selling_price
, header_id
, payment_term_id
INTO l_tax_value
, l_extended_price
, l_header_id
, l_payment_term_id
FROM oe_order_lines_all ool
WHERE ool.line_id = p_line_id
-- AND ool.open_flag = 'Y'
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(nvl(op.commitment_applied_amount,0))
INTO l_commitment_applied
FROM oe_payments op
WHERE op.line_id = p_line_id
AND op.header_id = p_header_id
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti, oe_order_lines_all ool
WHERE ool.line_id = op.line_id
AND mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price)
INTO l_commitment_applied
FROM oe_order_lines_all ool
WHERE ool.line_id = p_line_id
AND ool.commitment_id is not null
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,(-1) * (P.OPERAND)),
(-1) * (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
(L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
)
)
INTO l_charge_amount
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.LINE_ID = p_line_id
AND P.LINE_ID = L.LINE_ID
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y';
SELECT
SUM(ROUND(nvl(ool.tax_value,0),OE_ORDER_UTIL.G_Precision))
, SUM(ROUND(nvl(ool.Ordered_Quantity,0)
*(ool.unit_selling_price),OE_ORDER_UTIL.G_Precision))
INTO
l_tax_total
, l_order_total
FROM oe_order_lines_all ool
WHERE ool.header_id = p_header_id
AND ool.open_flag = 'Y'
AND nvl(ool.invoice_interface_status_code,'NO') <> 'YES' --bug3225795
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(ROUND(nvl(op.commitment_applied_amount,0),OE_ORDER_UTIL.G_Precision))
INTO l_commitment_total
FROM oe_payments op,
oe_order_lines_all ool --bug3225795
WHERE op.header_id = p_header_id
AND ool.header_id = p_header_id --bug3225795
AND nvl(ool.invoice_interface_status_code,'NO') <> 'YES' --bug3225795
AND ool.line_id=op.line_id --bug3225795
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti, oe_order_lines_all ool
WHERE ool.line_id = op.line_id
AND mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(ROUND(nvl(ool.Ordered_Quantity,0) * (ool.unit_selling_price),OE_ORDER_UTIL.G_Precision))
INTO l_commitment_total
FROM oe_order_lines_all ool
WHERE ool.header_id = p_header_id
AND ool.commitment_id is not null
AND ool.open_flag = 'Y'
AND nvl(ool.invoice_interface_status_code,'NO') <> 'YES' --bug3225795
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(
ROUND(DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',(-1) * P.OPERAND,P.OPERAND),OE_ORDER_UTIL.G_Precision)
)
INTO l_chgs_wo_line_id
FROM OE_PRICE_ADJUSTMENTS P
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID IS NULL
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y'
AND NVL(P.INVOICED_FLAG, 'N') = 'N';
SELECT SUM(
ROUND(DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
(-1) * (P.OPERAND),
(-1) * (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
P.OPERAND,
(L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
),OE_ORDER_UTIL.G_Precision)
)
INTO l_chgs_w_line_id
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID = L.LINE_ID
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y'
AND L.header_id = p_header_id
AND L.open_flag = 'Y'
AND nvl(L.invoice_interface_status_code,'NO') <> 'YES' --bug3225795
AND L.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM MTL_SYSTEM_ITEMS MTI
WHERE MTI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND MTI.ORGANIZATION_ID = NVL(L.SHIP_FROM_ORG_ID,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID', L.org_id))
AND (MTI.INVOICEABLE_ITEM_FLAG = 'N'
OR MTI.INVOICE_ENABLED_FLAG = 'N'));
Updates Order Line with Authorized Amount, Authorization Code and DATE
----------------------------------------------------------------------*/
PROCEDURE Update_AuthInfo_for_MultiPmnts
( p_header_id IN NUMBER
, p_auth_amount IN NUMBER
, p_auth_code IN VARCHAR2
, p_auth_date IN DATE
, p_tangible_id IN VARCHAR2
, p_line_id IN NUMBER
, p_payment_number IN NUMBER
, p_msg_count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
, p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
)
IS
l_index NUMBER := 1;
SAVEPOINT Update_AuthInfo_for_MultiPmnts;
oe_debug_pub.add( 'OEXPVPMB: IN UPDATE AUTHORIZATION INFORMATION' ) ;
l_Line_Payment_rec.last_updated_by := FND_GLOBAL.USER_ID;
l_Line_Payment_rec.last_update_date := SYSDATE;
l_Line_Payment_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
l_Line_Payment_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
update oe_payments
set credit_card_approval_code = p_auth_code
, credit_card_approval_date = p_auth_date
, tangible_id = p_tangible_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, lock_control = lock_control + 1
where nvl(payment_number, -1) = nvl(p_payment_number, -1)
and line_id = p_line_id
and header_id = p_header_id;
update oe_payments
set credit_card_approval_code = p_auth_code
, credit_card_approval_date = p_auth_date
, tangible_id = p_tangible_id
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_update_login = FND_GLOBAL.LOGIN_ID
, lock_control = lock_control + 1
where line_id IS NULL
and payment_type_code = 'CREDIT_CARD'
and payment_collection_event = 'INVOICE'
and nvl(payment_number, -1) = nvl(p_payment_number, -1)
and header_id = p_header_id;
update oe_order_headers_all
set credit_card_approval_code = p_auth_code
, credit_card_approval_date = p_auth_date
where header_id = p_header_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_header_rec => l_header_rec
, p_old_header_rec => l_old_header_rec
, p_header_id => l_header_rec.header_id
, x_index => l_notify_index
, x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || L_RETURN_STATUS ) ;
OE_ORDER_UTIL.g_header_rec.last_updated_by:=l_header_rec.last_updated_by;
OE_ORDER_UTIL.g_header_rec.last_update_login:=l_header_rec.last_update_login;
OE_ORDER_UTIL.g_header_rec.last_update_date:=l_header_rec.last_update_date;
ROLLBACK TO Update_AuthInfo_for_MultiPmnts;
ROLLBACK TO Update_AuthInfo_for_MultiPmnts;
ROLLBACK TO Update_AuthInfo_for_MultiPmnts;
, 'Update_AuthInfo_for_MultiPmnts'
);
END Update_AuthInfo_for_MultiPmnts ;
SELECT line_number,
shipment_number,
option_number,
component_number,
service_number
INTO l_line_number,
l_shipment_number,
l_option_number,
l_component_number,
l_service_number
from oe_order_lines_all
where line_id = p_line_id;
Select hca.party_id, acct_site.cust_account_id
Into l_party_id, l_cust_account_id
From HZ_CUST_SITE_USES_ALL SITE,
HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_CUST_ACCOUNTS_ALL HCA
Where SITE.SITE_USE_ID = p_site_use_id
AND SITE.SITE_USE_CODE = 'BILL_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
select instr_assignment_id
into l_assign_id
from IBY_TRXN_EXTENSIONS_V
where trxn_extension_id = p_trxn_extension_id;