[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning
into l_description
from ar_lookups
where lookup_type = 'COMMITMENT_DESCRIPTION'
and lookup_code = 'GENERIC'
and sysdate between start_date_active and nvl(end_date_active, sysdate)
and enabled_flag = 'Y';
SELECT name
INTO l_organization_name
FROM hr_operating_units
WHERE organization_id = l_org_id;
SELECT RECEIVABLES_TRX_ID
INTO l_receivables_trx_id
FROM ar_receivables_trx_all
WHERE status = 'A' AND
TYPE ='CM_REFUND'AND ORG_ID = l_org_id;
SELECT RECEIVABLES_TRX_ID
INTO l_receivables_trx_id
FROM ar_receivables_trx_all
WHERE status = 'A' AND
TYPE ='CCREFUND'AND ORG_ID = l_org_id;
SELECT name
INTO l_batch_source_name
FROM ra_batch_sources_all
WHERE batch_source_id = p_in_rec.batch_source_id
AND org_id = l_org_id
AND Trunc(SYSDATE) BETWEEN start_date AND Nvl(end_date,Trunc(SYSDATE+1));
SELECT rx.TRX_NUMBER,
rl.customer_trx_line_id
INTO x_out_rec.cm_trx_number,
x_out_rec.cm_customer_trx_line_id
FROM ra_customer_trx_all rx,
ra_customer_trx_lines_all rl
WHERE rx.customer_trx_id = x_cm_customer_trx_id
AND rl.customer_trx_id = rx.customer_trx_id;
SELECT HZ_ACCOUNT_NUM_S.NEXTVAL INTO l_cust_account_rec.account_number FROM DUAL;
update_trx_status_lookup_code(l_emd_transactions_info.emd_transaction_id);
UPDATE pon_emd_transactions
SET CUST_TRX_NUMBER = l_deposit_out_record.deposit_trx_number,
CUST_TRX_ID = l_deposit_out_record.deposit_trx_id,
CUST_TRX_LINE_ID = l_deposit_out_record.deposit_trx_line_id,
POSTED_TO_FINANCE = 'Y',
STATUS_LOOKUP_CODE = 'RECEIVED'
WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
AND auction_header_id = l_emd_transactions_info.auction_header_id;
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
update_trx_status_lookup_code(l_emd_transactions_info.emd_transaction_id);
UPDATE pon_emd_transactions
SET CUST_TRX_NUMBER = l_cm_out_record.cm_trx_number,
CUST_TRX_ID = l_cm_out_record.cm_customer_trx_id,
CUST_TRX_LINE_ID = l_cm_out_record.cm_customer_trx_line_id,
APPLICATION_REF_ID =l_refund_activity_out_rec.APPLICATION_REF_ID,
application_ref_type = l_refund_activity_out_rec.application_ref_type,
application_ref_num = l_refund_activity_out_rec.application_ref_num,
POSTED_TO_FINANCE = 'Y',
STATUS_LOOKUP_CODE = 'REFUNDED'
WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
AND auction_header_id = l_emd_transactions_info.auction_header_id;
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
update_trx_status_lookup_code(l_emd_transactions_info.emd_transaction_id);
SELECT SUBSEQUENT_TRX_TYPE_ID
INTO l_inv_trx_type_id
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = l_pon_emd_fin_parameters.RA_TRX_TYPE_ID
AND org_id = l_org_id;
insert_and_call_autoinvoice(l_forfeit_info_in_rec,
l_emd_transactions,
x_return_status);
print_log('After executing insert_and_call_autoinvoice');
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
l_searched := fnd_msg_pub.delete_msg;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
SELECT hca.cust_account_id,
hp.party_name
INTO x_cust_account_id,
x_party_name
FROM hz_cust_accounts hca,
hz_parties hp
WHERE Nvl(hca.customer_type,'I') = 'R'
AND hca.party_id = hp.party_id
AND hca.account_name = hp.party_name
AND hca.party_id = p_party_id
AND ROWNUM =1;
SELECT party_name
INTO x_party_name
FROM hz_parties
WHERE party_id =p_party_id;
SELECT hps.party_site_id,
hps.party_site_name,
loc.city
INTO x_party_site_id,
x_party_site_name,
x_location
FROM hz_party_sites hps,
ap_supplier_sites_all vs,
hz_locations loc
WHERE vs.vendor_site_id = p_vendor_site_id
AND vs.party_site_id = hps.party_site_id
AND hps.location_id = loc.location_id
AND hps.party_id = p_party_id
AND vs.org_id = p_org_id;
SELECT hps.party_site_id,
hps.party_site_name,
loc.city
INTO x_party_site_id,
x_party_site_name,
x_location
FROM hz_party_sites hps,
hz_locations loc
WHERE hps.party_id = p_party_id
AND hps.location_id = loc.location_id
AND hps.identifying_address_flag = 'Y'
AND ROWNUM =1;
SELECT site.cust_acct_site_id,
USE.site_use_id
INTO x_cust_acct_site_id,
x_site_use_id
FROM HZ_CUST_ACCT_SITES_ALL site,
HZ_CUST_SITE_USES_ALL use
WHERE cust_account_id = x_cust_account_id
AND site.org_id = p_org_id
AND site.party_site_id = x_party_site_id
AND site.cust_acct_site_id = USE.cust_acct_site_id
AND USE.org_id = site.org_id
AND USE.site_use_code = 'BILL_TO'
AND ROWNUM = 1 ;
SELECT instrument_payment_use_id
INTO x_account_assignment_id
FROM iby_pmt_instr_uses_all
WHERE payment_flow = 'FUNDS_CAPTURE'
AND ext_pmt_party_id = x_payer_attribs_id
AND instrument_id = x_acct_id;
SELECT cc.instrid,
cc.expirydate,
'Y'
INTO x_instr_id,
l_expiry_date,
l_card_exists
FROM IBY_CREDITCARD cc,
HZ_PARTIES hzp,
HZ_PARTY_SITES hzps,
HZ_PARTY_SITE_USES hzpsu,
IBY_CREDITCARD_ISSUERS_VL ccissuers
WHERE ccissuers.card_issuer_code = cc.card_issuer_code
AND cc.CARD_OWNER_ID = hzp.party_id
AND cc.ADDRESSID = hzpsu.party_site_use_id(+)
AND hzpsu.party_site_id = hzps.party_site_id(+)
AND cc.CCNUMBER = p_credit_card_num
AND cc.CARD_ISSUER_CODE = p_card_issuer_code
AND cc.CHNAME = p_name_on_card
AND cc.card_owner_id = p_party_id;
SELECT instrument_payment_use_id
INTO x_card_assignment_id
FROM iby_pmt_instr_uses_all
WHERE payment_flow = 'FUNDS_CAPTURE'
AND ext_pmt_party_id = x_payer_attribs_id
AND instrument_id = x_instr_id;
SELECT CUST_TRX_NUMBER,
CUST_TRX_ID,
CUST_TRX_LINE_ID,
ORG_ID,
PAYMENT_TYPE_CODE
INTO x_parent_trx_number,
x_parent_trx_id,
x_parent_trx_line_id,
x_org_id,
x_deposit_payment_type_code
FROM pon_emd_transactions
WHERE emd_transaction_id = p_parent_emd_transaction_id;
SELECT Max(acr.cash_receipt_id),
Max(arp.receivable_application_id)
INTO x_cash_receipt_id,
x_receivable_app_id
from ar_receivable_applications_all arp,
ra_customer_trx_all rct,
ra_cust_trx_types_all ctyp,
ar_cash_receipts_all acr
where arp.applied_customer_trx_id = p_parent_trx_id
AND arp.org_id = p_org_id
AND arp.status = 'APP'
AND arp.applied_customer_trx_id = rct.customer_trx_id
AND arp.org_id = rct.org_id
AND rct.CUST_TRX_TYPE_ID = ctyp.CUST_TRX_TYPE_ID
AND rct.org_id = ctyp.org_id
AND ctyp.type = 'DEP'
AND arp.cash_receipt_id= acr.cash_receipt_id
AND arp.amount_applied > 0;
SELECT acr.receipt_number,
acrh.status
INTO x_receipt_num,
x_receipt_status
FROM ar_cash_Receipt_history_all acrh,
ar_cash_receipts_all acr
WHERE acrh.cash_receipt_id =acr.cash_receipt_id
AND acrh.current_record_flag = 'Y'
AND acrh.status = 'CLEARED'
AND acr.cash_receipt_id = x_cash_receipt_id;
SELECT ORG_ID,
AUCTION_STATUS,
DOCUMENT_NUMBER
INTO l_org_id,
l_auction_status,
l_document_num
FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = p_emd_transaction_rec.auction_header_id;
SELECT period_name
INTO l_period_name
FROM gl_period_statuses
WHERE application_id = 200
AND set_of_books_id = ARP_GLOBAL.sysparam.set_of_books_id
AND trunc(p_emd_transaction_rec.transaction_date)
between start_date and END_date
AND closing_status in ('O', 'F')
AND NVL(adjustment_period_flag, 'N') = 'N';
SELECT name
INTO l_organization_name
FROM hr_operating_units
WHERE organization_id = p_org_id;
SELECT TYPE,
CREDIT_MEMO_TYPE_ID,
SUBSEQUENT_TRX_TYPE_ID,
POST_TO_GL,
ACCOUNTING_AFFECT_FLAG,
CREATION_SIGN,
NATURAL_APPLICATION_ONLY_FLAG,
ALLOW_OVERAPPLICATION_FLAG,
DEFAULT_STATUS,
TAX_CALCULATION_FLAG,
EXCLUDE_FROM_LATE_CHARGES,
GL_ID_REC,
GL_ID_REV,
START_DATE,
END_DATE,
NAME
INTO l_type,
l_credit_memo_type_id,
l_subsequent_trx_type_id,
l_post_to_gl,
l_accounting_affect_flag,
l_creation_sign,
l_natural_app_only_flag,
l_allow_overapplication_flag,
l_default_status,
l_tax_calculation_flag,
l_exclude_from_late_charges,
l_gl_id_rec,
l_gl_id_rev,
l_start_date,
l_end_date,
l_deposit_trx_name
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = p_ra_trx_type_id
AND org_id = p_org_id;
SELECT CREDIT_MEMO_TYPE_ID
INTO l_credit_memo_type_id
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = p_ra_trx_type_id
AND org_id = p_org_id;
SELECT TYPE,
POST_TO_GL,
ACCOUNTING_AFFECT_FLAG,
CREATION_SIGN,
NATURAL_APPLICATION_ONLY_FLAG,
ALLOW_OVERAPPLICATION_FLAG,
DEFAULT_STATUS,
TAX_CALCULATION_FLAG,
EXCLUDE_FROM_LATE_CHARGES,
GL_ID_REC,
GL_ID_REV,
START_DATE,
END_DATE,
NAME
INTO l_type,
l_post_to_gl,
l_accounting_affect_flag,
l_creation_sign,
l_natural_app_only_flag,
l_allow_overapplication_flag,
l_default_status,
l_tax_calculation_flag,
l_exclude_from_late_charges,
l_gl_id_rec,
l_gl_id_rev,
l_start_date,
l_end_date,
l_cm_trx_name
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = l_credit_memo_type_id
AND org_id = p_org_id;
SELECT SUBSEQUENT_TRX_TYPE_ID
INTO l_subsequent_trx_type_id
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = p_ra_trx_type_id
AND org_id = p_org_id;
SELECT TYPE,
POST_TO_GL,
ACCOUNTING_AFFECT_FLAG,
CREATION_SIGN,
NATURAL_APPLICATION_ONLY_FLAG,
ALLOW_OVERAPPLICATION_FLAG,
DEFAULT_STATUS,
TAX_CALCULATION_FLAG,
EXCLUDE_FROM_LATE_CHARGES,
GL_ID_REC,
GL_ID_REV,
START_DATE,
END_DATE,
NAME
INTO l_type,
l_post_to_gl,
l_accounting_affect_flag,
l_creation_sign,
l_natural_app_only_flag,
l_allow_overapplication_flag,
l_default_status,
l_tax_calculation_flag,
l_exclude_from_late_charges,
l_gl_id_rec,
l_gl_id_rev,
l_start_date,
l_end_date,
l_invoice_trx_name
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = l_subsequent_trx_type_id
AND org_id = p_org_id;
SELECT NAME,
STATUS,
AUTO_BATCH_NUMBERING_FLAG,
AUTO_TRX_NUMBERING_FLAG,
BATCH_SOURCE_TYPE,
ALLOW_DUPLICATE_TRX_NUM_FLAG,
CREDIT_MEMO_BATCH_SOURCE_ID
INTO l_batch_source_name,
l_batch_source_status,
l_auto_batch_numbering_flag,
l_auto_trx_numbering_flag,
l_batch_source_type,
l_allow_dup_trx_num_flag,
l_credit_memo_batch_source_id
FROM RA_BATCH_SOURCES_ALL
WHERE BATCH_SOURCE_ID = l_ra_batch_source_id
AND ORG_ID = p_org_id;
print_log('End of Select block');
SELECT NAME,
STATUS,
AUTO_BATCH_NUMBERING_FLAG,
AUTO_TRX_NUMBERING_FLAG,
BATCH_SOURCE_TYPE,
ALLOW_DUPLICATE_TRX_NUM_FLAG
INTO l_credit_batch_source_name,
l_cr_batch_source_status,
l_cr_auto_batch_numbering_flag,
l_cr_auto_trx_numbering_flag,
l_cr_batch_source_type,
l_cr_allow_dup_trx_num_flag
FROM RA_BATCH_SOURCES_ALL
WHERE BATCH_SOURCE_ID = l_credit_memo_batch_source_id
AND ORG_ID = p_org_id;
SELECT NAME,
LINE_TYPE,
START_DATE,
END_DATE,
GL_ID_REV
INTO l_memo_line_name,
l_memo_line_type,
l_memo_line_start_date,
l_memo_line_end_date,
l_memo_line_rev_acc
FROM AR_MEMO_LINES_VL
WHERE ORG_ID = p_org_id
AND MEMO_LINE_ID = p_memo_line_id;
SELECT NAME,
LINE_TYPE,
START_DATE,
END_DATE,
GL_ID_REV
INTO l_memo_line_name,
l_memo_line_type,
l_memo_line_start_date,
l_memo_line_end_date,
l_memo_line_rev_acc
FROM AR_MEMO_LINES_VL
WHERE ORG_ID = p_org_id
AND MEMO_LINE_ID = p_memo_line_invoice_id;
SELECT rt.NAME,
rt.START_DATE_ACTIVE,
rt.END_DATE_ACTIVE
INTO l_term_name,
l_term_start_date,
l_term_end_Date
FROM ra_terms_vl rt
WHERE 1 = (SELECT Count(*)
FROM ra_terms_lines rtl
WHERE rtl.term_id = rt.term_id
AND rtl.relative_amount = rt.base_amount
AND rtl.due_days = 0)
AND Nvl(rt.PARTIAL_DISCOUNT_FLAG,'N') = 'N'
AND Nvl(rt.PREPAYMENT_FLAG,'N') = 'N'
AND Nvl(rt.CREDIT_CHECK_FLAG,'N') = 'N'
AND rt.CALC_DISCOUNT_ON_LINES_FLAG = 'I'
AND rt.term_id = p_payment_terms_id;
SELECT NAME
INTO l_payment_type_name
FROM PON_EMD_PAYMENT_TYPES_VL
WHERE PAYMENT_TYPE_CODE=p_payment_type_code
AND ORG_ID = p_org_id;
SELECT RM.NAME,
RM.PAYMENT_CHANNEL_CODE,
RM.START_DATE,
RM.END_DATE,
RC.CREATION_METHOD_CODE
INTO l_receipt_method_name,
l_payment_channal_code,
l_rm_start_date,
l_rm_end_date,
l_receipt_creation_method
FROM AR_RECEIPT_METHODS RM,
AR_RECEIPT_CLASSES RC
WHERE RM.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND RM.RECEIPT_METHOD_ID = p_receipt_method_id;
SELECT PAYMENT_METHOD_NAME
INTO l_refund_payment_method_name
FROM IBY_PAYMENT_METHODS_VL
WHERE PAYMENT_METHOD_CODE = p_refund_payment_method;
PROCEDURE update_emd_trx_error_status(p_emd_transaction_id IN NUMBER,
p_msg_data IN VARCHAR2)
IS
BEGIN
UPDATE pon_emd_transactions
SET status_lookup_code = Decode(transaction_type_code, G_EMD_DEPOSIT, 'RECEIVE_ERROR',
G_EMD_REFUND, 'REFUND_ERROR',
G_EMD_FORFEIT, 'FORFEIT_ERROR'),
error_message = p_msg_data
WHERE emd_transaction_id = p_emd_transaction_id;
END update_emd_trx_error_status;
PROCEDURE update_trx_status_lookup_code(p_emd_transaction_id IN NUMBER)
IS
BEGIN
UPDATE pon_emd_transactions
SET status_lookup_code = Decode(transaction_type_code, G_EMD_DEPOSIT, 'RECEIVING',
G_EMD_REFUND, 'REFUNDING',
G_EMD_FORFEIT, 'FORFEITING'),
error_message = NULL
WHERE emd_transaction_id = p_emd_transaction_id;
END update_trx_status_lookup_code;
PROCEDURE insert_and_call_autoinvoice
( p_in_rec IN FORFEIT_INFO_IN_RECTYPE,
p_in_emd_trx IN pon_emd_trx_info_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_batch_source_name ra_batch_sources.NAME%TYPE := NULL;
SELECT message_text,invalid_value
FROM ra_interface_errors_all
WHERE org_id = x_org_id
AND interface_line_id = x_interface_line_id;
print_log('insert_and_call_autoinvoice');
SELECT name
INTO l_batch_source_name
FROM ra_batch_sources_all
WHERE batch_source_id = p_in_rec.batch_source_id
AND org_id = p_in_rec.org_id
AND Trunc(SYSDATE) BETWEEN start_date AND Nvl(end_date,Trunc(SYSDATE+1));
SELECT NAME,
DESCRIPTION
INTO l_memo_name,
l_memo_desc
FROM AR_MEMO_LINES_VL
WHERE ORG_ID = p_in_rec.org_id
AND MEMO_LINE_ID = p_in_rec.memo_line_id;
SELECT NAME
INTO l_cust_trx_name
FROM ra_cust_trx_types_all
WHERE CUST_TRX_TYPE_ID = p_in_rec.trx_type_id
AND org_id = p_in_rec.org_id;
SELECT customer_trx_line_id
INTO l_deposit_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = p_in_rec.deposit_trx_id
AND org_id = p_in_rec.org_id
AND ROWNUM =1;
SELECT ra_customer_trx_lines_s.NEXTVAL INTO l_interface_line_id FROM DUAL;
print_log('Before inserting records into ra_interface_lines');
/* Inserting
ORIG_SYSTEM_BILL_CONTACT_ID,
ORIG_SYSTEM_BILL_CONTACT_REF as NULLs
only as part of Forfeit Bug
*/
/* Insert Payment Term Name also */
SELECT rt.NAME INTO l_term_name
FROM ra_terms_vl rt
WHERE rt.term_id = p_in_rec.payment_term_id;
INSERT INTO ra_interface_lines_all
( INTERFACE_LINE_ID,
DESCRIPTION,
BATCH_SOURCE_NAME,
LINE_TYPE,
CURRENCY_CODE,
AMOUNT,
CUST_TRX_TYPE_ID,
CUST_TRX_TYPE_NAME,
CONVERSION_TYPE,
CONVERSION_DATE,
CONVERSION_RATE,
TRX_DATE,
GL_DATE,
PRINTING_OPTION,
ORIG_SYSTEM_BILL_CUSTOMER_ID,
ORIG_SYSTEM_BILL_CUSTOMER_REF,
ORIG_SYSTEM_BILL_ADDRESS_ID,
ORIG_SYSTEM_BILL_ADDRESS_REF,
TERM_NAME,
TERM_ID,
RELATED_CUSTOMER_TRX_ID,
REFERENCE_LINE_ID,
MEMO_LINE_ID,
PAYING_CUSTOMER_ID,
PAYING_SITE_USE_ID,
ORG_ID,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
INTERFACE_LINE_ATTRIBUTE2,
INTERFACE_LINE_ATTRIBUTE3,
INTERFACE_LINE_ATTRIBUTE4,
INTERFACE_LINE_ATTRIBUTE5,
INTERFACE_LINE_ATTRIBUTE6,
INTERFACE_LINE_ATTRIBUTE7,
INTERFACE_LINE_ATTRIBUTE8)
VALUES(l_interface_line_id,
Nvl(l_memo_desc,l_memo_name),
l_batch_source_name,
'LINE',
p_in_rec.transaction_currency_code,
p_in_rec.amount,
p_in_rec.trx_type_id,
l_cust_trx_name,
p_in_rec.exchange_rate_type,
p_in_rec.exchange_date,
p_in_rec.exchange_rate,
p_in_rec.transaction_date,
p_in_rec.transaction_date,
'PRI',
p_in_rec.cust_account_id,
p_in_rec.cust_account_id,
p_in_rec.cust_acct_site_id,
p_in_rec.cust_acct_site_id,
l_term_name,
p_in_rec.payment_term_id,
p_in_rec.deposit_trx_id,
l_deposit_trx_line_id,
p_in_rec.memo_line_id,
p_in_rec.cust_account_id,
p_in_rec.site_use_id,
p_in_rec.org_id,
'EMD TRANSACTIONS',
p_in_rec.document_number,
p_in_rec.party_id,
p_in_rec.party_site_id,
Nvl(p_in_rec.party_contact_id,'-9999'),
'No Bill Number',
'No Bill Info',
p_in_rec.emd_trx_group_id,
p_in_rec.emd_transaction_id);
print_log('exception while inserting'||SQLERRM);
print_log('After inserting into interface lines table'||l_interface_line_id);
DELETE FROM ra_interface_lines_all
WHERE INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
AND INTERFACE_LINE_ATTRIBUTE7 = to_char(p_in_rec.emd_trx_group_id);
DELETE FROM ra_interface_lines_all
WHERE INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
AND INTERFACE_LINE_ATTRIBUTE7 = to_char(p_in_rec.emd_trx_group_id);
SELECT rc.CUSTOMER_TRX_ID,
rc.TRX_NUMBER
INTO l_customer_trx_id,
l_trx_number
FROM ra_customer_trx_all rc, ra_customer_trx_lines_all rcl
WHERE rc.CUSTOMER_TRX_ID = rcl.CUSTOMER_TRX_ID
AND rcl.INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
AND rcl.INTERFACE_LINE_ATTRIBUTE7 = to_char(l_emd_transactions_info.emd_trx_group_id)
AND rcl.INTERFACE_LINE_ATTRIBUTE8 = to_char(l_emd_transactions_info.emd_transaction_id)
AND rcl.org_id = l_emd_transactions_info.org_id;
UPDATE pon_emd_transactions
SET CUST_TRX_ID =l_customer_trx_id,
CUST_TRX_NUMBER = l_trx_number,
POSTED_TO_FINANCE = 'Y',
STATUS_LOOKUP_CODE = 'FORFEITED'
WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
AND emd_transaction_group_id = l_emd_transactions_info.emd_trx_group_id
AND auction_header_id = l_emd_transactions_info.auction_header_id;
SELECT ril.interface_line_id
INTO l_interface_line_id
FROM ra_interface_lines_all ril
WHERE ril.INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
AND ril.INTERFACE_LINE_ATTRIBUTE7 = to_char(l_emd_transactions_info.emd_trx_group_id)
AND ril.INTERFACE_LINE_ATTRIBUTE8 = to_char(l_emd_transactions_info.emd_transaction_id);
SELECT Count(*)
INTO l_error_count
FROM ra_interface_errors_all
WHERE interface_line_id = l_interface_line_id
AND org_id = l_emd_transactions_info.org_id;
UPDATE pon_emd_transactions
SET status_lookup_code = 'FORFEIT_ERROR',
error_message = concat_err_msg
WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
AND emd_transaction_group_id = l_emd_transactions_info.emd_trx_group_id
AND auction_header_id = l_emd_transactions_info.auction_header_id;
DELETE FROM ra_interface_lines_all
WHERE interface_line_id =l_interface_line_id;
DELETE FROM ra_interface_errors_all
WHERE interface_line_id =l_interface_line_id;
DELETE FROM ra_interface_lines_all
WHERE INTERFACE_LINE_CONTEXT = 'EMD TRANSACTIONS'
AND INTERFACE_LINE_ATTRIBUTE7 = to_char(p_in_rec.emd_trx_group_id);
END insert_and_call_autoinvoice;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
UPDATE pon_emd_transactions
SET request_id = l_conc_req_id,
requested_by = fnd_global.user_id,
status_lookup_code = 'FORFEITING',
error_message = NULL
WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
AND emd_transaction_group_id = p_emd_trx_group_id;
update_emd_trx_error_status(l_emd_transactions_info.emd_transaction_id,
x_msg_data);
UPDATE pon_emd_transactions
SET request_id = l_conc_req_id,
requested_by = fnd_global.user_id,
status_lookup_code = 'REFUNDING',
error_message = NULL
WHERE emd_transaction_id = l_emd_transactions_info.emd_transaction_id
AND emd_transaction_group_id = p_emd_trx_group_id;