[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'X' into l_dummy
from ra_batch_sources
where batch_source_id = ar_deposit_lib_pvt.pg_profile_batch_source;
| arp_process_header_insrt_cover.insert_header_cover
| FND_MESSAGE.SET_NAME
| FND_MSG_PUB.Add;
SELECT cust_trx_type_id
INTO l_cust_trx_type_id
FROM ra_cust_trx_types
where type = 'DEP' and
nvl(p_deposit_date, trunc(sysdate)) between
nvl(start_date(+), nvl(p_deposit_date, trunc(sysdate))) and
nvl(end_date(+), nvl(p_deposit_date, trunc(sysdate))) and
cust_trx_type_id = l_default_cust_trx_type_id;
| Inserting Header and line for commitment calling the routine , |
| arp_process_header_insrt_cover.insert_header_cover |
+-----------------------------------------------------------------------------*/
BEGIN
arp_process_header_insrt_cover.insert_header_cover(
G_PKG_NAME,
p_api_version,
'DEP', -- p_class, --p_class can be used for future enhancement
l_default_gl_date,
NULL,
NULL,
NULL,
l_deposit_number,
NULL,
'Y',
NULL,
NULL,
NULL,
NULL,
l_default_cust_trx_type_id,
NULL,
l_default_batch_source_id,
l_agreement_id,
trunc(l_deposit_date), /*Bug 4065254*/
l_bill_to_customer_id,
l_bill_to_contact_id,
l_bill_to_site_use_id,
l_ship_to_customer_id,
l_ship_to_contact_id,
l_ship_to_site_use_id,
l_sold_to_customer_id ,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
trunc(l_start_date_commitmenmt), /*Bug 4065254*/
trunc(l_end_date_commitmenmt), /*Bug 4065254*/
trunc(l_exchange_rate_date), /*Bug 4065254*/
l_exchange_rate,
l_exchange_rate_type,
nvl(l_cust_bank_account_id,
l_fin_customer_bank_account_id),
l_financial_charges,
ar_deposit_lib_pvt.Get_FOB_POINT(l_bill_to_customer_id,
l_bill_to_location,
l_ship_to_customer_id,
l_ship_to_location,
x_return_status),
p_comments,
p_special_instructions,
l_currency_code,
NULL,
NULL,
NULL,
l_salesrep_id,
NULL,
NULL,
l_printing_option,
NULL,
l_printing_pending,
p_purchase_order,
p_purchase_order_date,
p_purchase_order_revision,
nvl(l_receipt_method_id,l_fin_receipt_method_id),
l_remit_to_address_id ,
NULL,
NULL,
NULL, /*Bug 4065254*/
trunc(l_term_due_date), /*Bug 4065254*/
l_default_term_id,
ar_deposit_lib_pvt.Get_Territory_id(l_bill_to_customer_id, l_bill_to_location,
l_ship_to_customer_id, l_ship_to_location,
l_salesrep_id,
l_deposit_date,
x_return_status),
NULL,
l_status_trx,
NUll,
l_doc_sequence_id,
l_doc_sequence_value,
l_paying_customer_id,
l_paying_site_use_id,
NULL,
l_tax_calculation_flag,
'ARXCDEPB' ,
p_ussgl_transaction_code,
NULL,
p_interface_header_context,
p_interface_header_attribute1,
p_interface_header_attribute2,
p_interface_header_attribute3,
p_interface_header_attribute4,
p_interface_header_attribute5,
p_interface_header_attribute6,
p_interface_header_attribute7,
p_interface_header_attribute8,
p_interface_header_attribute9,
p_interface_header_attribute10,
p_interface_header_attribute11,
p_interface_header_attribute12,
p_interface_header_attribute13,
p_interface_header_attribute14,
p_interface_header_attribute15,
l_attribute_rec.attribute_category,
l_attribute_rec.attribute1,
l_attribute_rec.attribute2,
l_attribute_rec.attribute3,
l_attribute_rec.attribute4,
l_attribute_rec.attribute5,
l_attribute_rec.attribute6,
l_attribute_rec.attribute7,
l_attribute_rec.attribute8,
l_attribute_rec.attribute9,
l_attribute_rec.attribute10,
l_attribute_rec.attribute11,
l_attribute_rec.attribute12,
l_attribute_rec.attribute13,
l_attribute_rec.attribute14,
l_attribute_rec.attribute15,
NULL,
l_inventory_id,
l_memo_line_id,
p_description,
l_amount,
p_comm_interface_line_attr1,
p_comm_interface_line_attr2,
p_comm_interface_line_attr3,
p_comm_interface_line_attr4,
p_comm_interface_line_attr5,
p_comm_interface_line_attr6,
p_comm_interface_line_attr7,
p_comm_interface_line_attr8,
p_comm_interface_line_attr9,
p_comm_interface_line_attr10,
p_comm_interface_line_attr11,
p_comm_interface_line_attr12,
p_comm_interface_line_attr13,
p_comm_interface_line_attr14,
p_comm_interface_line_attr15,
p_comm_interface_line_context,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
NULL,
l_new_trx_number,
l_new_customer_trx_id,
l_new_customer_trx_line_id,
l_new_rowid,
l_new_status,
l_legal_entity_id);
arp_util.debug('arp_process_header_insrt_cover.insert_header_cover: l_new_status'||l_new_status );
FND_MESSAGE.SET_NAME('AR','AR_DAPI_INSERT_HEADER_ST');
| arp_process_header_insrt_cover.insert_header_cover using |
| arp_process_header.post_commit |
+-----------------------------------------------------------------------------*/
IF l_new_customer_trx_id IS NOT NULL
THEN
BEGIN
arp_process_header.post_commit( 'AR_DEPOSIT_API_PUB',
1.0,
l_new_customer_trx_id,
NULL,
'Y',
l_open_receivables_flag,
NULL,
l_creation_sign,
l_allow_overapplication_flag,
l_natural_app_only_flag,
NULL
);
update ra_customer_trx
set old_trx_number = l_new_trx_number ,
trx_number = l_doc_sequence_value
where customer_trx_id = l_new_customer_trx_id ;
update ar_payment_schedules
set trx_number = l_doc_sequence_value
where customer_trx_id = l_new_customer_trx_id ;
| PUBLIC PROCEDURE insert_non_rev_salescredit
|
| DESCRIPTION
| Enter a brief description of what the package procedure does.
| ----------------------------------------
| This routine is called to assign non revenue sales credit to
| salesreps of to deposit, commitment. You can create as many
| of the non-revenue credit assignment.
| This API routine has 4 output and 22 input parameters in total.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Enter a list of all local procedures and functions which
| are call this package.
| AR_DEPOSIT_API_PUB.insert_non_rev_salescredit
| Parameter
| p_deposit_number
| p_customer_trx_id
| p_salesrep_number
| p_salesrep_id
| p_non_revenue_amount_split
| p_non_revenue_percent_split
| p_attribute_category
| p_attribute1 to p_attribute15
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| PARAMETERS
|
| None
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 08-JUL-2003 Anuj Created
| DD-MON-YYYY Name Bug #####, modified amount ..
|
*=======================================================================*/
PROCEDURE insert_non_rev_salescredit
(
-- Standard API parameters.
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_deposit_number IN VARCHAR2 DEFAULT NULL,
p_customer_trx_id IN NUMBER DEFAULT NULL,
p_salesrep_number IN VARCHAR2 DEFAULT NULL,
p_salesrep_id IN NUMBER DEFAULT NULL,
p_non_revenue_amount_split IN NUMBER DEFAULT NULL,
p_non_revenue_percent_split IN NUMBER DEFAULT NULL,
p_attribute_category IN VARCHAR2 DEFAULT NULL ,
p_attribute1 IN VARCHAR2 DEFAULT NULL ,
p_attribute2 IN VARCHAR2 DEFAULT NULL ,
p_attribute3 IN VARCHAR2 DEFAULT NULL ,
p_attribute4 IN VARCHAR2 DEFAULT NULL ,
p_attribute5 IN VARCHAR2 DEFAULT NULL ,
p_attribute6 IN VARCHAR2 DEFAULT NULL ,
p_attribute7 IN VARCHAR2 DEFAULT NULL ,
p_attribute8 IN VARCHAR2 DEFAULT NULL ,
p_attribute9 IN VARCHAR2 DEFAULT NULL ,
p_attribute10 IN VARCHAR2 DEFAULT NULL ,
p_attribute11 IN VARCHAR2 DEFAULT NULL ,
p_attribute12 IN VARCHAR2 DEFAULT NULL ,
p_attribute13 IN VARCHAR2 DEFAULT NULL ,
p_attribute14 IN VARCHAR2 DEFAULT NULL ,
p_attribute15 IN VARCHAR2 DEFAULT NULL ,
p_org_id IN NUMBER DEFAULT NULL )
IS
l_api_name CONSTANT VARCHAR2(100) := 'insert_non_rev_salescredit';
arp_util.debug('AR_DEPOSIT_API_PUB.insert_non_rev_salescredit()+ ');
SELECT customer_trx_id,INVOICE_CURRENCY_CODE,trx_date
INTO l_customer_trx_id,l_INVOICE_CURRENCY_CODE,l_deposit_date
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT customer_trx_id into l_dummy_number
FROM ra_customer_trx cust_trx ,
ra_cust_trx_types cust_trx_type
WHERE cust_trx.cust_trx_type_id =cust_trx_type.cust_trx_type_id
and customer_trx_id = l_customer_trx_id
and cust_trx_type.TYPE = 'DEP';
SELECT customer_trx_id,INVOICE_CURRENCY_CODE,trx_date
INTO l_customer_trx_id,l_INVOICE_CURRENCY_CODE,l_deposit_date
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
SELECT customer_trx_id into l_dummy_number
FROM ra_customer_trx cust_trx ,
ra_cust_trx_types cust_trx_type
WHERE cust_trx.cust_trx_type_id =cust_trx_type.cust_trx_type_id
and customer_trx_id = l_customer_trx_id and
cust_trx_type.TYPE = 'DEP';
SELECT customer_trx_id,INVOICE_CURRENCY_CODE,trx_date
INTO l_customer_trx_id,l_INVOICE_CURRENCY_CODE,l_deposit_date
FROM ra_customer_trx
WHERE trx_number = p_deposit_number;
SELECT customer_trx_id into l_dummy_number
FROM ra_customer_trx cust_trx ,
ra_cust_trx_types cust_trx_type
WHERE cust_trx.cust_trx_type_id =cust_trx_type.cust_trx_type_id
and customer_trx_id = l_customer_trx_id and
cust_trx_type.TYPE = 'DEP';
select sum(EXTENDED_AMOUNT)
into l_amount
from ra_customer_trx_lines
where customer_trx_id = l_customer_trx_id;
select customer_trx_line_id
into l_customer_trx_line_id
from ra_customer_trx_lines
where customer_trx_id = l_customer_trx_id; --only line per deposit
SELECT salesrep_id
INTO l_salesrep_id
FROM ra_salesreps
WHERE SALESREP_NUMBER = p_salesrep_number and
NVL(status,'A') ='A' and
l_deposit_date between nvl(start_date_active, l_deposit_date) and
nvl(end_date_active, l_deposit_date);
SELECT salesrep_id
INTO l_salesrep_id
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id and
NVL(status,'A') ='A' and
l_deposit_date between nvl(start_date_active, l_deposit_date) and
nvl(end_date_active, l_deposit_date);
arp_process_salescredit.insert_salescredit_cover(
l_api_name,
l_api_version,
FALSE, --l_rerun_autoaccounting_flag,
l_customer_trx_id , -- ak1 art_context.pg_customer_trx_id,
l_customer_trx_line_id, -- derive based on p_customer_trx_id
l_salesrep_id , -- ak2 Name_In('tscr_lines.salesrep_id'),
null, -- l_revenue_amount_split,
l_non_revenue_amount_split, --ak3 l_non_revenue_amount_split,
l_non_revenue_percent_split, --ak4 Name_In('tscr_lines.non_revenue_percent_split'),
null, --Name_In('tscr_lines.revenue_percent_split'),
null, --Name_In('tscr_lines.prev_cust_trx_line_salesrep_id'),
l_desc_flex_rec.attribute_category ,
l_desc_flex_rec.attribute1,
l_desc_flex_rec.attribute2,
l_desc_flex_rec.attribute3,
l_desc_flex_rec.attribute4,
l_desc_flex_rec.attribute5,
l_desc_flex_rec.attribute6,
l_desc_flex_rec.attribute7,
l_desc_flex_rec.attribute8,
l_desc_flex_rec.attribute9,
l_desc_flex_rec.attribute10,
l_desc_flex_rec.attribute11,
l_desc_flex_rec.attribute12,
l_desc_flex_rec.attribute13,
l_desc_flex_rec.attribute14,
l_desc_flex_rec.attribute15,
l_cust_trx_line_salesrep_id,
l_status );
arp_util.debug('AR_DEPOSIT_API_PUB.insert_non_rev_salescredit()- ');
END insert_non_rev_salescredit;
| This procedure does the following: updates pmt_trx_extn
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Enter a list of all local procedures and functions which
| are call this package.
|
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| Enter a list of all local procedures and funtions which
| this package calls.
| fnd_profile.value
|
| PARAMETERS
|
| None
|
| KNOWN ISSUES
|
| NOTES
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 26-sep-2005 bichatte created
|
*=======================================================================*/
PROCEDURE copy_trxn_extension( p_customer_trx_id IN NUMBER,
p_payment_trxn_extension_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2 ) IS
l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
select trx.paying_customer_id,
trx.paying_site_use_id,
trx.org_id,
party.party_id,
trx.trx_number,
rm.payment_channel_code,
trx.customer_trx_id
into l_customer_id,
l_customer_site_use_id,
l_org_id,
l_party_id,
l_trx_number,
l_payment_channel,
l_customer_trx_id
FROM hz_cust_accounts hca,
hz_parties party,
ra_customer_trx trx,
ar_receipt_methods rm
WHERE trx.customer_trx_id = p_customer_trx_id
AND hca.party_id = party.party_id
AND hca.cust_account_id = trx.paying_customer_id
AND trx.receipt_method_id = rm.receipt_method_id(+) ;
SELECT INSTR_ASSIGNMENT_ID
INTO l_assignment_id
from iby_fndcpt_tx_extensions
where trxn_extension_id = p_payment_trxn_extension_id;
update ra_customer_trx
set payment_trxn_extension_id = o_payment_trxn_extension_id
where customer_trx_id = p_customer_trx_id ;