[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_REMIT_ADDR_ID'),
remit_to_address_id
FROM ar_trx_header_gt gt
WHERE gt.remit_to_address_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM ar_active_remit_to_addresses_v arta
WHERE arta.address_id = gt.remit_to_address_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_TRX_CLASS'),
trx_class
FROM ar_trx_header_gt gt
WHERE gt.trx_class NOT IN ('INV', 'DM' , 'CM') -- added CM for ER 5869149
AND gt.trx_class IS NOT NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_VIA'),
ship_via
FROM ar_trx_header_gt gt
WHERE gt.ship_via IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM org_freight orf
WHERE orf.freight_code = gt.ship_via
AND orf.organization_id = pg_so_org_id /*Bug4709987*/
AND gt.trx_date <= nvl(trunc(orf.disable_date), gt.trx_date));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_FOB'),
fob_point
FROM ar_trx_header_gt gt
WHERE gt.fob_point IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM ar_lookups
WHERE lookup_code = gt.fob_point
AND lookup_type = 'FOB'
AND gt.trx_date
BETWEEN start_date_active and nvl(end_date_active, gt.trx_date));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_USSGL_CODE'),
default_ussgl_transaction_code
FROM ar_trx_header_gt gt
WHERE gt.default_ussgl_transaction_code IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM gl_ussgl_transaction_codes gutc
WHERE gutc.ussgl_transaction_code = gt.default_ussgl_transaction_code
AND gutc.chart_of_accounts_id = arp_global.chart_of_accounts_id
AND gt.trx_date
BETWEEN NVL(gutc.start_date_active, gt.trx_date)
AND NVL(gutc.end_date_active, gt.trx_date));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
receipt_method_id
FROM ar_trx_header_gt gt
WHERE gt.receipt_method_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM ar_receipt_methods rm
WHERE rm.receipt_method_id = gt.receipt_method_id
AND gt.trx_date BETWEEN NVL(rm.start_date, gt.trx_date)
AND NVL(rm.end_date, gt.trx_date));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_BOE_OBSOLETE'),
receipt_method_id
FROM ar_trx_header_gt gt
WHERE gt.receipt_method_id IS NOT NULL
AND arpt_sql_func_util.check_boe_paymeth(gt.receipt_method_id) = 'Y';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
receipt_method_id
FROM ar_trx_header_gt gt
WHERE gt.payment_trxn_extension_id IS NOT NULL
AND EXISTS
(SELECT 'X'
FROM ar_receipt_methods rm,
ar_receipt_classes rc
WHERE rm.receipt_method_id = gt.receipt_method_id
AND rm.receipt_class_id = rc.receipt_class_id
AND rc.creation_method_code = 'MANUAL' );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
receipt_method_id
FROM ar_trx_header_gt gt
WHERE gt.payment_trxn_extension_id IS NOT NULL
AND EXISTS
(SELECT 'X'
FROM ar_receipt_methods rm,
iby_trxn_extensions_v iby
WHERE rm.receipt_method_id = gt.receipt_method_id
AND iby.trxn_extension_id= gt.payment_trxn_extension_id
AND iby.PAYMENT_CHANNEL_CODE <> rm.payment_channel_code );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
receipt_method_id
FROM ar_trx_header_gt gt
WHERE gt.receipt_method_id IS NOT NULL
AND gt.payment_trxn_extension_id IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
receipt_method_id
FROM ar_trx_header_gt gt
WHERE gt.receipt_method_id IS NULL
AND gt.payment_trxn_extension_id IS NOT NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_LEGAL_ENTITY'),
legal_entity_id
FROM ar_trx_header_gt gt
WHERE gt.legal_entity_id IS NOT NULL
AND NOT EXISTS (
SELECT 'valid LE'
FROM XLE_LE_OU_LEDGER_V LE
WHERE LE.legal_entity_id = GT.legal_entity_id
AND LE.operating_unit_id = GT.org_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_CUS_ID'),
paying_customer_id
FROM ar_trx_header_gt gt
WHERE gt.paying_customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM hz_cust_accounts cust_acct
WHERE cust_acct.cust_account_id = gt.paying_customer_id
AND cust_acct.status = 'A' );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_CUS_ID'),
paying_customer_id
FROM ar_trx_header_gt gt
WHERE gt.paying_customer_id IS NOT NULL
AND NOT EXISTS
( SELECT 'X'
FROM hz_cust_accounts cust_acct
WHERE cust_acct.cust_account_id = gt.paying_customer_id
AND cust_acct.status = 'A'
AND cust_acct.cust_account_id IN
(
SELECT cr.cust_account_id
FROM hz_cust_acct_relate cr
WHERE cr.related_cust_account_id = gt.bill_to_customer_id
AND cr.status = 'A'
AND cr.bill_to_flag ='Y'
UNION
SELECT to_number(gt.bill_to_customer_id)
FROM DUAL
UNION
SELECT acc.cust_account_id
FROM ar_paying_relationships_v rel, hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND rel.related_cust_account_id = gt.bill_to_customer_id
AND gt.trx_date BETWEEN effective_start_date
AND effective_end_date)
);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_SIT_ID'),
paying_site_use_id
FROM ar_trx_header_gt gt
WHERE gt.paying_site_use_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_acct_sites acct_site,
hz_cust_site_uses site_uses
WHERE acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
AND acct_site.cust_account_id = gt.paying_customer_id
AND site_uses.site_use_id = gt.paying_site_use_id
AND site_uses.site_use_code IN
('BILL_TO', decode(p_trx_system_param_rec.br_enabled_flag,
'DRAWEE', 'PAYING')));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_DAPI_SOLD_CUST_ID_INVALID'),
sold_to_customer_id
FROM ar_trx_header_gt gt
WHERE gt.sold_to_customer_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_accounts cust_acct
WHERE cust_acct.cust_account_id = gt.sold_to_customer_id
AND cust_acct.status = 'A');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_NAME'),
ship_to_customer_name
FROM ar_trx_header_gt gt
WHERE gt.ship_to_customer_name IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
AND party.party_name = gt.ship_to_customer_name);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_NUM'),
ship_to_account_number
FROM ar_trx_header_gt gt
WHERE gt.ship_to_account_number IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_accounts cust_acct
WHERE cust_acct.account_number = gt.ship_to_account_number);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_NAME'),
bill_to_customer_name
FROM ar_trx_header_gt gt
WHERE gt.bill_to_customer_name IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
AND party.party_name = gt.bill_to_customer_name);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_NUM'),
bill_to_account_number
FROM ar_trx_header_gt gt
WHERE gt.bill_to_account_number IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_accounts cust_acct
WHERE cust_acct.account_number = gt.bill_to_account_number);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_BAD_BLL_TO_CONTACT_ID'),
bill_to_contact_id
FROM ar_trx_header_gt gt
WHERE gt.bill_to_contact_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = gt.bill_to_customer_id
and ( acct_role.cust_acct_site_id = gt.bill_to_address_id
or acct_role.cust_acct_site_id IS NULL )
AND acct_role.status = 'A');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_BAD_SHP_TO_CONTACT_ID'),
ship_to_contact_id
FROM ar_trx_header_gt gt
WHERE gt.ship_to_contact_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM hz_cust_account_roles acct_role,
hz_parties party,
hz_relationships rel,
hz_org_contacts org_cont,
hz_parties rel_party
WHERE acct_role.party_id = rel.party_id
AND acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.party_id = rel_party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and rel.directional_flag = 'F'
and acct_role.cust_account_id = gt.ship_to_customer_id
and ( acct_role.cust_acct_site_id = gt.ship_to_address_id
or acct_role.cust_acct_site_id IS NULL )
AND acct_role.status = 'A');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_XCHNG_RATE_TYPE'),
exchange_rate_type
FROM ar_trx_header_gt gt
WHERE gt.exchange_rate_type IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM gl_daily_conversion_types
WHERE conversion_type <> 'EMU FIXED'
AND conversion_type = gt.exchange_rate_type); /*Bug 4517001*/
SELECT *
FROM ar_trx_header_gt gt
WHERE gt.doc_sequence_value IS NOT NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
VALUES
(header_rec.trx_header_id,
arp_standard.fnd_message('AR', 'AR_RAPI_DOC_SEQ_AUTOMATIC'),
header_rec.doc_sequence_value);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_BFB_BILLING_DATE_REQD'),
billing_date
FROM ar_trx_header_gt
WHERE term_id IS NOT NULL
AND billing_date IS NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
AND nvl(ar_bfb_utils_pvt.get_cycle_type (ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'EVENT';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_BFB_OPEN_REC'),
term_id
FROM ar_trx_header_gt
WHERE term_id IS NOT NULL
AND cust_trx_type_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
AND ar_bfb_utils_pvt.get_open_rec(cust_trx_type_id) = 'N';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_BFB_BILLING_DATE_INV'),
billing_date
FROM ar_trx_header_gt
WHERE term_id IS NOT NULL
AND billing_date IS NOT NULL
AND
(
(-- billing_date should be null for non-BFB
ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'N'
)
OR
(-- BFB is not valid for open_rec = No, so there should be NO billing date either
cust_trx_type_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
AND ar_bfb_utils_pvt.get_open_rec(cust_trx_type_id) = 'N'
)
);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
DECODE(b.auto_trx_numbering_flag, 'Y',
arp_standard.fnd_message('AR_INAPI_TRX_NUM_NOT_REQUIRED'),
arp_standard.fnd_message('AR_TW_NULL_TRX_NUMBER')),
trx_number
FROM ar_trx_header_gt gt,
ra_batch_sources b
WHERE b.batch_source_id = gt.batch_source_id
AND ((gt.trx_number IS NULL AND
NVL(b.auto_trx_numbering_flag,'N') = 'N')
OR (gt.trx_number IS NOT NULL AND
b.auto_trx_numbering_flag = 'Y'));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_BANK_ACC_NOT_REQUIRED'),
customer_bank_account_id
FROM ar_trx_header_gt gt
WHERE gt.customer_bank_account_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM ar_receipt_methods rm,
ar_receipt_classes rc
WHERE rm.receipt_class_id = rc.receipt_class_id
AND rm.receipt_method_id = gt.receipt_method_id
AND rc.creation_method_code = 'AUTOMATIC');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_PAYING_SITE_REQUIRED'),
paying_customer_id
FROM ar_trx_header_gt gt
WHERE gt.paying_customer_id IS NOT NULL
AND gt.paying_site_use_id IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_PO_REQUIRED'),
paying_customer_id
FROM ar_trx_header_gt gt
WHERE (gt.purchase_order_revision IS NOT NULL
OR gt.purchase_order_date IS NOT NULL)
AND purchase_order IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT lgt.trx_header_id,
lgt.trx_line_id,
arp_standard.fnd_message('AR_INAPI_RULE_INFO_UNNECESSARY'),
NVL(lgt.accounting_rule_id, lgt.accounting_rule_duration)
FROM ar_trx_lines_gt lgt,
ar_trx_header_gt hgt
WHERE (lgt.accounting_rule_id IS NOT NULL
OR lgt.accounting_rule_duration IS NOT NULL)
AND lgt.trx_header_id = hgt.trx_header_id
AND hgt.invoicing_rule_id IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INV_MASTER_LINE_TYPE'),
link_to_trx_line_id
FROM ar_trx_lines_gt gt
WHERE gt.link_to_trx_line_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM ar_trx_lines_gt gt2
WHERE gt2.trx_line_id = gt.link_to_trx_line_id
AND gt2.line_type = 'LINE');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_SUB_LINE_TYPE'),
link_to_trx_line_id
FROM ar_trx_lines_gt gt
WHERE gt.link_to_trx_line_id IS NOT NULL
AND gt.line_type NOT IN ('TAX', 'FREIGHT');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_EXCHANGE_RATE_REQUIRED'),
gt.trx_currency
FROM ar_trx_header_gt gt
WHERE gt.trx_currency IS NOT NULL
AND ( exchange_rate IS NULL
OR exchange_rate <= 0 )
AND exchange_rate_type = 'User'
AND gt.trx_currency <> p_trx_system_param_rec.base_currency_code;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_RATE_NOT_REQUIRED2'),
exchange_rate
FROM ar_trx_header_gt
WHERE exchange_rate IS NOT NULL
AND exchange_rate_type <> 'User';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_EXCHG_INFO_NOT_ALLOWED'),
trx_currency
FROM ar_trx_header_gt
WHERE ( exchange_rate IS NOT NULL
OR exchange_rate_type IS NOT NULL
OR exchange_date IS NOT NULL )
AND trx_currency = p_trx_system_param_rec.base_currency_code;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_TAX_CHOOSE_YES_NO'),
amount_includes_tax_flag
FROM ar_trx_lines_gt lgt
WHERE amount_includes_tax_flag IS NOT NULL
AND amount_includes_tax_flag NOT IN ('Y', 'N');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TW_NO_LINES'),
null
FROM ar_trx_header_gt gt
WHERE NOT EXISTS
(SELECT 'X'
FROM ar_trx_lines_gt lgt
WHERE lgt.trx_header_id = gt.trx_header_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_ORPHAN_LINES'),
null
FROM ar_trx_lines_gt lgt
WHERE NOT EXISTS
(SELECT 'X'
FROM ar_trx_header_gt hgt
WHERE hgt.trx_header_id = lgt.trx_header_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_LINE_NUMS_NOT_UNIQUE'),
lgt.line_number
FROM ar_trx_lines_gt lgt
WHERE EXISTS
(SELECT 'X'
FROM ar_trx_lines_gt lgt2
WHERE lgt2.trx_header_id = lgt.trx_header_id
AND lgt2.line_number = lgt.line_number
AND lgt2.line_type = lgt.line_type
GROUP BY lgt2.trx_header_id, lgt2.line_number
HAVING count(*) > 1);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_LINE_ID_NOT_UNIQUE'),
null
FROM ar_trx_lines_gt lgt
WHERE EXISTS
(SELECT 'X'
FROM ar_trx_lines_gt lgt2
WHERE lgt2.trx_header_id = lgt.trx_header_id
AND lgt2.trx_line_id = lgt.trx_line_id
GROUP BY lgt2.trx_header_id, lgt2.trx_line_id
HAVING count(*) > 1);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_HEADER_ID_NOT_UNIQUE'),
null
FROM ar_trx_header_gt hgt
WHERE EXISTS
(SELECT 'X'
FROM ar_trx_header_gt hgt2
WHERE hgt2.trx_header_id = hgt.trx_header_id
GROUP BY hgt2.trx_header_id
HAVING count(*) > 1);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TW_INVALID_TRX_NUMBER'),
trx_number
FROM ar_trx_header_gt gt
WHERE gt.trx_number IS NOT NULL
AND gt.batch_source_id IS NOT NULL
AND EXISTS (
SELECT 'X'
FROM ra_batch_sources batch,
ra_customer_trx trx
WHERE trx.batch_source_id = gt.batch_source_id
AND trx.trx_number = gt.trx_number
AND trx.customer_trx_id <> NVL(gt.customer_trx_id, -99)
AND trx.batch_source_id = batch.batch_source_id
AND nvl(batch.copy_doc_number_flag,'N') = 'N'
AND nvl(batch.allow_duplicate_trx_num_flag,'N') = 'N'
UNION
SELECT 'X'
FROM ra_recur_interim ri,
ra_customer_trx ct,
ra_batch_sources batch
WHERE ct.customer_trx_id = ri.customer_trx_id
AND ct.batch_source_id = gt.batch_source_id
AND ri.trx_number = gt.trx_number
AND NVL(ri.new_customer_trx_id, -98)
<> NVL(gt.customer_trx_id, -99)
AND ct.batch_source_id = batch.batch_source_id
AND nvl(batch.copy_doc_number_flag,'N') = 'N'
AND nvl(batch.allow_duplicate_trx_num_flag,'N') = 'N'
UNION
SELECT 'X'
FROM ra_batch_sources bs,
ar_trx_header_gt ril
WHERE ril.batch_source_id = bs.batch_source_id
AND bs.batch_source_id = gt.batch_source_id
AND ril.trx_number = gt.trx_number
AND ril.customer_trx_id <> NVL(gt.customer_trx_id, -99)
AND nvl(bs.copy_doc_number_flag,'N') = 'N'
AND nvl(bs.allow_duplicate_trx_num_flag,'N') = 'N'
);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_BATCH_SOURCE'),
batch_source_id
FROM ar_trx_header_gt gt
WHERE NOT EXISTS (
SELECT 'X'
FROM ra_batch_sources bs
where nvl(gt.trx_date, trunc(sysdate)) between
nvl(bs.start_date, nvl(gt.trx_date, trunc(sysdate)))
and nvl(bs.end_date, nvl(gt.trx_date, trunc(sysdate)))
and nvl(bs.status, 'A') = 'A'
--and bs.batch_source_type = 'INV' -- means manual batch
and bs.batch_source_id not in (11, 12)
and gt.batch_source_id = bs.batch_source_id);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_CURRENCY'),
trx_currency
FROM ar_trx_header_gt gt
WHERE gt.trx_currency IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM fnd_currencies c
WHERE c.currency_code = gt.trx_currency
AND gt.trx_date BETWEEN NVL(c.start_date_active, gt.trx_date)
AND NVL(c.end_date_active, gt.trx_date) );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_BR_TRX_TYPE_NULL'),
cust_trx_type_id
FROM ar_trx_header_gt gt
WHERE gt.cust_trx_type_id IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_TRX_TYPE'),
cust_trx_type_id
FROM ar_trx_header_gt gt
WHERE gt.cust_trx_type_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM ra_cust_trx_types ctt
where nvl(gt.trx_date, trunc(sysdate)) between
ctt.start_date and nvl(ctt.end_date, nvl(gt.trx_date, trunc(sysdate)))
and type IN ('INV', 'DM', 'CM') -- added CM for ER 5869149
and ctt.cust_trx_type_id = gt.cust_trx_type_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_ID'),
bill_to_customer_id
FROM ar_trx_header_gt gt
WHERE gt.bill_to_customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM hz_cust_accounts ct
WHERE ct.cust_account_id = gt.bill_to_customer_id
AND status = 'A');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_SITE_USE'),
bill_to_site_use_id
FROM ar_trx_header_gt gt
WHERE gt.bill_to_site_use_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM hz_cust_site_uses ct
WHERE site_use_code = 'BILL_TO'
AND cust_acct_site_id in (
SELECT cust_acct_site_id
FROM hz_cust_acct_sites
WHERE cust_account_id = gt.bill_to_customer_id)
AND site_use_id = gt.bill_to_site_use_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_BILL_ADDR_ID'),
bill_to_address_id
FROM ar_trx_header_gt gt
WHERE gt.bill_to_address_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
--HZ_LOCATIONS LOC,
HZ_CUST_SITE_USES SU
-- FND_TERRITORIES_VL T
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
--AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
--AND LOC.COUNTRY = T.TERRITORY_CODE
AND ACCT_SITE.CUST_ACCOUNT_ID = gt.bill_to_customer_id
AND SU.SITE_USE_ID = NVL(gt.bill_to_site_use_id, SU.SITE_USE_ID)
AND SU.SITE_USE_CODE = 'BILL_TO'
AND (SU.SITE_USE_ID = gt.bill_to_site_use_id
OR (gt.bill_to_site_use_id IS NULL
AND SU.STATUS = 'A' AND ACCT_SITE.STATUS = 'A' ))
AND SU.CUST_ACCT_SITE_ID = gt.bill_to_address_id );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_ID'),
ship_to_customer_id
FROM ar_trx_header_gt gt
WHERE gt.ship_to_customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM hz_cust_accounts ct
WHERE ct.cust_account_id = gt.ship_to_customer_id
AND ct.status = 'A');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
ship_to_site_use_id
FROM ar_trx_header_gt gt
WHERE gt.ship_to_site_use_id IS NOT NULL
AND gt.ship_to_customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM hz_cust_site_uses ct
WHERE site_use_code = 'SHIP_TO'
and cust_acct_site_id in (
select cust_acct_site_id from hz_cust_acct_sites
where cust_account_id = gt.ship_to_customer_id)
and site_use_id = gt.ship_to_site_use_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_NULL_SHIP_TO_SITE'),
ship_to_site_use_id
FROM ar_trx_header_gt gt
WHERE gt.ship_to_site_use_id IS NULL
AND gt.ship_to_customer_id IS NOT NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
ship_to_site_use_id
FROM ar_trx_header_gt gt
WHERE gt.ship_to_site_use_id IS NOT NULL
AND NOT EXISTS (
select 'X'
from
(
SELECT
A.CUST_ACCOUNT_ID CUSTOMER_ID ,
A.STATUS A_STATUS ,
SU.STATUS SU_STATUS ,
SU.SITE_USE_ID SITE_USE_ID
FROM
HZ_CUST_ACCT_SITES A,
HZ_CUST_SITE_USES SU
WHERE
A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_CODE = 'SHIP_TO'
) asa
where asa.customer_id = gt.ship_to_customer_id
and ( asa.site_use_id = gt.ship_to_site_use_id
or ( asa.su_status = 'A' and asa.a_status = 'A' ) ));
UPDATE ar_trx_header_gt gt
SET ship_to_site_use_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE site_use_code = 'SHIP_TO'
AND cust_acct_site_id = gt.ship_to_address_id
AND gt.ship_to_site_use_id IS NULL
AND gt.ship_to_address_id IS NOT NULL)
WHERE gt.ship_to_site_use_id IS NULL
AND gt.ship_to_address_id IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET ship_to_site_use_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE primary_flag = 'Y'
AND site_use_code = 'SHIP_TO'
AND cust_acct_site_id IN (
SELECT cust_acct_site_id
FROM hz_cust_acct_sites
WHERE cust_account_id = gt.ship_to_customer_id))
WHERE gt.ship_to_site_use_id IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
gt.ship_to_site_use_id
FROM ar_trx_header_gt gt
WHERE gt.ship_to_customer_id IS NOT NULL
AND gt.ship_to_site_use_id IS NOT NULL
AND NOT EXISTS (
select 'X'
from
(
SELECT
A.CUST_ACCOUNT_ID CUSTOMER_ID ,
A.STATUS A_STATUS ,
SU.STATUS SU_STATUS ,
SU.SITE_USE_ID SITE_USE_ID
FROM
HZ_CUST_ACCT_SITES A,
HZ_CUST_SITE_USES SU
WHERE
A.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_CODE = 'SHIP_TO'
)asa
where asa.customer_id = gt.ship_to_customer_id
and ( asa.site_use_id = gt.ship_to_site_use_id
or ( gt.customer_trx_id is null
and asa.su_status = 'A' and asa.a_status = 'A' ) ) );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT gt.trx_header_id,
arp_standard.fnd_message('AR_INVALID_TERM'),
gt.term_id
FROM ar_trx_header_gt gt,
ra_cust_trx_types ctt
WHERE gt.term_id IS NOT NULL
AND gt.trx_class <> 'CM' -- added for ER 5869149
AND ctt.cust_trx_type_id = gt.cust_trx_type_id -- ER 5869149
AND ctt.type <> 'CM' -- ER 5869149
AND NOT EXISTS (
SELECT 'X'
from ra_terms_lines tl, ra_terms t
where nvl(gt.trx_date, trunc(sysdate))
between t.start_date_active and nvl(t.end_date_active, nvl( gt.trx_date, trunc(sysdate) ) )
and t.term_id = tl.term_id
and t.term_id = gt.term_id );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT gt.trx_header_id,
arp_standard.fnd_message('AR_INAPI_TERM_NOT_ALLOWED'),
gt.term_id
FROM ar_trx_header_gt gt,
ra_cust_trx_types ctt
WHERE gt.term_id IS NOT NULL
AND gt.cust_trx_type_id = ctt.cust_trx_type_id
AND ctt.type = 'CM';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_BFB_INVALID_TERM'),
term_id
FROM ar_trx_header_gt gt
WHERE gt.term_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) = 'N';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INVALID_PRIMARY_SALESREP'),
primary_salesrep_id
FROM ar_trx_header_gt gt
WHERE gt.primary_salesrep_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_SITE_USES SU,
FND_TERRITORIES_VL T
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.COUNTRY = T.TERRITORY_CODE
AND ACCT_SITE.CUST_ACCOUNT_ID = gt.bill_to_customer_id
AND SU.SITE_USE_ID = NVL(gt.bill_to_site_use_id, SU.SITE_USE_ID)
AND SU.SITE_USE_CODE = 'BILL_TO'
AND (SU.SITE_USE_ID = gt.bill_to_site_use_id
OR (gt.bill_to_site_use_id IS NULL
AND SU.STATUS = 'A'
AND ACCT_SITE.STATUS = 'A' ))
-- AND SU.PRIMARY_FLAG = 'Y'
AND SU.primary_salesrep_id = gt.primary_salesrep_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message(l_message_name),
primary_salesrep_id
FROM ar_trx_header_gt gt
WHERE gt.primary_salesrep_id IS NOT NULL
AND NOT EXISTS (
(SELECT 'X'
FROM ra_salesreps rs
WHERE rs.salesrep_id = gt.primary_salesrep_id
AND gt.trx_date
BETWEEN rs.start_date_active
AND NVL(rs.end_date_active, sysdate)));
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message(l_message_name),
primary_salesrep_id
FROM ar_trx_header_gt gt
WHERE gt.primary_salesrep_id IS NULL
AND p_trx_system_param_rec.salesrep_required_flag = 'Y';
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_RULE_NOT_REQUIRED'),
invoicing_rule_id
FROM ar_trx_header_gt gt
WHERE gt.invoicing_rule_id IS NOT NULL
AND gt.cust_trx_type_id NOT IN (
SELECT tt.cust_trx_type_id
FROM ra_cust_trx_types tt
WHERE tt.cust_trx_type_id = gt.cust_trx_type_id
AND tt.type = 'INV' );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_INV_RULE_ID'),
invoicing_rule_id
FROM ar_trx_header_gt gt
WHERE gt.invoicing_rule_id IS NOT NULL
AND gt.invoicing_rule_id not in ( -2, -3);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_PRINT_OPTION'),
printing_option
FROM ar_trx_header_gt gt
WHERE gt.printing_option IS NOT NULL
AND gt.printing_option NOT IN ( 'PRI', 'NOT');
update ar_trx_header_gt gt
set gt.printing_pending=decode(gt.PRINTING_OPTION,'PRI','Y','N');
select nvl(tax_use_customer_exempt_flag,'N')
into l_tax_use_exempt_flag
from zx_product_options
where application_id = 222;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_TAX_EXEMPT_FLAG'),
default_tax_exempt_flag
FROM ar_trx_header_gt gt
WHERE default_tax_exempt_flag IS NOT NULL
AND NOT EXISTS (
select 'X'
from ar_lookups AL1
where AL1.lookup_type = 'TAX_CONTROL_FLAG'
and (AL1.lookup_code in ('R','S')
or (AL1.lookup_code = 'E' and
l_tax_use_exempt_flag = 'Y'))
and gt.default_tax_exempt_flag = AL1.lookup_code );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_TRX_STATUS'),
status_trx
FROM ar_trx_header_gt
WHERE status_trx IS NOT NULL
AND status_trx not in ( 'OP','CL','PEN','VD');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_FIN_CHARGE'),
finance_charges
FROM ar_trx_header_gt
WHERE finance_charges IS NOT NULL
AND finance_charges not in ( 'Y','N');
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INAVLID_CROSS_REF'),
related_customer_trx_id
FROM ar_trx_header_gt gt
WHERE gt.related_customer_trx_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
FROM ra_customer_trx trx, ra_batch_sources bs, ar_lookups look,
ra_cust_trx_types types
where trx.batch_source_id = bs.batch_source_id
and trx.cust_trx_type_id = types.cust_trx_type_id
and look.lookup_type = 'INV/CM'
and types.type = look.lookup_code
and types.type <> 'BR'
and trx.complete_flag = 'Y'
and trx.customer_trx_id = gt.related_customer_trx_id
and trx.bill_to_customer_id IN
(
select distinct cr.cust_account_id
from hz_cust_acct_relate cr
where cr.related_cust_account_id = gt.bill_to_customer_id
and cr.status = 'A'
and cr.bill_to_flag ='Y'
--union
--select to_number(gt.bill_to_customer_id)
--from dual
UNION
SELECT acc.cust_account_id
FROM ar_paying_relationships_v rel,
hz_cust_accounts acc
WHERE rel.party_id = acc.party_id
AND rel.related_cust_account_id = gt.bill_to_customer_id
AND gt.trx_date BETWEEN effective_start_date
AND effective_end_date));
select hdr.trx_header_id, hdr.gl_date, hdr.invoicing_rule_id,
NVL(tt.post_to_gl, 'N') post_to_gl
from ar_trx_header_gt hdr,
ra_cust_trx_types tt
where tt.cust_trx_type_id = hdr.cust_trx_type_id;
SELECT closing_status
INTO l_period_status
FROM gl_period_statuses g,
gl_sets_of_books b
WHERE b.set_of_books_id = g.set_of_books_id
AND g.set_of_books_id = arp_global.set_of_books_id
AND g.period_type = b.accounted_period_type
AND g.application_id = 222
AND g.adjustment_period_flag = 'N'
AND closing_status <> 'C'
AND trunc(cglDateRec.gl_date) between start_date and end_date;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
values
( cglDateRec.trx_header_id,
arp_standard.fnd_message('AR_INAPI_NO_PERIOD_FOR_GL_DATE'),
cglDateRec.gl_date );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
values
( cglDateRec.trx_header_id,
arp_standard.fnd_message('AR_INAPI_BAD_GL_DATE_FOR_ADV'),
cglDateRec.gl_date );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
values
( cglDateRec.trx_header_id,
arp_standard.fnd_message('AR_INAPI_BAD_GL_DATE_FOR_ARR'),
cglDateRec.gl_date );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
values
( cglDateRec.trx_header_id,
arp_standard.fnd_message('AR_TAPI_NO_PERIOD_FOR_GL_DATE'),
cglDateRec.gl_date );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
values
( cglDateRec.trx_header_id,
arp_standard.fnd_message('AR_RAXTRX-1785'),
cglDateRec.gl_date );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_AGREEMENT_ID'),
agreement_id
FROM ar_trx_header_gt gt
WHERE gt.agreement_id IS NOT NULL
AND NOT EXISTS (
SELECT 'X'
from hz_cust_accounts cust_acct,
hz_parties party,
so_agreements a,
qp_lookups sl
where a.agreement_type_code = sl.lookup_code
and sl.lookup_type = 'QP_AGREEMENT_TYPE'
and a.customer_id = cust_acct.cust_account_id(+)
and cust_acct.party_id = party.party_id(+)
and a.customer_id in ( select cr.cust_account_id
from hz_cust_acct_relate cr
where related_cust_account_id =
gt.bill_to_customer_id
and cr.status = 'A'
and cr.bill_to_flag='Y'
union
select to_number(gt.bill_to_customer_id)
from dual
union
select -1 /* no customer case */ from dual )
and gt.trx_date between
nvl( trunc( a.start_date_active ), gt.trx_date )
and nvl( trunc( a.end_date_active ), gt.trx_date )
and gt.agreement_id = a.agreement_id);
SELECT bs.auto_batch_numbering_flag,
bs.auto_trx_numbering_flag,
bs.default_reference,
bs.default_inv_trx_type, -- trx_type_id,
bs.copy_doc_number_flag
INTO l_bs_batch_auto_num_flag,
l_bs_auto_trx_num_flag,
l_dft_ref,
l_cust_trx_type_id,
l_copy_doc_number_flag
FROM RA_BATCH_SOURCES bs
WHERE batch_source_id = (SELECT gt.batch_source_id
FROM ar_trx_header_gt gt
WHERE rownum =1 );
UPDATE ar_trx_header_gt
SET auto_batch_numbering_flag = l_bs_batch_auto_num_flag,
auto_trx_numbering_flag = l_bs_auto_trx_num_flag,
copy_doc_number_flag = l_copy_doc_number_flag,
cust_trx_type_id = NVL(cust_trx_type_id, l_cust_trx_type_id),
ct_reference = decode(l_dft_ref,
1, interface_header_attribute1,
2, interface_header_attribute2,
3, interface_header_attribute3,
4, interface_header_attribute4,
5, interface_header_attribute5,
6, interface_header_attribute6,
7, interface_header_attribute7,
8, interface_header_attribute8,
9, interface_header_attribute9,
10, interface_header_attribute10,
11, interface_header_attribute11,
12, interface_header_attribute12,
13, interface_header_attribute13,
14, interface_header_attribute14,
15, interface_header_attribute15,
NULL );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT gt.trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_BATCH_SOURCE'),
gt.batch_source_id
FROM ar_trx_header_gt gt;
SELECT distinct cust_trx_type_id
FROM ar_trx_header_gt;
SELECT default_printing_option,
default_status,
allow_freight_flag,
tax_calculation_flag,
allow_overapplication_flag,
creation_sign,
natural_application_only_flag,
accounting_affect_flag,
name,
type
INTO l_default_printing_option,
l_default_status,
l_allow_freight_flag,
l_tax_calculation_flag,
l_allow_overapplication_flag,
l_creation_sign,
l_natural_application_flag,
l_accounting_affect_flag,
l_cust_trx_type_name,
l_trx_type
FROM ra_cust_trx_types
WHERE cust_trx_type_id = cust_trx_type_rec.cust_trx_type_id;
UPDATE ar_trx_header_gt
SET printing_option = nvl(printing_option,l_default_printing_option),
status_trx = NVL(status_trx,l_default_status),
allow_freight_flag = l_allow_freight_flag,
tax_calculation_flag = l_tax_calculation_flag,
allow_overapplication_flag = l_allow_overapplication_flag,
creation_sign = l_creation_sign,
natural_application_only_flag = l_natural_application_flag,
accounting_affect_flag = l_accounting_affect_flag,
cust_trx_type_name = l_cust_trx_type_name,
trx_class = l_trx_type
WHERE cust_trx_type_id = cust_trx_type_rec.cust_trx_type_id;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT gt.trx_header_id,
arp_standard.fnd_message('ARTA_INVALID_TRX_TYPE'),
gt.cust_trx_type_id
FROM ar_trx_header_gt gt
WHERE gt.cust_trx_type_id = cust_trx_type_rec.cust_trx_type_id;
UPDATE ar_trx_header_gt gt
SET gt.primary_salesrep_id =
( SELECT SU.PRIMARY_SALESREP_ID
FROM HZ_CUST_ACCT_SITES ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_SITE_USES SU,
FND_TERRITORIES_VL T
WHERE ACCT_SITE.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.COUNTRY = T.TERRITORY_CODE
AND ACCT_SITE.CUST_ACCOUNT_ID = gt.bill_to_customer_id
AND SU.SITE_USE_ID = NVL(gt.bill_to_site_use_id, SU.SITE_USE_ID)
AND SU.SITE_USE_CODE = 'BILL_TO'
AND (SU.SITE_USE_ID = gt.bill_to_site_use_id
OR (gt.bill_to_site_use_id IS NULL
AND SU.STATUS = 'A' AND ACCT_SITE.STATUS = 'A' ))
AND SU.PRIMARY_FLAG = 'Y' )
WHERE gt.primary_salesrep_id IS NULL;
UPDATE ar_trx_header_gt gt
SET gt.bill_to_customer_id =
(SELECT cust_acct.cust_account_id
FROM hz_cust_accounts cust_acct
WHERE cust_acct.account_number = gt.bill_to_account_number
AND cust_acct.status = 'A'
AND gt.bill_to_customer_id IS NULL
AND gt.bill_to_account_number IS NOT NULL
)
WHERE gt.bill_to_customer_id IS NULL
AND gt.bill_to_account_number IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.bill_to_customer_id =
(SELECT cust_acct.cust_account_id
FROM hz_cust_accounts cust_acct
WHERE cust_acct.account_number = gt.ship_to_account_number
AND cust_acct.status = 'A'
AND gt.bill_to_customer_id IS NULL
AND gt.ship_to_account_number IS NOT NULL
)
WHERE gt.bill_to_customer_id IS NULL
AND gt.bill_to_account_number IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.bill_to_customer_id = gt.ship_to_customer_id
WHERE gt.bill_to_customer_id IS NULL
AND gt.ship_to_customer_id IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.bill_to_customer_id =
(SELECT cust_acct.cust_account_id
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
AND party.party_name = gt.bill_to_customer_name
AND gt.bill_to_customer_id IS NULL
AND gt.bill_to_customer_name IS NOT NULL
)
WHERE gt.bill_to_customer_id IS NULL
AND gt.bill_to_customer_name IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET bill_to_site_use_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE site_use_code = 'BILL_TO'
AND cust_acct_site_id = gt.bill_to_address_id
AND gt.bill_to_site_use_id IS NULL
AND gt.bill_to_address_id IS NOT NULL)
WHERE gt.bill_to_site_use_id IS NULL
AND gt.bill_to_address_id IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET bill_to_site_use_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE primary_flag = 'Y'
AND site_use_code = 'BILL_TO'
AND cust_acct_site_id IN (
SELECT cust_acct_site_id
FROM hz_cust_acct_sites
WHERE cust_account_id = gt.bill_to_customer_id))
WHERE gt.bill_to_site_use_id IS NULL;
select sgt.bill_to_site_use_id, sgt.bill_to_customer_id,
sgt.paying_customer_id
from ar_trx_header_gt sgt
WHERE sgt.bill_to_customer_id = sgt.paying_customer_id;
UPDATE ar_trx_header_gt
set paying_customer_id = bill_to_customer_id
WHERE paying_customer_id IS NULL;
UPDATE ar_trx_header_gt ugt
set ugt.paying_site_use_id = cSiteUSeRec.bill_to_site_use_id
WHERE ugt.paying_site_use_id IS NULL
AND ugt.paying_customer_id = cSiteUSeRec.paying_customer_id;
UPDATE ar_trx_header_gt gt
SET paying_site_use_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE primary_flag = 'Y'
AND site_use_code = 'BILL_TO'
AND cust_acct_site_id IN (
SELECT cust_acct_site_id
FROM hz_cust_acct_sites
WHERE cust_account_id = gt.paying_customer_id))
WHERE gt.paying_site_use_id IS NULL;
UPDATE ar_trx_header_gt gt
SET bill_to_address_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE site_use_code = 'BILL_TO'
AND site_use_id= gt.bill_to_site_use_id
AND gt.bill_to_address_id IS NULL
AND gt.bill_to_site_use_id IS NOT NULL)
WHERE gt.bill_to_address_id IS NULL
AND gt.bill_to_site_use_id IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.bill_to_address_id = (
SELECT su.cust_acct_site_id
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su,
fnd_territories_vl t
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND loc.country = t.territory_code
AND acct_site.cust_account_id = gt.bill_to_customer_id
AND SU.SITE_USE_ID = NVL(gt.bill_to_site_use_id, SU.SITE_USE_ID)
AND SU.SITE_USE_CODE = 'BILL_TO'
AND (SU.SITE_USE_ID = gt.bill_to_site_use_id
OR (gt.bill_to_site_use_id IS NULL
AND SU.STATUS = 'A' AND ACCT_SITE.STATUS = 'A' ))
AND SU.PRIMARY_FLAG = 'Y' )
WHERE gt.bill_to_address_id IS NULL;
SELECT distinct bill_to_site_use_id
FROM ar_trx_header_gt
WHERE bill_to_site_use_id IS NOT NULL
AND remit_to_address_id IS NULL;
SELECT su.site_use_id,
loc.state,
loc.country,
loc.postal_code
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND su.site_use_id = l_site_use_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id;
UPDATE ar_trx_header_gt gt
SET remit_to_address_id = (
SELECT acct_site.cust_acct_site_id
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
fnd_territories_vl territory,
ra_remit_tos rt
WHERE NVL( acct_site.status, 'A' ) = 'A'
AND acct_site.cust_acct_site_id = rt.address_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND rt.status = 'A'
AND rt.country = bill_to_site_rec.country
AND loc.country = territory.territory_code
AND (
bill_to_site_rec.state = NVL( rt.state, bill_to_site_rec.state )
OR
(
bill_to_site_rec.state IS NULL AND
rt.state IS NULL
)
OR (
bill_to_site_rec.state IS NULL AND
bill_to_site_rec.postal_code <= NVL( rt.postal_code_high,
bill_to_site_rec.postal_code ) AND
bill_to_site_rec.postal_code >= NVL( rt.postal_code_low,
bill_to_site_rec.postal_code ) AND
(
postal_code_low IS NOT NULL
OR postal_code_high IS NOT NULL
)
)
)
AND (
(
bill_to_site_rec.postal_code <= NVL( rt.postal_code_high,
bill_to_site_rec.postal_code ) AND
bill_to_site_rec.postal_code >= NVL( rt.postal_code_low,
bill_to_site_rec.postal_code )
)
OR (
bill_to_site_rec.postal_code IS NULL AND
rt.postal_code_low IS NULL AND
rt.postal_code_high IS NULL
)
) and rownum = 1)
WHERE gt.bill_to_site_use_id = bill_to_site_rec.site_use_id
AND gt.remit_to_address_id IS NULL;
SELECT distinct bill_to_customer_id,
bill_to_address_id
FROM ar_trx_header_gt
WHERE bill_to_contact_id IS NULL;
SELECT distinct ACCT_ROLE.CUST_ACCOUNT_ROLE_ID -- CONTACT_ID
INTO l_contact_id
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,HZ_PARTIES PARTY,HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ACCT_ROLE.CUST_ACCOUNT_ID = bill_to_customer_id_rec.bill_to_customer_id
AND ACCT_ROLE.CUST_ACCT_SITE_ID = bill_to_customer_id_rec.bill_to_address_id
AND ACCT_ROLE.STATUS = 'A' ;
UPDATE ar_trx_header_gt
SET bill_to_contact_id = l_contact_id
WHERE bill_to_customer_id = bill_to_customer_id_rec.bill_to_customer_id
AND bill_to_address_id = bill_to_customer_id_rec.bill_to_address_id
AND bill_to_contact_id IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.ship_to_customer_id =
(SELECT cust_acct.cust_account_id
FROM hz_cust_accounts cust_acct
WHERE cust_acct.account_number = gt.ship_to_account_number
AND cust_acct.status = 'A'
AND gt.ship_to_customer_id IS NULL
AND gt.ship_to_account_number IS NOT NULL
)
WHERE gt.ship_to_customer_id IS NULL
AND gt.ship_to_account_number IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.ship_to_customer_id =
(SELECT cust_acct.cust_account_id
FROM hz_cust_accounts cust_acct,
hz_parties party
WHERE cust_acct.party_id = party.party_id
AND party.party_name = gt.ship_to_customer_name
AND cust_acct.status = 'A'
AND gt.ship_to_customer_id IS NULL
AND gt.ship_to_customer_name IS NOT NULL
)
WHERE gt.ship_to_customer_id IS NULL
AND gt.ship_to_customer_name IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET ship_to_address_id = (
SELECT site_use_id
FROM hz_cust_site_uses
WHERE site_use_code = 'SHIP_TO'
AND site_use_id= gt.ship_to_site_use_id
AND gt.ship_to_address_id IS NULL
AND gt.ship_to_site_use_id IS NOT NULL)
WHERE gt.ship_to_address_id IS NULL
AND gt.ship_to_site_use_id IS NOT NULL;
UPDATE ar_trx_header_gt gt
SET gt.ship_to_address_id = (
SELECT su.cust_acct_site_id
FROM hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses su,
fnd_territories_vl t
WHERE acct_site.cust_acct_site_id = su.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND loc.country = t.territory_code
AND acct_site.cust_account_id = gt.ship_to_customer_id
AND su.site_use_id = nvl(gt.ship_to_site_use_id, su.site_use_id)
AND su.site_use_code = 'ship_to'
AND (su.site_use_id = gt.ship_to_site_use_id
or (gt.ship_to_site_use_id IS NULL
AND su.status = 'a' AND acct_site.status = 'a' ))
AND su.primary_flag = 'y' )
WHERE gt.ship_to_address_id IS NULL;
SELECT distinct ship_to_customer_id,
ship_to_address_id
FROM ar_trx_header_gt
WHERE ship_to_contact_id IS NULL;
SELECT distinct ACCT_ROLE.CUST_ACCOUNT_ROLE_ID -- CONTACT_ID
INTO l_contact_id
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,HZ_PARTIES PARTY,HZ_RELATIONSHIPS REL,
HZ_ORG_CONTACTS ORG_CONT,HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND ACCT_ROLE.CUST_ACCOUNT_ID = ship_to_customer_id_rec.ship_to_customer_id
AND ACCT_ROLE.CUST_ACCT_SITE_ID = ship_to_customer_id_rec.ship_to_address_id
AND ACCT_ROLE.STATUS = 'A' ;
UPDATE ar_trx_header_gt
SET ship_to_contact_id = l_contact_id
WHERE ship_to_customer_id = ship_to_customer_id_rec.ship_to_customer_id
AND ship_to_address_id = ship_to_customer_id_rec.ship_to_address_id;
SELECT /*+ LEADING(gt) */ st.territory_id territory_id, gt.primary_salesrep_id
FROM ra_salesrep_territories st, ar_trx_header_gt gt
WHERE st.salesrep_id = gt.primary_salesrep_id
AND 'A' = NVL(st.status(+), 'A')
AND gt.trx_date BETWEEN NVL(st.start_date_active(+), gt.trx_date )
AND NVL(st.end_date_active(+), gt.trx_date );
SELECT /*+ LEADING(gt) */ hz.territory_id, gt.bill_to_site_use_id
FROM HZ_CUST_SITE_USES hz, ar_trx_header_gt gt
WHERE hz.site_use_id = gt.bill_to_site_use_id;
SELECT /*+ LEADING(gt) */ site_uses.territory_id, gt.ship_to_site_use_id,
gt.ship_to_customer_id
FROM HZ_CUST_SITE_USES site_uses,
ar_trx_header_gt gt
WHERE site_uses.SITE_USE_CODE = 'SHIP_TO'
AND site_uses.site_use_id = gt.ship_to_site_use_id
AND site_uses.primary_flag = 'Y';
UPDATE ar_trx_header_gt
SET territory_id = cBillToRec.territory_id
WHERE bill_to_site_use_id = cBillToRec.bill_to_site_use_id;
UPDATE ar_trx_header_gt
SET territory_id = cShipToRec.territory_id
WHERE ship_to_site_use_id = cShipToRec.ship_to_site_use_id
AND ship_to_customer_id = cShipToRec.ship_to_customer_id;
UPDATE ar_trx_header_gt
SET territory_id = cSalesTerRec.territory_id
WHERE primary_salesrep_id = cSalesTerRec.primary_salesrep_id;
/* single update to default value */
UPDATE ar_trx_header_gt
SET legal_entity_id = arp_legal_entity_util.get_default_le(
sold_to_customer_id,
bill_to_customer_id,
cust_trx_type_id,
batch_source_id)
WHERE legal_entity_id is NULL;
UPDATE ar_trx_header_gt gt
SET bill_to_site_use_id = (select site_use_id
from hz_cust_site_uses
where primary_flag = 'Y'
and site_use_code = 'BILL_TO'
and cust_acct_site_id in (
select cust_acct_site_id from hz_cust_acct_sites
where cust_account_id = gt.bill_to_customer_id))
WHERE gt.bill_to_site_use_id IS NULL;
UPDATE ar_trx_header_gt gt
SET term_id = (SELECT tl.term_id
FROM ra_terms t_su,
ra_terms t_cp1,
ra_terms t_cp2,
ra_terms tl, /*Bug 3984916*/
hz_customer_profiles cp1,
hz_customer_profiles cp2,
hz_cust_site_uses su
WHERE gt.bill_to_customer_id = cp1.cust_account_id(+)
AND su.site_use_id = gt.bill_to_site_use_id
AND cp2.cust_account_id = gt.bill_to_customer_id
AND su.site_use_id = cp1.site_use_id(+)
AND cp2.site_use_id IS NULL
AND su.payment_term_id = t_su.term_id(+)
AND cp1.standard_terms = t_cp1.term_id(+)
AND cp2.standard_terms = t_cp2.term_id(+)
AND NVL(
t_su.term_id,
NVL(
t_cp1.term_id,
t_cp2.term_id
)
) = tl.term_id
AND gt.trx_date BETWEEN t_su.start_date_active(+)
AND NVL(t_su.end_date_active(+),
gt.trx_date)
AND gt.trx_date BETWEEN t_cp1.start_date_active(+)
AND NVL(t_cp1.end_date_active(+),
gt.trx_date)
AND gt.trx_date BETWEEN t_cp2.start_date_active(+)
AND NVL(t_cp2.end_date_active(+),
gt.trx_date))
WHERE gt.term_id IS NULL
AND gt.trx_class in ('INV','DM') -- added for ER 5869149
AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) = 'N';
UPDATE ar_trx_header_gt gt
SET term_id = ar_bfb_utils_pvt.get_default_term(
gt.cust_trx_type_id,
gt.trx_date,
gt.org_id,
gt.bill_to_site_use_id,
gt.bill_to_customer_id)
WHERE gt.term_id IS NULL
AND gt.trx_class in ('INV','DM') -- added for ER 5869149 **addtional condition**
AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) in ('A','S');
UPDATE ar_trx_header_gt gt
SET billing_date = ar_bfb_utils_pvt.get_billing_date
(ar_bfb_utils_pvt.get_billing_cycle(gt.term_id),
nvl(gt.trx_date,sysdate))
WHERE gt.term_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'Y'
AND gt.billing_date IS NULL
AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) in ('A','S')
AND nvl(ar_bfb_utils_pvt.get_cycle_type
(ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'RECURRING';
UPDATE ar_trx_header_gt gt
SET billing_date = ar_bfb_utils_pvt.get_billing_date
(ar_bfb_utils_pvt.get_billing_cycle(gt.term_id),
gt.billing_date)
WHERE gt.term_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'Y'
AND gt.billing_date IS NOT NULL
AND billing_date <> ar_bfb_utils_pvt.get_billing_date(
ar_bfb_utils_pvt.get_billing_cycle(term_id),
gt.billing_date)
AND nvl(ar_bfb_utils_pvt.get_cycle_type
(ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'RECURRING';
UPDATE ar_trx_header_gt gt
SET term_due_date = trunc(arpt_sql_func_util.get_First_Due_Date(
gt.term_id, NVL(gt.trx_date,sysdate)))
WHERE gt.term_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'N'
AND NOT EXISTS
(SELECT 'X'
FROM ar_trx_errors_gt errgt
WHERE errgt.trx_header_id = gt.trx_header_id);
UPDATE ar_trx_header_gt gt
SET term_due_date = ar_bfb_utils_pvt.get_due_date(gt.billing_date, gt.term_id)
WHERE gt.term_id IS NOT NULL
AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'Y'
AND gt.billing_date IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM ar_trx_errors_gt errgt
WHERE errgt.trx_header_id = gt.trx_header_id);
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_ID'),
bill_to_address_id
FROM ar_trx_header_gt gt
WHERE gt.bill_to_customer_id IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_BILL_ADDR_ID'),
bill_to_address_id
FROM ar_trx_header_gt gt
WHERE gt.bill_to_address_id IS NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_UOM_NOT_REQ'),
gt.uom_code
FROM ar_trx_lines_gt gt
WHERE gt.uom_code IS NOT NULL
AND gt.line_type <> 'LINE';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT gt.trx_header_id,
gt.trx_line_id,
arp_standard.fnd_message('AR_INAPI_CM_UOM_NOT_ALLOWED'),
gt.uom_code
FROM ar_trx_lines_gt gt, ar_trx_header_gt gt2
WHERE gt.uom_code IS NOT NULL
AND gt.line_type = 'LINE'
AND gt2.trx_header_id = gt.trx_header_id
AND gt2.trx_class = 'CM'; -- added for ER 5869149
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT gt.trx_header_id,
gt.trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_UOM'),
gt.uom_code
FROM ar_trx_lines_gt gt , ar_trx_header_gt gt2
WHERE gt.uom_code IS NOT NULL
AND gt.line_type = 'LINE'
AND gt2.trx_header_id = gt.trx_header_id
AND gt2.trx_class <> 'CM' -- added for ER 5869149
AND NOT EXISTS (
SELECT 'X'
FROM mtl_item_uoms_view uom
where organization_id = nvl(gt.warehouse_id,l_so_org_id) /*Bug 3752043*/
and inventory_item_id = gt.inventory_item_id
and uom.uom_code = gt.uom_code
union
select 'X'
from mtl_units_of_measure uom
where sysdate <= nvl(trunc(uom.disable_date), sysdate)
and gt.inventory_item_id is null
and gt.uom_code = uom.uom_code);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_TAX_LINE'),
gt.vat_tax_id
FROM ar_trx_lines_gt gt
WHERE extended_amount IS NULL
AND line_type in ('TAX', 'FREIGHT');
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_QTY_NOT_ALLOWED'),
gt.vat_tax_id
FROM ar_trx_lines_gt gt
WHERE line_type in ('TAX', 'FREIGHT')
AND (quantity_invoiced IS NOT NULL
OR unit_selling_price IS NOT NULL );
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_RAXTRX-1706B'),
gt.tax_regime_code || ', ' ||
gt.tax || ', ' ||
gt.tax_status_code || ', ' ||
gt.tax_rate_code || ', ' ||
gt.tax_jurisdiction_code
FROM ar_trx_lines_gt gt
WHERE line_type = 'TAX'
AND gt.tax_regime_code ||
gt.tax ||
gt.tax_status_code ||
gt.tax_rate_code ||
gt.tax_jurisdiction_code IS NULL;
select nvl(tax_use_customer_exempt_flag,'N')
into l_tax_use_exempt_flag
from zx_product_options
where application_id = 222;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INV_TAX_EXEMPT_FLAG'),
tax_exempt_flag
FROM ar_trx_lines_gt
WHERE tax_exempt_flag IS NOT NULL
AND NOT EXISTS
( select 'X'
from ar_lookups AL1
where AL1.lookup_type = 'TAX_CONTROL_FLAG'
and (AL1.lookup_code in ('R','S')
or (AL1.lookup_code = 'E'
and 'Y' = l_tax_use_exempt_flag))
/* 4257557 - This logic is temporarily disabled
or (AL1.lookup_code = 'O'
and exists (select 1 from ar_system_parameters where tax_database_view_set in ('_V','_A') ))) */
and tax_exempt_flag = lookup_code);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_REASON_NOT_REQ')
FROM ar_trx_lines_gt
WHERE tax_exempt_flag = 'E'
AND TAX_EXEMPT_REASON_CODE IS NULL;
UPDATE ar_trx_lines_gt
SET TAX_EXEMPT_REASON_CODE = null,
tax_exempt_number = null
WHERE tax_exempt_flag in ( 'R', 'S');
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_TAX_EXEMPT_CODE'),
TAX_EXEMPT_REASON_CODE
FROM ar_trx_lines_gt
WHERE TAX_EXEMPT_REASON_CODE IS NOT null
AND NOT EXISTS (
SELECT 'X'
FROM ar_lookups
WHERE lookup_type = 'TAX_REASON'
AND enabled_flag = 'Y'
AND trx_date between start_date_active and nvl(end_date_active,trx_date)
AND lookup_code = TAX_EXEMPT_REASON_CODE );
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_REASON_NOT_REQ'),
gt.reason_code
FROM ar_trx_lines_gt gt
WHERE gt.reason_code IS NOT NULL
AND gt.line_type = 'TAX';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value )
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_REASON'),
gt.reason_code
FROM ar_trx_lines_gt gt
WHERE gt.reason_code IS NOT NULL
AND NOT EXISTS (
select 'X'
from ar_lookups
where lookup_type = 'INVOICING_REASON'
and enabled_flag = 'Y'
and lookup_code = gt.reason_code
and sysdate -- g_trx_date
between start_date_active and nvl(end_date_active, sysdate));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INV_DESC_NULL')
FROM ar_trx_lines_gt gt
WHERE gt.description IS NULL
AND gt.inventory_item_id IS NULL
AND gt.memo_line_id IS NULL
AND gt.line_type = 'LINE';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_DAPI_MEMO_NAME_INVALID')
FROM ar_trx_lines_gt gt
WHERE gt.memo_line_id IS NOT NULL
AND gt.line_type = 'LINE'
AND NOT EXISTS (
SELECT 'X'
FROM ar_memo_lines m
WHERE m.memo_line_id = gt.memo_line_id
AND m.line_type = 'LINE'
AND sysdate between start_date and nvl(end_date,sysdate) );
/*This update should be fired only when the description isn't populated
initially*/
/* 4536358 - changed ar_memo_lines_all_tl to
ar_memo_lines_tl. Removed rownum = 1. This was
just masking issues that would arise due to
cartesian join. */
UPDATE ar_trx_lines_gt gt
SET description = ( SELECT description
FROM ar_memo_lines_tl
WHERE memo_line_id = gt.memo_line_id
AND language = USERENV('LANG')
AND rownum = 1)
WHERE gt.memo_line_id IS NOT NULL
AND description is NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
SELECT gt.trx_header_id,
arp_standard.fnd_message('AR_INAPI_QTY_NOT_NULL')
FROM ar_trx_lines_gt gt ,
ar_trx_header_gt gt2
WHERE gt.quantity_invoiced IS NULL
AND gt.trx_header_id = gt2.trx_header_id
AND gt2.trx_class <> 'CM' -- added for ER 5869149
AND gt.line_type = 'LINE';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
SELECT gt.trx_header_id,
arp_standard.fnd_message('AR_INAPI_UNIT_PRICE_NOT_NULL')
FROM ar_trx_lines_gt gt ,
ar_trx_header_gt gt2
WHERE gt.unit_selling_price IS NULL
AND gt.trx_header_id = gt2.trx_header_id
AND gt2.trx_class <> 'CM' -- Added for ER 5869149
AND gt.line_type = 'LINE';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message,
invalid_value )
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_INAVLID_LINE_TYPE'),
gt.line_type
FROM ar_trx_lines_gt gt
WHERE gt.line_type not in ('LINE', 'TAX', 'FREIGHT');
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_LINE_NUM_NOT_NULL')
FROM ar_trx_lines_gt gt
WHERE gt.line_number IS NULL;
/* INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message
SELECT trx_header_id,
'Duplicate Line Number'
FROM ar_trx_lines_gt gt
WHERE gt.line_number IS NOT NULL
AND ; */
SELECT trx_header_id, count(*) number_of_freight_lines
FROM ar_trx_lines_gt
WHERE line_type = 'FREIGHT'
group by trx_header_id;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_HDR_ID_NOT_NULL')
FROM ar_trx_lines_gt gt
WHERE gt.trx_header_id IS NULL
OR gt.trx_line_id IS NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_LINK_LINE_ID_NOT_NULL')
FROM ar_trx_lines_gt gt
WHERE gt.line_type = 'TAX'
AND gt.link_to_trx_line_id IS NULL;
SELECT nvl(gt.allow_freight_flag, 'N'), gt.cust_trx_type_id
INTO l_allow_freight_flag, l_cust_trx_type_id
FROM ar_trx_header_gt gt
WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
VALUES
( c_freight_rec.trx_header_id,
arp_standard.fnd_message('AR_TAPI_FREIGHT_NOT_ALLOWED'));
SELECT count(*)
INTO l_header_freight_count
FROM ar_trx_lines_gt
WHERE trx_header_id = c_freight_rec.trx_header_id
AND link_to_trx_line_id IS NULL;
SELECT count(*)
INTO l_line_freight_count
FROM ar_trx_lines_gt
WHERE trx_header_id = c_freight_rec.trx_header_id
AND link_to_trx_line_id IS NOT NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
SELECT trx_header_id,
arp_standard.fnd_message('AR_TAPI_TOO_MANY_FREIGHT_LINE')
FROM ar_trx_header_gt gt
WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_VIA')
FROM ar_trx_lines_gt gt
WHERE gt.line_type = 'FREIGHT'
AND gt.ship_via IS NOT NULL
AND NOT EXISTS (
SELECT 'X' FROM
org_freight fr
WHERE fr.organization_id = gt.org_id
and gt.trx_date <= nvl(trunc(disable_date), gt.trx_date)
and freight_code = gt.ship_via);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_FOB')
FROM ar_trx_lines_gt gt
WHERE gt.line_type = 'FREIGHT'
AND gt.fob_point IS NOT NULL
AND NOT EXISTS (
SELECT 'X' FROM
ar_lookups
WHERE lookup_type = 'FOB'
and lookup_code = gt.fob_point
and gt.trx_date between start_date_active and nvl(end_date_active, gt.trx_date));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_EXT_AMT_NOT_NULL')
FROM ar_trx_lines_gt gt
WHERE gt.line_type = 'FREIGHT'
AND gt.extended_amount IS NULL;
SELECT gt.trx_header_id, gt.trx_line_id,
gt2.creation_sign, gt.extended_amount,
gt.revenue_amount, gt.quantity_invoiced,
gt.unit_selling_price
FROM ar_trx_lines_gt gt,
ar_trx_header_gt gt2
WHERE gt.line_type ='LINE'
AND gt2.trx_header_id = gt.trx_header_id
AND gt2.trx_class = 'CM';
select
trx_line_id,extended_amount,quantity_invoiced,unit_selling_price,currency_code
from ar_trx_lines_gt
where extended_amount IS NULL;
UPDATE ar_trx_lines_gt
SET extended_amount =ext_amt
WHERE extended_amount IS NULL
AND trx_line_id= I.trx_line_id;
UPDATE ar_trx_lines_gt
SET revenue_amount = extended_amount
WHERE revenue_amount IS NULL
AND line_type <> 'TAX';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
VALUES
( c_line_rec.trx_header_id,
c_line_rec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_AMT_SIGN_INVALID'));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
VALUES
( c_line_rec.trx_header_id,
c_line_rec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_AMT_SIGN_INVALID'));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
VALUES
( c_line_rec.trx_header_id,
c_line_rec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_AMT_INVALID'));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message)
SELECT trx_header_id,
arp_standard.fnd_message('AR_INAPI_DUP_DOC_SEQUENCE')
FROM ar_trx_header_gt gt
WHERE gt.cust_trx_type_id IS NOT NULL
AND gt.doc_sequence_value IS NOT NULL
AND EXISTS (
SELECT 'Y' --already exists
FROM ra_recur_interim ri,
ra_customer_trx ct
WHERE ct.customer_trx_id = ri.customer_trx_id
AND ct.cust_trx_type_id = gt.cust_trx_type_id
AND ri.doc_sequence_value = gt.doc_sequence_value
AND NVL(ri.new_customer_trx_id, -98)
<> NVL(gt.customer_trx_id, -99)
UNION
SELECT 'Y' --already exists /*Bug 4080107*/
FROM ra_cust_trx_types ctt,
ra_interface_lines ril
WHERE ril.cust_trx_type_name = ctt.name(+)
AND NVL(ril.cust_trx_type_id,
ctt.cust_trx_type_id) = gt.cust_trx_type_id
AND ril.document_number = gt.doc_sequence_value
AND NVL(ril.customer_trx_id, -98) <> NVL(gt.customer_trx_id, -99));
SELECT * FROM
ar_trx_header_gt
WHERE trx_header_id NOT IN (
SELECT trx_header_id FROM
ar_trx_errors_gt);
l_update_trx boolean := FALSE;
l_update_trx := FALSE;
INSERT into ar_trx_errors_gt (
trx_header_id,
error_message )
VALUES
( ctrxHeaderRec.trx_header_id,
'UNIQUE-ALWAYS USED');
l_update_trx := TRUE;
INSERT into ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
VALUES
( ctrxHeaderRec.trx_header_id,
arp_standard.fnd_message('AR_INAPI_DUP_DOC_SEQUENCE'),
l_doc_sequence_value );
/* 5921925 - moved update outside of doc sequence code so I could
use it for trx_number also */
IF ctrxHeaderRec.auto_trx_numbering_flag = 'Y' AND
ctrxHeaderRec.trx_number IS NULL AND
NOT l_seq_err
THEN
/* pull trx_number from sequence dynamically */
IF (ctrxHeaderRec.org_id IS NOT NULL)
THEN
l_org_str := '_'||to_char(ctrxHeaderRec.org_id);
l_trx_str := 'select ra_trx_number_' ||
REPLACE(ctrxHeaderRec.batch_source_id, '-', 'N') ||
l_org_str||
'_s.nextval trx_number from dual';
l_update_trx := TRUE;
IF l_update_trx
THEN
UPDATE ar_trx_header_gt
SET doc_sequence_value = l_doc_sequence_value,
doc_sequence_id = l_doc_sequence_id,
trx_number = DECODE(ctrxHeaderRec.copy_doc_number_flag,
'Y',NVL(to_char(l_doc_sequence_value),
NVL(l_trx_number,trx_number)),
DECODE(ctrxHeaderRec.auto_trx_numbering_flag,
'Y',l_trx_number,trx_number))
WHERE trx_header_id = ctrxHeaderRec.trx_header_id;
l_update_trx := FALSE;
SELECT * FROM
ar_trx_header_gt
WHERE trx_header_id NOT IN (
SELECT trx_header_id FROM
ar_trx_errors_gt)
AND payment_trxn_extension_id is not null;
SELECT party.party_id
INTO l_party_id
FROM hz_cust_accounts hca,
hz_parties party
WHERE hca.party_id = party.party_id
AND hca.cust_account_id = ctrxHeaderRec.paying_customer_id ;
SELECT INSTR_ASSIGNMENT_ID
INTO l_assignment_id
from iby_fndcpt_tx_extensions
where trxn_extension_id = ctrxHeaderRec.payment_trxn_extension_id;
UPDATE ar_trx_header_gt
SET payment_trxn_extension_id = o_payment_trxn_extension_id
WHERE trx_header_id = ctrxHeaderRec.trx_header_id;
INSERT into ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
VALUES
( ctrxHeaderRec.trx_header_id,
arp_standard.fnd_message('AR_CC_AUTH_FAILED'),
ctrxHeaderRec.payment_trxn_extension_id );
SELECT trx_header_id, trx_line_id,inventory_item_id, org_id
FROM ar_trx_lines_gt
WHERE inventory_item_id IS NOT NULL;
INSERT into ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
VALUES
( cItemFlexRec.trx_header_id,
cItemFlexRec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_ITEM_D'),
cItemFlexRec.inventory_item_id );
/*This update should be fired only when the description isn't populated
initially*/
-- get the description
update ar_trx_lines_gt
SET description = (
select description
from mtl_system_items_vl
WHERE inventory_item_id = cItemFlexRec.inventory_item_id
AND organization_id = pg_so_org_id)
WHERE trx_line_id = cItemFlexRec.trx_line_id
AND description is NULL;
INSERT into ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
VALUES
( cItemFlexRec.trx_header_id,
cItemFlexRec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_ITEM_ID'),
cItemFlexRec.inventory_item_id );
SELECT territory_id, trx_header_id
FROm ar_trx_header_gt
WHERE territory_id IS NOT NULL;
INSERT into ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
VALUES
( cTerritoryRec.trx_header_id,
arp_standard.fnd_message('AR_INAPI_INVALID_TERRITORY'),
cTerritoryRec.territory_id );
INSERT INTO ar_trx_errors_gt
( trx_line_id,
trx_header_id,
error_message,
Invalid_value )
select l.trx_line_id,
l.trx_header_id,
arp_standard.fnd_message('AR_RAXTRX_INV_WAREHOUSE'),
l.warehouse_id
from ar_trx_lines_gt l
where l.line_type = 'LINE'
and l.warehouse_id is not null
and not exists (select 'x'
from hr_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2,
mtl_parameters mp,
gl_sets_of_books gsob
where hou.organization_id = hoi1.organization_id
and hou.organization_id = hoi2.organization_id
and hou.organization_id = mp.organization_id
and hoi1.org_information1 = 'INV'
and hoi1.org_information2 = 'Y'
and ( hoi1.org_information_context || '') = 'CLASS'
and ( hoi2.org_information_context || '') ='Accounting Information'
and hoi2.org_information1 = to_char(l.set_of_books_id)
and l.warehouse_id = hou.organization_id
and l.trx_date <= nvl(hou.date_to, l.trx_date));
SELECT line.trx_header_id, line.trx_line_id, hdr.invoicing_rule_id,
line.ACCOUNTING_RULE_ID, line.ACCOUNTING_RULE_DURATION,
line.RULE_START_DATE,line.RULE_END_DATE,line.set_of_books_id,hdr.trx_date,
rr.type, rr.frequency, rr.occurrences
FROM ar_trx_lines_gt line, ar_trx_header_gt hdr,
ra_rules rr
WHERE hdr.invoicing_rule_id IS NOT NULL
AND line.accounting_rule_id IS NOT NULL
AND hdr.trx_header_id = line.trx_header_id
AND hdr.trx_class <> 'CM' -- Added for ER 5869149
AND line.line_type = 'LINE'
AND line.accounting_rule_id = rr.rule_id
AND rr.status = 'A';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT gt.trx_header_id,
gt.trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_RULE_NAME')
FROM ar_trx_lines_gt gt , ar_trx_header_gt gt2
WHERE gt.accounting_rule_id IS NOT NULL
AND gt2.trx_header_id = gt.trx_header_id
AND gt2.trx_class <> 'CM' -- Added for ER 5869149
AND NOT EXISTS (
SELECT 'X'
FROM ra_rules rr
WHERE rr.type in ('A', 'ACC_DUR','PP_DR_ALL','PP_DR_PP')
and rr.status = 'A'
AND rr.rule_id = gt.accounting_rule_id);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT gt.trx_header_id,
gt.trx_line_id,
arp_standard.fnd_message('AR_INAPI_RULE_NAME_NOT_NULL')
FROM ar_trx_lines_gt gt, ar_trx_header_gt gth
WHERE gt.accounting_rule_id IS NULL
AND gt.trx_header_id = gth.trx_header_id
AND gth.invoicing_rule_id IS NOT NULL
AND gth.trx_class <> 'CM' -- Added for ER 5869149
AND gt.line_type = 'LINE';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message)
SELECT gt.trx_header_id,
gt.trx_line_id,
arp_standard.fnd_message('AR_INAPI_RULE_NOT_ALLOWED')
FROM ar_trx_lines_gt gt, ar_trx_header_gt gth
WHERE (gt.accounting_rule_id IS NOT NULL
OR gth.invoicing_rule_id IS NOT NULL)
AND gt.trx_header_id = gth.trx_header_id
AND gth.trx_class = 'CM' -- Added for ER 5257046
AND gt.line_type = 'LINE';
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message) VALUES
( c_invoicing_rule_rec.trx_header_id,
c_invoicing_rule_rec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_RULE_NAME_NOT_NULL') );
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message) VALUES
( c_invoicing_rule_rec.trx_header_id,
c_invoicing_rule_rec.trx_line_id,
arp_standard.fnd_message('AR_RAXTRX_RULE_START_DT_NULL'));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message) VALUES
( c_invoicing_rule_rec.trx_header_id,
c_invoicing_rule_rec.trx_line_id,
arp_standard.fnd_message('AR_RAXTRX_RULE_END_DT_NULL'));
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message) VALUES
( c_invoicing_rule_rec.trx_header_id,
c_invoicing_rule_rec.trx_line_id,
arp_standard.fnd_message('AR_RAXTRX_RSD_LT_RED'));
UPDATE ar_trx_lines_gt a
SET ACCOUNTING_RULE_DURATION =
(SELECT COUNT(*)
FROM ar_periods gps,
ra_rules rr2,
ar_system_parameters sys,
gl_sets_of_books gl
WHERE
rr2.rule_id = a.ACCOUNTING_RULE_ID
AND rr2.frequency = gps.period_type
AND rr2.type NOT IN ('A', 'ACC_DUR')
AND sys.set_of_books_id = gl.set_of_books_id
AND gl.period_set_name = gps.period_set_name
AND ( a.RULE_START_DATE BETWEEN gps.start_date
AND gps.end_date
OR a.RULE_end_DATE BETWEEN gps.start_date
AND gps.end_date
OR gps.start_date BETWEEN a.RULE_START_DATE
AND a.RULE_end_DATE ))
WHERE trx_line_id = c_invoicing_rule_rec.trx_line_id
AND trx_header_id = c_invoicing_rule_rec.trx_header_id;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message) VALUES
( c_invoicing_rule_rec.trx_header_id,
c_invoicing_rule_rec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_RULE_DUR_NOT_NULL' ));
UPDATE ar_trx_lines_gt
SET ACCOUNTING_RULE_DURATION = c_invoicing_rule_rec.occurrences
WHERE trx_line_id = c_invoicing_rule_rec.trx_line_id
AND trx_header_id = c_invoicing_rule_rec.trx_header_id;
UPDATE ar_trx_lines_gt
SET ACCOUNTING_RULE_DURATION = c_invoicing_rule_rec.occurrences
WHERE trx_line_id = c_invoicing_rule_rec.trx_line_id
AND trx_header_id = c_invoicing_rule_rec.trx_header_id;
SELECT MIN(rs.rule_date)
FROM ra_rule_schedules rs
WHERE rs.rule_id = l_accounting_rule_id;
UPDATE ar_trx_lines_gt
SET rule_start_date = l_rule_start_date
WHERE trx_line_id = c_invoicing_rule_rec.trx_line_id
AND trx_header_id = c_invoicing_rule_rec.trx_header_id;
SELECT COUNT(*)
INTO l_period_exist
FROM gl_periods gp,
gl_sets_of_books sob,
gl_period_statuses gps
WHERE sob.set_of_books_id = l_set_of_books_id
AND gp.adjustment_period_flag = 'N'
AND gp.period_set_name = sob.period_set_name
AND gp.period_type = l_frequency
AND gp.period_name = gps.period_name
AND gp.period_type = gps.period_type
AND gp.period_year = gps.period_year
AND gp.period_num = gps.period_num
AND gp.quarter_num = gps.quarter_num
AND gp.year_start_date = gps.year_start_date
AND gp.quarter_start_date = gps.quarter_start_date
AND gp.start_date = gps.start_date
AND gp.end_date = gps.end_date
AND gps.application_id = 222
AND gps.adjustment_period_flag = 'N'
AND gps.closing_status in ('O','F')
AND sob.set_of_books_id = gps.set_of_books_id
AND gps.period_type = sob.accounted_period_type
AND c_invoicing_rule_rec.rule_start_date BETWEEN gp.start_date AND gp.end_date;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message) VALUES
( c_invoicing_rule_rec.trx_header_id,
c_invoicing_rule_rec.trx_line_id,
arp_standard.fnd_message('AR_INAPI_RULE_START_DT_NO_NULL') );
INSERT INTO ar_trx_errors_gt (
trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT trx_header_id,
trx_line_id,
arp_standard.fnd_message('AR_INAPI_INVALID_USSGL_CODE'),
default_ussgl_transaction_code
FROM ar_trx_lines_gt gt
WHERE gt.default_ussgl_transaction_code IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM gl_ussgl_transaction_codes gutc
WHERE gutc.ussgl_transaction_code = gt.default_ussgl_transaction_code
AND gutc.chart_of_accounts_id = arp_global.chart_of_accounts_id
AND gt.trx_date
BETWEEN NVL(gutc.start_date_active, gt.trx_date)
AND NVL(gutc.end_date_active, gt.trx_date));
UPDATE ar_trx_lines_gt gt
set gt.unit_selling_price =
( SELECT m.unit_std_price
FROM ar_memo_lines_vl m
WHERE m.memo_line_id = gt.memo_line_id
AND gt.memo_line_id IS NOT NULL
AND sysdate between m.start_date and nvl(m.end_date,sysdate))
WHERE gt.unit_selling_price IS NULL;
UPDATE ar_trx_lines_gt gt
set gt.uom_code =
( SELECT m.uom_code
FROM ar_memo_lines_vl m
WHERE m.memo_line_id = gt.memo_line_id
AND gt.memo_line_id IS NOT NULL
AND sysdate between m.start_date and nvl(m.end_date,sysdate))
WHERE gt.uom_code IS NULL;
SELECT trx_header_id, trx_currency,
nvl(exchange_rate_type,
p_trx_profile_rec.default_exchange_rate_type) exchange_rate_type,
trunc(nvl(exchange_date,trx_date)) exchange_date, exchange_rate
FROM ar_trx_header_gt gt
WHERE p_trx_system_parameters_rec.base_currency_code <>
trx_currency
AND nvl(exchange_rate_type,
p_trx_profile_rec.default_exchange_rate_type) <> 'User'
AND NOT EXISTS
(SELECT 'X'
FROM ar_trx_errors_gt errgt
WHERE errgt.trx_header_id = gt.trx_header_id
AND errgt.invalid_value = gt.trx_currency);
UPDATE ar_trx_header_gt
SET exchange_rate = l_exchange_rate,
exchange_date = cExchangeRateRec.exchange_date,
exchange_rate_type = cExchangeRateRec.exchange_rate_type
WHERE trx_header_id = cExchangeRateRec.trx_header_id;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
error_message,
invalid_value)
VALUES
( cExchangeRateRec.trx_header_id,
arp_standard.fnd_message('AR_INAPI_NO_EXCH_DEFINE'),
cExchangeRateRec.trx_currency);
UPDATE ar_trx_header_gt
SET exchange_date = trunc(trx_date)
WHERE exchange_rate_type = 'User'
AND exchange_date IS NULL;
INSERT INTO ar_trx_errors_gt (
trx_header_id,
error_message,
invalid_value)
SELECT trx_header_id,
arp_standard.fnd_message('AR_EXCHANGE_RATE_NEEDED'),
gt.trx_currency
FROM ar_trx_header_gt gt
WHERE gt.trx_currency IS NOT NULL
AND ( exchange_rate IS NULL
OR exchange_rate <= 0
OR exchange_date IS NULL)
AND gt.trx_currency <> p_trx_system_parameters_rec.base_currency_code;
SELECT trx_currency, paying_customer_id,
paying_site_use_id, bill_to_customer_id,
bill_to_site_use_id, trx_date, trx_header_id
FROM ar_trx_header_gt
WHERE receipt_method_id IS NULL
AND payment_trxn_extension_id IS NOT NULL;
UPDATE ar_trx_header_gt
set receipt_method_id = l_receipt_method_id
WHERE trx_header_id = cCustDetailsRec.trx_header_id;
SELECT code_combination_id, trx_dist_id, trx_line_id, trx_header_id
FROM ar_trx_dist_gt
WHERE code_combination_id IS NOT NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
trx_dist_id,
error_message,
invalid_value)
VALUES
( CccidRec.trx_header_id,
CccidRec.trx_line_id,
CccidRec.trx_dist_id,
arp_standard.fnd_message('AR_INVALID_CCID'),
CccidRec.code_combination_id);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
trx_dist_id,
error_message)
SELECT d.trx_header_id,
d.trx_line_id,
D.trx_DIST_ID,
arp_standard.fnd_message('AR_INAPI_CCID_NULL')
FROM ar_trx_DIST_gt D
WHERE code_combination_id IS NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
trx_dist_id,
error_message)
SELECT d.trx_header_id,
d.trx_line_id,
D.trx_DIST_ID,
arp_standard.fnd_message('AR_INAPI_AMT_PER_REQUIRED')
FROM ar_trx_DIST_gt D
WHERE d.PERCENT IS NULL
AND d.amount IS NULL;
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
trx_dist_id,
error_message)
SELECT d.trx_header_id,
L.trx_line_id,
D.trx_DIST_ID,
arp_standard.fnd_message('AR_INAPI_ONLY_PER_ALLOWED')
FROM ar_trx_DIST_gt D,
ar_trx_lines_GT L
WHERE (L.ACCOUNTING_RULE_ID IS NOT NULL
OR D.ACCOUNT_CLASS = 'REC')
AND d.PERCENT IS NULL
--AND L.trx_line_ID = D.trx_line_ID;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_line_id,
trx_dist_ID,
error_message)
SELECT d.trx_header_id,
L.trx_LINE_ID,
D.trx_DIST_ID,
arp_standard.fnd_message('AR_INAPI_INAVLID_LINE_TYPE')
FROM ar_trx_dist_gt D,
ar_trx_LINES_GT L
WHERE L.trx_LINE_ID = D.trx_LINE_ID
AND DECODE(D.ACCOUNT_CLASS,
'TAX', 'TAX',
'FREIGHT','FREIGHT',
'CHARGES','CHARGES',
'LINE')
<> L.LINE_TYPE;
| Update distribution lines with rounded amount |
| for account class: 'REV', 'TAX', 'FREIGHT', 'CHARGES' |
| For lines w/o accounting rules. |
+------------------------------------------------------------*/
UPDATE ar_trx_dist_gt D
SET AMOUNT =
(
SELECT
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL, ROUND(((D.PERCENT * L.extended_AMOUNT) / 100), C.PRECISION),
ROUND(((D.PERCENT * L.extended_AMOUNT) / 100) /
C.MINIMUM_ACCOUNTABLE_UNIT) *
C.MINIMUM_ACCOUNTABLE_UNIT
)
FROM FND_CURRENCIES C,
ar_trx_LINES_GT L
WHERE L.CURRENCY_CODE = C.CURRENCY_CODE
AND L.trx_LINE_ID = D.trx_LINE_ID
)
WHERE ACCOUNT_CLASS IN ('REV', 'TAX', 'FREIGHT','CHARGES')
AND D.PERCENT IS NOT NULL
AND d.amount IS NULL
AND EXISTS
(SELECT 'X'
FROM ar_trx_LINES_GT L
WHERE L.trx_LINE_ID = D.trx_LINE_ID
AND L.ACCOUNTING_RULE_ID IS NULL);
UPDATE ar_trx_dist_gt D
SET d.PERCENT =
(
SELECT DECODE(L.extended_AMOUNT,
0, 0, /* set percent =0 if line amt = 0 */
ROUND(100 * (D.AMOUNT / L.extended_AMOUNT), 4))
FROM FND_CURRENCIES C,
ar_trx_LINES_GT L
WHERE L.CURRENCY_CODE = C.CURRENCY_CODE
AND L.trx_LINE_ID = D.trx_LINE_ID
)
WHERE ACCOUNT_CLASS in ('REV', 'TAX', 'FREIGHT', 'CHARGES')
AND D.AMOUNT IS NOT NULL
AND d.percent IS NULL
AND EXISTS
(SELECT 'X'
FROM ar_trx_LINES_GT L
WHERE L.trx_LINE_ID = D.trx_LINE_ID
AND L.ACCOUNTING_RULE_ID IS NULL);
| Update distribution lines with rounded acctd_amount |
| for account class: 'REV', 'TAX', 'FREIGHT', 'CHARGES' |
| For lines w/o accounting rules. |
+------------------------------------------------------------*/
UPDATE ar_trx_dist_gt D
SET ACCTD_AMOUNT =
(
SELECT
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL, ROUND(((D.PERCENT * L.extended_AMOUNT * nvl(h.exchange_rate,1)) / 100), C.PRECISION),
ROUND(((D.PERCENT * L.extended_AMOUNT * nvl(h.exchange_rate,1)) / 100) /
C.MINIMUM_ACCOUNTABLE_UNIT) *
C.MINIMUM_ACCOUNTABLE_UNIT
)
FROM FND_CURRENCIES C,
ar_trx_LINES_GT L,
ar_trx_header_gt h
WHERE C.CURRENCY_CODE = p_trx_system_parameters_rec.base_currency_code
AND L.trx_LINE_ID = D.trx_LINE_ID
AND L.trx_header_id = h.trx_header_id
)
WHERE ACCOUNT_CLASS IN ('REV', 'TAX', 'FREIGHT','CHARGES')
AND d.acctd_amount IS NULL
AND D.PERCENT IS NOT NULL
AND EXISTS
(SELECT 'X'
FROM ar_trx_LINES_GT L
WHERE L.trx_LINE_ID = D.trx_LINE_ID
AND L.ACCOUNTING_RULE_ID IS NULL);
INSERT INTO ar_trx_errors_gt
( trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT l.trx_header_id,
L.trx_LINE_ID,
arp_standard.fnd_message('AR_INAPI_NVALID_SUM_DIST_AMT'),
d.account_class || ':'||SUM(d.amount)
FROM ar_trx_DIST_gt D,
ar_trx_lines_GT L
WHERE L.ACCOUNTING_RULE_ID IS NULL
AND L.LINE_TYPE = DECODE(D.ACCOUNT_CLASS,
'REV', 'LINE',
'TAX', 'TAX',
'FREIGHT', 'FREIGHT',
'CHARGES', 'CHARGES',
'INVALID_TYPE')
AND L.trx_LINE_ID = D.trx_LINE_ID
GROUP BY l.trx_header_id,
L.trx_LINE_ID,
L.LINE_TYPE,
L.extended_AMOUNT,
D.ACCOUNT_CLASS
HAVING L.extended_AMOUNT <> SUM(D.AMOUNT);
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
error_message,
invalid_value)
SELECT d.trx_header_id,
d.trx_line_ID,
arp_standard.fnd_message('AR_INAPI_100_PERCENT'),
sum(d.percent)
FROM ar_trx_dist_gt d, ar_trx_lines_gt L, ar_trx_header_gt h
WHERE d.trx_line_id = l.trx_line_id
AND l.trx_header_id = h.trx_header_id
GROUP BY d.trx_header_id,d.trx_line_ID, ACCOUNT_CLASS
HAVING SUM(d.PERCENT) <> 100;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
error_message,
invalid_value)
SELECT d.trx_header_id,
arp_standard.fnd_message('AR_INAPI_100_PERCENT'),
sum(d.percent)
FROM ar_trx_dist_gt d
WHERE d.account_class = 'REC'
GROUP BY d.trx_header_id
HAVING SUM(d.PERCENT) <> 100;
/* INSERT INTO RA_INTERFACE_ERRORS
(INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
MESSAGE_TEXT,
INVALID_VALUE)
SELECT INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
DECODE(PERCENT, NULL, :error_message, :message2),
NVL(TO_CHAR(PERCENT), :nil_message)
FROM RA_INTERFACE_DISTRIBUTIONS
WHERE REQUEST_ID = :request_id
AND ( PERCENT IS NULL
OR
(NVL(PERCENT, 0) - ROUND(NVL(PERCENT,0), :pct_precision)) <> 0
); */
UPDATE ar_trx_dist_gt dgt
SET dgt.account_set_flag =
(SELECT DECODE(hgt.invoicing_rule_id,null,'N','Y')
FROM ar_trx_header_gt hgt, ar_trx_lines_gt lgt
WHERE hgt.trx_header_id = lgt.trx_header_id
AND lgt.trx_line_id = dgt.trx_line_id
AND dgt.account_class <> 'REC'
UNION
SELECT DECODE(h.invoicing_rule_id,null,'N','Y')
FROM ar_trx_header_gt h
WHERE h.trx_header_id = dgt.trx_header_id
AND dgt.account_class = 'REC');
/* UPDATE ar_trx_dist_gt D
set (amount,acctd_amount) =
(Select DECODE(D.ACCOUNT_CLASS,
'REC', NULL,
'TAX', DECODE(NVL(PREV_TRX.INVOICING_RULE_ID,
NVL(L_PARENT.INVOICING_RULE_ID,
L.INVOICING_RULE_ID)), NULL,
DECODE(D.AMOUNT, NULL,
DECODE(L.TAX_PRECEDENCE, NULL,
DECODE(L_PARENT.QUANTITY *
L_PARENT.UNIT_SELLING_PRICE, NULL,
DECODE(l_min_acc_unit, NULL,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100),
l_precision),
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100)/
l_min_acc_unit) *
l_min_acc_unit),
DECODE(l_min_acc_unit, NULL,
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(NVL(l.tax_rate, 0)/100),
l_precision),
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(nvl(l.tax_rate, 0)/100)/
l_min_acc_unit) *
l_min_acc_unit)),0),
D.AMOUNT), NULL ),
DECODE(NVL(PREV_TRX.INVOICING_RULE_ID,
NVL(L_PARENT.INVOICING_RULE_ID,
L.INVOICING_RULE_ID)),
NULL, D.AMOUNT,
NULL)), -- amount
DECODE(D.ACCOUNT_CLASS,
'REC', null,
'TAX', DECODE(NVL(PREV_TRX.INVOICING_RULE_ID,
NVL(L_PARENT.INVOICING_RULE_ID,
L.INVOICING_RULE_ID)),
NULL, DECODE(D.ACCTD_AMOUNT, NULL,
DECODE(L.CURRENCY_CODE,
G.CURRENCY_CODE,
DECODE(D.AMOUNT, NULL, DECODE(L.TAX_PRECEDENCE, NULL,
DECODE(L_PARENT.QUANTITY * L_PARENT.UNIT_SELLING_PRICE,
NULL, DECODE(l_min_acc_unit, NULL,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100), l_precision)
,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100)/
l_min_acc_unit) * l_min_acc_unit),
DECODE(l_min_acc_unit, NULL,
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(NVL(l.tax_rate, 0)/100),l_precision)
,
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(nvl(l.tax_rate, 0)/100)/
l_min_acc_unit) * l_min_acc_unit)),0),
D.amount) ,
DECODE(l_min_acc_unit,
NULL,
ROUND(
DECODE(D.AMOUNT, NULL, DECODE(L.TAX_PRECEDENCE, NULL,
DECODE(L_PARENT.QUANTITY * L_PARENT.UNIT_SELLING_PRICE,
NULL, DECODE(l_min_acc_unit, NULL,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100), l_precision)
,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100)/
l_min_acc_unit) * l_min_acc_unit),
DECODE(l_min_acc_unit, NULL,
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(NVL(l.tax_rate, 0)/100),l_precision)
,
ROUND(l_parent.quantity *
l_parent.unit_selling_price * --
(nvl(l.tax_rate, 0)/100)/
l_min_acc_unit) * l_min_acc_unit)),0),
D.amount) * h.exchange_rate , l_precision ), --L.CONVERSION_RATE, l_precision,
ROUND(
DECODE(D.AMOUNT, NULL, DECODE(L.TAX_PRECEDENCE, NULL,
DECODE(L_PARENT.QUANTITY * L_PARENT.UNIT_SELLING_PRICE,
NULL, DECODE(l_min_acc_unit, NULL,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100), l_precision)
,
ROUND(NVL(l_parent.amount,0) *
(NVL(l.tax_rate, 0)/100)/
l_min_acc_unit) * l_min_acc_unit),
DECODE(l_min_acc_unit, NULL,
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(NVL(l.tax_rate, 0)/100),l_precision)
,
ROUND(l_parent.quantity *
l_parent.unit_selling_price *
(nvl(l.tax_rate, 0)/100)/
l_min_acc_unit) * l_min_acc_unit)),0),
D.amount) * h.exchange_rate / l_min_acc_unit ) * --L.CONVERSION_RATE / l_min_acc_unit *
l_min_acc_unit)),
D.ACCTD_AMOUNT), NULL),
DECODE(NVL(PREV_TRX.INVOICING_RULE_ID,
NVL(L_PARENT.INVOICING_RULE_ID,
L.INVOICING_RULE_ID)),
NULL, DECODE(D.ACCTD_AMOUNT, NULL,
DECODE(L.CURRENCY_CODE,
G.CURRENCY_CODE, D.AMOUNT,
DECODE(l_min_acc_unit,
NULL,
ROUND(D.AMOUNT *
h.exchange_rate , --L.CONVERSION_RATE,
l_precision),
ROUND(D.AMOUNT *
h.exchange_rate ,--L.CONVERSION_RATE /
l_min_acc_unit) *
l_min_acc_unit)),
D.ACCTD_AMOUNT),
NULL))
FROM ar_trx_DIST_gt D,
ar_trx_header_gt h
--RA_CUST_TRX_TYPES TYPE,
FND_CURRENCIES C,
GL_SETS_OF_BOOKS G,
--RA_CUSTOMER_TRX PREV_TRX,
ar_trx_LINES_GT L_PARENT,
ar_trx_LINES_GT L
WHERE --L.REQUEST_ID = :request_id
--L.PREVIOUS_CUSTOMER_TRX_ID = PREV_TRX.CUSTOMER_TRX_ID(+)
--AND L.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID
h.trx_header_id = l_trx_header_id
AND L.CURRENCY_CODE = C.CURRENCY_CODE
AND L.SET_OF_BOOKS_ID = G.SET_OF_BOOKS_ID
AND L.CUSTOMER_TRX_ID IS NOT NULL
AND L.LINK_TO_cust_trx_LINE_ID = L_PARENT.trx_LINE_ID (+)
AND L.trx_LINE_ID = D.trx_LINE_ID
AND D.ACCOUNT_CLASS = 'TAX'); */
UPDATE ar_trx_dist_gt
SET gl_date = NULL
WHERE trx_header_id IN (SELECT trx_header_id
FROM ar_trx_header_gt gt,
ra_cust_trx_types ctt
WHERE ctt.cust_trx_type_id = gt.cust_trx_type_id
AND ctt.post_to_gl = 'N');
SELECT trx_line_id, (100 - SUM(revenue_percent_split)) rounding_error,
MAX(SC.trx_SALESCREDIT_ID) max_trx_salescredit_id
FROM ar_trx_SALESCREDITS_gt SC,
SO_SALES_CREDIT_TYPES CR
WHERE SC.SALES_CREDIT_TYPE_ID = CR.SALES_CREDIT_TYPE_ID
AND CR.QUOTA_FLAG = 'Y'
GROUP BY trx_LINE_ID
HAVING SUM(revenue_percent_split) <> 100;
SELECT l.trx_line_id, MIN(L.trx_LINE_ID) min_trx_line_id,
MIN(DECODE(L.QUANTITY_invoiced * L.UNIT_SELLING_PRICE,
NULL, L.extended_AMOUNT,
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL,
ROUND(L.quantity_invoiced * L.UNIT_SELLING_PRICE,
C.PRECISION),
ROUND(L.quantity_invoiced *
L.UNIT_SELLING_PRICE/
C.MINIMUM_ACCOUNTABLE_UNIT)
* C.MINIMUM_ACCOUNTABLE_UNIT
)
)
) line_amount,
MAX(SC.trx_SALESCREDIT_ID) max_trx_salescredit_id,
SUM(revenue_amount_split) sales_credit_amount
FROM FND_CURRENCIES C,
ar_trx_SALESCREDITS_gt SC,
SO_SALES_CREDIT_TYPES CR,
ar_trx_LINES_GT L
WHERE L.LINE_TYPE = 'LINE'
AND L.CURRENCY_CODE = C.CURRENCY_CODE
and L.trx_LINE_ID = SC.trx_LINE_ID
and SC.SALES_CREDIT_TYPE_ID = CR.SALES_CREDIT_TYPE_ID
AND CR.QUOTA_FLAG = 'Y'
GROUP BY L.trx_LINE_ID;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
INVALID_VALUE)
SELECT trx_header_id,
trx_LINE_ID,
trx_SALESCREDIT_ID,
arp_standard.fnd_message(l_message_name),
sc.salesrep_id
FROM ar_trx_salescredits_gt SC
WHERE salesrep_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM RA_SALESREPS REP
WHERE REP.SALESREP_ID = SC.SALESREP_ID);
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
INVALID_VALUE)
SELECT trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
arp_standard.fnd_message(l_message_name),
sc.salesrep_num
FROM ar_trx_salescredits_gt SC
WHERE sc.salesrep_num IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM RA_SALESREPS REP
WHERE REP.SALESREP_NUMber = SC.SALESREP_NUM);
UPDATE ar_trx_salescredits_gt SC
SET SALESREP_ID = (SELECT SALESREP_ID
FROM RA_SALESREPS REP
WHERE REP.SALESREP_NUMBER = SC.SALESREP_NUM)
WHERE salesrep_id IS NULL;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
INVALID_VALUE)
SELECT trx_header_id,
trx_LINE_ID,
trx_SALESCREDIT_ID,
arp_standard.fnd_message(l_message_name),
sc.sales_credit_type_id
FROM ar_trx_salescredits_gt SC
WHERE sales_credit_type_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM SO_SALES_CREDIT_TYPES CR
WHERE CR.SALES_CREDIT_TYPE_ID = SC.SALES_CREDIT_TYPE_ID);
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
INVALID_VALUE)
SELECT trx_header_id,
trx_line_id,
trx_salescredit_id,
arp_standard.fnd_message(l_message_name),
sc.sales_credit_type_name
FROM ar_trx_salescredits_gt SC
WHERE sales_credit_type_name IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM SO_SALES_CREDIT_TYPES CR
WHERE CR.NAME = SC.SALES_CREDIT_TYPE_NAME);
UPDATE ar_trx_salescredits_gt SC
SET SALES_CREDIT_TYPE_ID =
(SELECT SALES_CREDIT_TYPE_ID
FROM SO_SALES_CREDIT_TYPES CR
WHERE CR.NAME = SC.SALES_CREDIT_TYPE_NAME)
WHERE SALES_CREDIT_TYPE_ID IS NULL;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE)
SELECT trx_header_id,
trx_LINE_ID,
trx_SALESCREDIT_ID,
arp_standard.fnd_message(l_message_name)
FROM ar_trx_salescredits_gt SC
WHERE revenue_amount_split IS NULL
AND revenue_percent_split IS NULL;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
INVALID_VALUE)
SELECT S.trx_header_id,
S.trx_LINE_ID,
S.trx_SALESCREDIT_ID,
arp_standard.fnd_message(l_message_name),
L.LINE_TYPE
FROM ar_trx_LINES_GT L,
ar_trx_SALESCREDITS_gt S
WHERE S.trx_LINE_ID = L.trx_LINE_ID
AND L.LINE_TYPE <> 'LINE';
UPDATE ar_trx_SALESCREDITS_gt S1
SET revenue_percent_split =
(
SELECT DECODE(DECODE(L.QUANTITY_invoiced * L.UNIT_SELLING_PRICE,
NULL, L.extended_AMOUNT,
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL, ROUND(QUANTITY_invoiced *
UNIT_SELLING_PRICE,
C.PRECISION),
ROUND(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE/
C.MINIMUM_ACCOUNTABLE_UNIT)
* C.MINIMUM_ACCOUNTABLE_UNIT
)
),
0, 0,
ROUND(S2.revenue_amount_split * 100 /
DECODE(L.QUANTITY_invoiced * L.UNIT_SELLING_PRICE,
NULL, L.extended_AMOUNT,
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL, ROUND(QUANTITY_invoiced *
UNIT_SELLING_PRICE,
C.PRECISION),
ROUND(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE/
C.MINIMUM_ACCOUNTABLE_UNIT)
* C.MINIMUM_ACCOUNTABLE_UNIT
)
),
pct_precision))
FROM ar_trx_LINES_GT L,
FND_CURRENCIES C,
ar_trx_SALESCREDITS_gt S2
WHERE L.trx_LINE_ID = S2.trx_LINE_ID
AND L.LINE_TYPE = 'LINE'
AND L.CURRENCY_CODE = C.CURRENCY_CODE
AND S2.ROWID = S1.ROWID
)
WHERE S1.revenue_amount_split IS NOT NULL
AND S1.revenue_percent_split IS NULL;
UPDATE ar_trx_salescredits_gt SC
SET revenue_amount_split =
(SELECT DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL,
ROUND(SC.revenue_percent_split *
DECODE(L.QUANTITY_invoiced * L.UNIT_SELLING_PRICE,
NULL, L.extended_AMOUNT,
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL,
ROUND(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE,
C.PRECISION),
ROUND(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE/
C.MINIMUM_ACCOUNTABLE_UNIT)
* C.MINIMUM_ACCOUNTABLE_UNIT
)
) / 100,
C.PRECISION),
ROUND((SC.revenue_percent_split *
DECODE(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE,
NULL, L.extended_amount,
DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL,
ROUND(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE,
C.PRECISION),
ROUND(L.QUANTITY_invoiced *
L.UNIT_SELLING_PRICE/
C.MINIMUM_ACCOUNTABLE_UNIT)
* C.MINIMUM_ACCOUNTABLE_UNIT
)
) / 100) /
C.MINIMUM_ACCOUNTABLE_UNIT)
* C.MINIMUM_ACCOUNTABLE_UNIT
)
FROM ar_trx_LINES_GT L,
FND_CURRENCIES C
WHERE L.trx_LINE_ID = SC.trx_LINE_ID
AND L.CURRENCY_CODE = C.CURRENCY_CODE)
WHERE SC.revenue_percent_split IS NOT NULL
AND SC.revenue_amount_split IS NULL;
UPDATE ar_trx_SALESCREDITS_gt
SET revenue_percent_split = revenue_percent_split + cRoundingRec.rounding_error
WHERE trx_SALESCREDIT_ID = cRoundingRec.max_trx_salescredit_id;
UPDATE ar_trx_SALESCREDITS_gt
SET revenue_amount_split = revenue_amount_split +
(cRoundingAmtRec.line_amount -
cRoundingAmtRec.sales_credit_amount)
WHERE trx_SALESCREDIT_ID = cRoundingAmtRec.max_trx_salescredit_id;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
invalid_value)
SELECT S.trx_header_id,
S.trx_LINE_ID,
S.trx_SALESCREDIT_ID,
arp_standard.fnd_message('AR_INAPI_INVALID_PRECISION'),
S.revenue_amount_split
FROM ar_trx_salescredits_gt S,
ar_trx_LINES_GT L,
FND_CURRENCIES C
WHERE S.trx_LINE_ID = L.trx_LINE_ID
AND L.CURRENCY_CODE = C.CURRENCY_CODE
AND s.revenue_amount_split IS NOT NULL
GROUP BY S.trx_header_id, S.trx_LINE_ID,
S.trx_SALESCREDIT_ID,
S.revenue_amount_split,
C.MINIMUM_ACCOUNTABLE_UNIT,
C.PRECISION
HAVING DECODE(C.MINIMUM_ACCOUNTABLE_UNIT,
NULL, ROUND(S.revenue_amount_split,
C.PRECISION),
ROUND(S.revenue_amount_split /
C.MINIMUM_ACCOUNTABLE_UNIT) *
C.MINIMUM_ACCOUNTABLE_UNIT) -
S.revenue_amount_split <> 0;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_LINE_ID,
trx_salescredit_id,
error_MESSAGE,
invalid_value)
SELECT SC.trx_header_id,
SC.trx_LINE_ID,
SC.trx_SALESCREDIT_ID,
arp_standard.fnd_message(l_message_name),
SC.revenue_percent_split
FROM ar_trx_salescredits_gt SC,
SO_SALES_CREDIT_TYPES SCT
WHERE SC.SALES_CREDIT_TYPE_ID = SCT.SALES_CREDIT_TYPE_ID
AND ( SC.revenue_percent_split IS NULL
OR
(NVL(SC.revenue_percent_split, 0) -
ROUND(NVL(SC.revenue_percent_split, 0),
pct_precision) <> 0)
);
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_line_id,
error_MESSAGE)
SELECT SC.trx_header_id,
SC.trx_LINE_ID,
arp_standard.fnd_message(l_message_name)
FROM
ar_trx_salescredits_gt SC,
SO_SALES_CREDIT_TYPES CR
WHERE SC.SALES_CREDIT_TYPE_ID = CR.SALES_CREDIT_TYPE_ID
GROUP BY
sc.trx_header_id, sc.trx_LINE_ID
HAVING
SUM(DECODE(CR.QUOTA_FLAG,
'Y', SC.revenue_percent_split,
'N', 0,
NULL, 0
) ) <> 100;
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_line_id,
error_message,
invalid_value)
SELECT s.trx_header_id,
s.trx_line_id,
arp_standard.fnd_message(l_message_name),
SUM(S.revenue_amount_split)
FROM ar_trx_salescredits_gt s,
so_sales_credit_types t
WHERE s.sales_credit_type_id = t.sales_credit_type_id
AND s.revenue_amount_split IS NOT NULL
AND t.quota_flag = 'Y'
GROUP BY S.trx_header_id, S.trx_line_id
HAVING SUM(S.revenue_amount_split) <>
(SELECT DECODE(l.quantity_invoiced * l.unit_selling_price,
NULL, l.extended_amount,
DECODE(c.minimum_accountable_unit, NULL,
ROUND(quantity_invoiced * unit_selling_price,
c.precision),
ROUND(l.quantity_invoiced * l.unit_selling_price
/ c.minimum_accountable_unit) *
c.minimum_accountable_unit))
FROM ar_trx_lines_gt l,
fnd_currencies c
WHERE l.trx_line_id = s.trx_line_id
AND l.line_type = 'LINE'
AND l.currency_code = c.currency_code);
INSERT INTO ar_trx_errors_gt
(trx_header_id,
trx_line_id,
error_message)
SELECT lgt.trx_header_id,
lgt.trx_line_id,
arp_standard.fnd_message(l_message_name)
FROM ar_trx_lines_gt lgt
WHERE lgt.line_type = 'LINE'
AND NOT EXISTS
(SELECT 'X'
FROM ar_trx_salescredits_gt scgt,
so_sales_credit_types type
WHERE scgt.trx_header_id = lgt.trx_header_id
AND scgt.trx_line_id = lgt.trx_line_id
AND scgt.sales_credit_type_id = type.sales_credit_type_id
AND type.quota_flag = 'Y' );