[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* 2. Changed the Truncate to Delete
* 3. Removed the Commit statement
*
* 7329586 - Removed schema logic and reverted DELETE statements
to static sql (no need for dynamic sql here)
*/
PROCEDURE clear_ebt_gt IS
l_owner VARCHAR2(30);
DELETE FROM ZX_TRX_HEADERS_GT;
DELETE FROM ZX_TRANSACTION_LINES_GT;
DELETE FROM ZX_IMPORT_TAX_LINES_GT;
DELETE FROM ZX_TRX_TAX_LINK_GT;
DELETE FROM ZX_DETAIL_TAX_LINES_GT;
p_rows_inserted OUT NOCOPY NUMBER) IS
l_rows NUMBER;
5) Added deletes for ra_customer_trx_lines and ra_cust_trx_line_gl_dist
tables.
6) Added logic to populate previous_customer_trx_line_id of CM
tax lines. Used Navigator to tweak for improved perf.
7) Added logic to preserve IL localization tax lines.
spoke with Ling (etax) and she said I need to join
to ZX lines to confirm that there is (or isnt) a
tax line over there. Just a value in tax_line_id is not
sufficient to guarantee that it is a migrated or native R12
tax line.
8) Number 7 didnt work. Only way to resolve is to separate
delete logic from insert logic. Execute delete before
call to calculate_tax and insert afterwards.
9) 5487466 - always call adjust_for_inclusive_tax because we
may need to alter the line values if the inclusive/excl state
of the tax changes or the tax lines go away.
End Dev Notes */
/* Bug 5152340 - Removed delete logic to its own procedure */
INSERT INTO RA_CUSTOMER_TRX_LINES
(
CUSTOMER_TRX_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
CUSTOMER_TRX_ID,
LINE_NUMBER,
SET_OF_BOOKS_ID,
LINE_TYPE, -- TAX
LINK_TO_CUST_TRX_LINE_ID, -- parent line
DEFAULT_USSGL_TRANSACTION_CODE,
REQUEST_ID,
EXTENDED_AMOUNT,
TAX_RATE,
AUTOTAX,
AMOUNT_INCLUDES_TAX_FLAG,
TAXABLE_AMOUNT,
VAT_TAX_ID,
TAX_LINE_ID, -- ID in ZX_ table
PREVIOUS_CUSTOMER_TRX_LINE_ID,
PREVIOUS_CUSTOMER_TRX_ID, -- 5125882
ORG_ID
)
SELECT
ra_customer_trx_lines_s.nextval,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
arp_standard.profile.user_id,
arp_standard.profile.program_id,
arp_standard.application_id,
zxt.trx_id,
zxt.tax_line_number,
arp_standard.sysparm.set_of_books_id,
'TAX',
zxt.trx_line_id,
plin.default_ussgl_transaction_code,
NULL, -- request_id
zxt.tax_amt,
zxt.tax_rate,
DECODE(NVL(zxt.manually_entered_flag, 'N'), 'Y', NULL, 'Y'),
zxt.tax_amt_included_flag,
zxt.taxable_amt,
zxt.tax_rate_id,
zxt.tax_line_id,
inv_lin.customer_trx_line_id, -- invoice tax line id
inv_lin.customer_trx_id, -- inv trx_id, 5125882
plin.org_id
FROM ZX_LINES zxt,
RA_CUSTOMER_TRX_LINES plin,
ZX_LINES inv_zxt,
RA_CUSTOMER_TRX_LINES inv_lin
WHERE
zxt.application_id = 222
AND zxt.entity_code = 'TRANSACTIONS'
AND zxt.event_class_code in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
AND zxt.trx_id = p_customer_trx_id
AND zxt.trx_level_type = 'LINE'
AND zxt.trx_line_id = plin.customer_trx_line_id
AND zxt.adjusted_doc_tax_line_id = inv_zxt.tax_line_id (+)
AND inv_zxt.trx_line_id = inv_lin.link_to_cust_trx_line_id (+)
AND inv_zxt.tax_line_id = inv_lin.tax_line_id (+);
/* Return number of rows inserted to limit calls
to autoaccounting and other followon code */
p_rows_inserted := l_rows;
we could delete from AR based on existing tax lines just before
calling calculate_tax (which recreates them in ZX). This means
that we can use the presence of lines in ZX_LINES as a basis
for our delete. So the intended flow is now like this:
1) call delete_tax_lines_from_ar
2) call eTax calculate_tax
3) call build_ar_tax_lines
NOTE: This is really only relevant for forms code as the invoice
API, autoinvoice, and invoice copy only create transactions so there
should not be a case where we recalculate tax (again) or manipulate
existing transactions with localization tax.
*/
PROCEDURE delete_tax_lines_from_ar(
p_customer_trx_id IN NUMBER) IS
l_rows NUMBER;
debug('arp_etax_util.delete_tax_lines_from_ar()+');
SELECT 'Transaction is not posted'
INTO l_posted
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND account_class = 'REC'
AND latest_rec_flag = 'Y'
AND posting_control_id = -3;
debug('EXCEPTION: Transaction is posted, cannot delete');
fnd_message.set_name('AR','AR_CANT_UPDATE_IF_POSTED');
DELETE FROM RA_CUST_TRX_LINE_GL_DIST gld
WHERE customer_trx_line_id in (
SELECT tl.customer_trx_line_id
FROM RA_CUSTOMER_TRX_LINES tl,
ZX_LINES zx
WHERE tl.customer_trx_id = p_customer_trx_id
AND tl.line_type = 'TAX'
AND tl.tax_line_id IS NOT NULL
AND tl.tax_line_id = zx.tax_line_id)
AND customer_trx_id = p_customer_trx_id
AND account_class = 'TAX'
AND posting_control_id = -3;
debug(' Deleted tax dists = ' || l_rows);
DELETE FROM RA_CUSTOMER_TRX_LINES
WHERE customer_trx_id = p_customer_trx_id
AND line_type = 'TAX'
AND tax_line_id IN
(SELECT tax_line_id
FROM ZX_LINES);
debug(' Deleted tax lines = ' || l_rows);
debug('arp_etax_util.delete_tax_lines_from_ar()-');
END delete_tax_lines_from_ar;
/* Public Procedure - to update doc sequence data on batch
transactions after insert
5468039 - added support for trx_line_gl_date
specifically for when gl_date changes */
PROCEDURE synchronize_for_doc_seq(p_trx_id IN NUMBER,
p_return_status OUT NOCOPY NUMBER,
p_request_id IN NUMBER DEFAULT NULL,
p_sync_line_data IN VARCHAR2 DEFAULT 'N')
IS
l_return_status VARCHAR2(50);
SELECT
DECODE(TT.type, 'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO') event_class,
TT.type || '_UPDATE' event_type,
'Y' tax_reporting_flag,
T.customer_trx_id customer_trx_id,
T.trx_number trx_number,
SUBSTRB(T.comments,1,240) description,
T.doc_sequence_id doc_sequence_id,
TT.name trx_type_name,
-- bug 6806843
SEQ.name doc_seq_name,
T.doc_sequence_value doc_sequence_value,
T.batch_source_id batch_source_id,
TB.name batch_source_name,
T.cust_trx_type_id cust_trx_type_id,
T.trx_date trx_date,
T.printing_original_date printing_original_date,
T.term_due_date term_due_date,
T.bill_to_site_use_id bill_to_site_use_id
FROM RA_CUSTOMER_TRX T,
RA_CUST_TRX_TYPES TT,
RA_BATCH_SOURCES TB,
FND_DOCUMENT_SEQUENCES SEQ
WHERE T.request_id = p_request_id
AND T.cust_trx_type_id = TT.cust_trx_type_id
AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
AND T.batch_source_id = TB.batch_source_id
AND (T.doc_sequence_id IS NOT NULL OR
T.doc_sequence_value IS NOT NULL OR
NVL(T.old_trx_number, T.trx_number) <> T.trx_number OR
p_sync_line_data = 'Y');
SELECT
DECODE(TT.type, 'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO') event_class,
TT.type || '_UPDATE' event_type,
T.customer_trx_id customer_trx_id,
T.trx_number trx_number,
SUBSTRB(T.comments,1,240) description,
T.doc_sequence_id doc_sequence_id,
-- bug 6806843
--TT.name trx_type_name,
SEQ.name doc_seq_name,
T.doc_sequence_value doc_sequence_value,
T.batch_source_id batch_source_id,
TB.name batch_source_name,
TT.description trx_type_description,
T.printing_original_date printing_original_date,
T.term_due_date term_due_date,
TT.type type
FROM RA_CUSTOMER_TRX T,
RA_CUST_TRX_TYPES TT,
RA_BATCH_SOURCES TB,
FND_DOCUMENT_SEQUENCES SEQ
WHERE T.customer_trx_id = trx_id
AND T.cust_trx_type_id = TT.cust_trx_type_id
AND T.doc_sequence_id = SEQ.doc_sequence_id (+)
AND T.batch_source_id = TB.batch_source_id
AND (T.doc_sequence_id IS NOT NULL OR
T.doc_sequence_value IS NOT NULL OR
NVL(T.old_trx_number, T.trx_number) <> T.trx_number OR
sync_line_data = 'Y');
SELECT 222 application_id,
'TRANSACTIONS' entity_code,
DECODE(TT.type, 'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO') event_class_code,
T.customer_trx_id trx_id,
'LINE' trx_level_type,
TL.customer_trx_line_id trx_line_id,
NULL trx_waybill_number,
TL.description trx_line_description,
NULL product_description,
REC.gl_date trx_line_gl_date,
NULL merchant_party_name,
NULL merchant_party_document_number,
NULL merchant_party_reference,
NULL merchant_party_taxpayer_id,
NULL merchant_party_tax_reg_number,
NULL asset_number
FROM
RA_CUSTOMER_TRX T,
RA_CUSTOMER_TRX_LINES TL,
RA_CUST_TRX_TYPES TT,
RA_CUST_TRX_LINE_GL_DIST REC
WHERE T.customer_trx_id = trx_id
AND T.cust_trx_type_id = TT.cust_trx_type_id
AND T.org_id = TT.org_id
AND T.customer_trx_id = TL.customer_trx_id
AND TL.line_type = 'LINE'
AND T.customer_trx_id = REC.customer_trx_id (+)
AND REC.account_class (+) = 'REC'
AND REC.latest_rec_flag (+) = 'Y';
has to get updated */
IF p_sync_line_data = 'Y'
THEN
OPEN c_trx_lines(p_trx_id);
PROCEDURE zx_global_document_update(
p_trx_rec IN OUT NOCOPY ZX_API_PUB.transaction_rec_type)
IS
l_return_status VARCHAR2(50);
debug('zx_global_document_update called for ' ||
p_trx_rec.trx_id);
ZX_API_PUB.global_document_update(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_message_data,
p_transaction_rec => p_trx_rec
);
END zx_global_document_update;
PROCEDURE global_document_update(p_customer_trx_id IN NUMBER,
p_request_id IN NUMBER,
p_action IN VARCHAR2)
IS
l_return_status VARCHAR2(50);
SELECT t.customer_trx_id customer_trx_id,
DECODE(tt.type,
'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO') event_class,
tt.type event_prefix
FROM ra_customer_trx t,
ra_cust_trx_types tt
WHERE t.request_id = p_request_id
AND t.cust_trx_type_id = tt.cust_trx_type_id;
debug('arp_etax_util.global_document_update()+');
SELECT t.customer_trx_id,
DECODE(tt.type,
'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO'),
tt.type || '_' || p_action
INTO l_trx_rec.trx_id,
l_trx_rec.event_class_code,
l_trx_rec.event_type_code
FROM ra_customer_trx t,
ra_cust_trx_types tt
WHERE t.customer_trx_id = p_customer_trx_id
AND t.cust_trx_type_id = tt.cust_trx_type_id;
zx_global_document_update(l_trx_rec);
zx_global_document_update(l_trx_rec);
debug('arp_etax_util.global_document_update()-');
END global_document_update;
l_rows_needing_update NUMBER;
l_rows_updated NUMBER;
select /*+ index (tl RA_CUSTOMER_TRX_LINES_N4) */
tl.customer_trx_line_id,
nvl(tl.tax_classification_code, zx.output_tax_classification_code)
from ra_customer_trx t,
ra_customer_trx_lines tl,
zx_lines_det_factors zx
where t.request_id = p_request_id
and t.customer_trx_id = tl.customer_trx_id
and tl.line_type = 'LINE'
and tl.request_id = p_request_id
and NVL(t.previous_customer_trx_id, -99) =
DECODE(p_phase, 'INV', -99, t.previous_customer_trx_id)
and zx.application_id = 222
and zx.entity_code = 'TRANSACTIONS'
and zx.event_class_code in ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')
and zx.trx_id = tl.customer_trx_id
and zx.trx_level_type = 'LINE'
and zx.trx_line_id = tl.customer_trx_line_id;
l_rows_needing_update := line_to_tax_class%ROWCOUNT;
IF l_rows_needing_update > 0
THEN
FORALL i IN t_line_id.FIRST..t_line_id.LAST
UPDATE ra_customer_trx_lines
SET tax_classification_code = t_class_code(i)
WHERE customer_trx_line_id = t_line_id(i);
l_rows_updated := SQL%ROWCOUNT;
debug( ' rows found : ' || l_rows_needing_update);
debug( ' rows updated : ' || l_rows_updated);
Select type.type
into l_trx_class
from ra_customer_trx trx,
ra_cust_trx_types type
where trx.customer_trx_id = p_customer_trx_id
and trx.cust_trx_type_id = type.cust_Trx_type_id;
SELECT sob.set_of_books_id,
sob.chart_of_accounts_id,
sob.currency_code,
c.precision,
c.minimum_accountable_unit,
sysp.code_combination_id_gain,
sysp.code_combination_id_loss,
sysp.code_combination_id_round
INTO p_ae_sys_rec.set_of_books_id,
p_ae_sys_rec.coa_id,
p_ae_sys_rec.base_currency,
p_ae_sys_rec.base_precision,
p_ae_sys_rec.base_min_acc_unit,
p_ae_sys_rec.gain_cc_id,
p_ae_sys_rec.loss_cc_id,
p_ae_sys_rec.round_cc_id
FROM ar_system_parameters sysp,
gl_sets_of_books sob,
fnd_currencies c
WHERE sob.set_of_books_id = sysp.set_of_books_id
AND sob.currency_code = c.currency_code;
l_rows_inserted NUMBER;
SELECT gt_id,
source_id,
source_table,
customer_trx_id,
customer_trx_line_id,
line_type,
line_amount,
ed_line_amount,
uned_line_amount,
tax_amount,
ed_tax_amount,
uned_tax_amount
FROM AR_LINE_DIST_INTERFACE_GT;
/* Insert tax lines into _GT table for processing */
/* Dev Note: Discounts are calculated as negative amounts
in etax because they are decreasing the tax liability.
However, for proration purposes, the allocation code
expects them to be positive values. As such, we
have to reverse the sign of the amount(s) from ZX
specifically for discounts. Adjustments are already
in the correct sign (same sign for AR and ZX) */
INSERT INTO AR_LINE_DIST_INTERFACE_GT
( GT_ID,
SOURCE_ID,
SOURCE_TABLE,
CUSTOMER_TRX_ID,
CUSTOMER_TRX_LINE_ID,
LINE_TYPE,
TAX_AMOUNT,
ED_TAX_AMOUNT,
UNED_TAX_AMOUNT)
(SELECT
l_gt_id,
zx.trx_id,
DECODE(l_mode, 'ADJUST' ,'ADJ',
'UNEDISC','RA',
'EDISC' ,'RA'),
tl.customer_trx_id,
tl.customer_trx_line_id,
'TAX',
DECODE(l_mode, 'ADJUST', zx.tax_amt, NULL),
DECODE(l_mode, 'EDISC', zx.tax_amt, NULL),
DECODE(l_mode, 'UNEDISC',zx.tax_amt, NULL)
FROM
zx_lines zx,
ra_customer_trx_lines tl
WHERE
zx.application_id = p_transaction_rec.application_id
AND zx.entity_code = p_transaction_rec.entity_code
AND zx.event_class_code = p_transaction_rec.event_class_code
AND zx.trx_id = p_transaction_rec.trx_id
AND zx.trx_level_type =
DECODE(l_mode, 'EDISC','LINE_EARNED',
'UNEDISC','LINE_UNEARNED',
'LINE')
-- AND zx.trx_line_id = NVL(p_ra_app_id, zx.trx_line_id)
AND tl.link_to_cust_trx_line_id = zx.adjusted_doc_line_id
AND tl.line_type = 'TAX'
AND tl.tax_line_id = zx.adjusted_doc_tax_line_id);
l_rows_inserted := SQL%ROWCOUNT;
arp_util.debug('tax lines inserted = ' || l_rows_inserted);
/* Only insert LINEs if it is not a tax-only adj */
IF p_mode <> 'TAX'
THEN
/* Insert line amounts */
INSERT INTO AR_LINE_DIST_INTERFACE_GT
( GT_ID,
SOURCE_ID,
SOURCE_TABLE,
CUSTOMER_TRX_ID,
CUSTOMER_TRX_LINE_ID,
LINE_TYPE,
LINE_AMOUNT,
ED_LINE_AMOUNT,
UNED_LINE_AMOUNT)
(SELECT
l_gt_id,
zx.trx_id,
DECODE(l_mode, 'ADJUST' ,'ADJ',
'UNEDISC','RA',
'EDISC' ,'RA'),
il.customer_trx_id,
il.customer_trx_line_id,
'LINE',
DECODE(l_mode, 'ADJUST', max(zx.line_amt) -
sum(zx.tax_amt), NULL),
DECODE(l_mode, 'EDISC',max(zx.line_amt) -
sum(zx.tax_amt), NULL),
DECODE(l_mode, 'UNEDISC',max(zx.line_amt) -
sum(zx.tax_amt), NULL)
FROM
zx_lines zx,
ra_customer_trx_lines il
WHERE
zx.application_id = p_transaction_rec.application_id
AND zx.entity_code = p_transaction_rec.entity_code
AND zx.event_class_code = p_transaction_rec.event_class_code
AND zx.trx_id = p_transaction_rec.trx_id
AND il.customer_trx_id = zx.adjusted_doc_trx_id
AND il.customer_trx_line_id = zx.adjusted_doc_line_id
AND il.line_type = 'LINE'
GROUP BY zx.trx_id, zx.adjusted_doc_line_id,
il.customer_trx_id, il.customer_trx_line_id);
l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
arp_util.debug('Total line and tax rows inserted = ' ||
l_rows_inserted);
/* Check total rows inserted.. if none, set p_gt_id to zero
and exit */
IF l_rows_inserted = 0
THEN
IF l_gt_passed
THEN
/* gt_id was passed into this program from a prior
call. Do not null it here as there are other
accounting entries using it */
NULL;
SELECT *
INTO l_adj_rec
FROM ar_adjustments
WHERE adjustment_id = p_transaction_rec.trx_id;
SELECT *
INTO l_trx_rec
FROM ra_customer_trx
WHERE customer_trx_id = l_adj_rec.customer_trx_id;
SELECT *
INTO l_app_rec
FROM ar_receivable_applications
WHERE receivable_application_id = p_rec_app_id;
SELECT *
INTO l_trx_rec
FROM ra_customer_trx
WHERE customer_trx_id = l_app_rec.applied_customer_trx_id;
originals were inserted before the etax call. Supports the following
values:
Y - update both
A - update adjustments only
N - update neither
*/
PROCEDURE update_adj_and_ps(
p_upd_adj_and_ps IN VARCHAR2,
p_adj_id IN NUMBER,
p_prorated_line IN NUMBER,
p_prorated_tax IN NUMBER)
IS
l_adj_rec ar_adjustments%ROWTYPE;
l_ps_update_needed BOOLEAN := TRUE;
arp_util.debug('arp_etax_util.update_adj_and_ps()+');
SELECT nvl(line_adjusted,0),
nvl(tax_adjusted,0)
INTO l_orig_line_adj,
l_orig_tax_adj
FROM ar_adjustments
WHERE adjustment_id = p_adj_id;
different, update adjustment here */
IF l_orig_line_adj <> p_prorated_line OR
l_orig_tax_adj <> p_prorated_tax
THEN
/* update adjustment manually */
UPDATE ar_adjustments
SET line_adjusted = p_prorated_line,
tax_adjusted = p_prorated_tax
WHERE adjustment_id = p_adj_id;
PS update */
l_ps_update_needed := FALSE;
arp_util.debug(' orig and prorate amts same, skip update(s)');
/* Do the PS update if specified and if
it is still deemed necessary */
IF p_upd_adj_and_ps in ('Y','P') AND
l_ps_update_needed = TRUE
THEN
/* select adj back from db for PS update
NOTE: This includes update line and tax
from above */
SELECT *
INTO l_adj_rec
FROM ar_adjustments
WHERE adjustment_id = p_adj_id;
arp_ps_util.update_adj_related_columns(
l_adj_rec.payment_schedule_id,
l_adj_rec.type,
l_adj_rec.amount,
null,
l_adj_rec.line_adjusted,
l_adj_rec.tax_adjusted,
l_adj_rec.freight_adjusted,
l_adj_rec.receivables_charges_adjusted,
l_adj_rec.apply_date,
l_adj_rec.gl_date,
l_adj_rec.acctd_amount,
l_ps_rec );
arp_util.debug('arp_etax_util.update_adj_and_ps()-');
END update_adj_and_ps;
Y=update both
A=update adj only
NULL/N=do nothing
p_gt_id ID assigned for proration logic
p_prorated_line amount allocated to lines
p_prorated_tax amount allocated to tax
*/
PROCEDURE prorate_recoverable(
p_adj_id IN NUMBER,
p_target_id IN NUMBER,
p_target_line_id IN NUMBER,
p_amount IN NUMBER,
p_apply_date IN DATE,
p_mode IN VARCHAR2,
p_upd_adj_and_ps IN VARCHAR2,
p_gt_id IN OUT NOCOPY NUMBER,
p_prorated_line IN OUT NOCOPY NUMBER,
p_prorated_tax IN OUT NOCOPY NUMBER,
p_quote IN VARCHAR2 DEFAULT 'N')
IS
l_junk_ra_app_id NUMBER := -1;
Y=update both
A=update adj only
NULL/N=do nothing
p_gt_id ID assigned for proration logic
p_prorated_line amount allocated to lines
p_prorated_tax amount allocated to tax
p_ra_app_id the application_id to be used for receipt
APP and UNAPP activities.
If passed in as -1, ignore.
If passed in as NULL, assign a value.
If passed as value other than -1, use as is
*/
PROCEDURE prorate_recoverable(
p_adj_id IN NUMBER,
p_target_id IN NUMBER,
p_target_line_id IN NUMBER,
p_amount IN NUMBER,
p_apply_date IN DATE,
p_mode IN VARCHAR2,
p_upd_adj_and_ps IN VARCHAR2,
p_gt_id IN OUT NOCOPY NUMBER,
p_prorated_line IN OUT NOCOPY NUMBER,
p_prorated_tax IN OUT NOCOPY NUMBER,
p_quote IN VARCHAR2 DEFAULT 'N',
p_ra_app_id IN OUT NOCOPY NUMBER)
IS
l_recov_flag VARCHAR2(1);
l_row NUMBER := 0; -- row counter for inserting
SELECT
TRX.org_id internal_organization_id,
TRX.customer_trx_id inv_trx_id,
TRX.trx_number trx_number,
TRX.invoice_currency_code trx_currency_code,
CUR.precision trx_precision,
CUR.minimum_accountable_unit trx_mau,
TRX.exchange_date,
TRX.exchange_rate,
TRX.exchange_rate_type,
TRX.legal_entity_id legal_entity_id,
TRX.trx_date inv_trx_date,
DECODE(TT.type,'CM','CREDIT_MEMO',
'DM','DEBIT_MEMO',
'INVOICE') trx_event_class,
TRX.ship_to_customer_id trx_ship_to_customer_id,
TRX.ship_to_site_use_id trx_ship_to_site_use_id,
AR.set_of_books_id ledger_id,
BILL_CUST.party_id bill_to_party_id,
BILL_AS.party_site_id bill_to_party_site_id,
BILL_PS.location_id bill_to_location_id,
LINES.customer_trx_line_id inv_trx_line_id,
LINES.line_number inv_trx_line_number,
NVL(LINES.historical_flag,'N') historical_flag,
LINES.extended_amount line_amt,
LINES.tax_exempt_flag exemption_control_flag,
LINES.tax_exempt_number exempt_certificate_number,
LINES.tax_exempt_reason_code exempt_reason,
DECODE(LINES.memo_line_id, NULL,
NVL(LINES.warehouse_id,to_number(pg_so_org_id))) warehouse_id,
LINES.line_recoverable,
LINES.tax_recoverable,
LINES.ship_to_customer_id line_ship_to_customer_id,
LINES.ship_to_site_use_id line_ship_to_site_use_id,
LINES.inventory_item_id inv_product_id,
HR.location_id ship_from_location_id,
HRL.location_id poa_location_id,
SR_PER.organization_id poo_party_id,
SR_HRL.location_id poo_location_id
FROM
RA_CUSTOMER_TRX TRX,
RA_CUST_TRX_TYPES TT,
RA_CUSTOMER_TRX_LINES LINES,
AR_SYSTEM_PARAMETERS AR,
FND_CURRENCIES CUR,
HZ_CUST_ACCOUNTS BILL_CUST,
HZ_PARTIES BILL_PARTY,
HZ_CUST_ACCT_SITES BILL_AS,
HZ_CUST_SITE_USES BILL_SU,
HZ_PARTY_SITES BILL_PS,
HR_ALL_ORGANIZATION_UNITS HR,
HR_ORGANIZATION_UNITS HRL,
JTF_RS_SALESREPS SR,
PER_ALL_ASSIGNMENTS_F SR_PER,
HR_ORGANIZATION_UNITS SR_HRL
WHERE
TRX.customer_trx_id = p_trx_id and
LINES.customer_trx_id = TRX.customer_trx_id and
LINES.customer_trx_line_id =
NVL(p_trx_line_id,LINES.customer_trx_line_id) and
LINES.line_type IN ('LINE' ,'CB') and
TRX.org_id = AR.org_id and
TRX.cust_trx_type_id = TT.cust_trx_type_id and
TRX.invoice_currency_code = CUR.currency_code and
TRX.bill_to_customer_id = BILL_CUST.cust_account_id and
BILL_CUST.party_id = BILL_PARTY.party_id and
BILL_CUST.cust_account_id = BILL_AS.cust_account_id and
BILL_AS.cust_acct_site_id = BILL_SU.cust_acct_site_id and
BILL_SU.site_use_id = TRX.bill_to_site_use_id and
BILL_AS.party_site_id = BILL_PS.party_site_id and
LINES.warehouse_id = HR.organization_id (+) and
TRX.org_id = HRL.organization_id and
TRX.primary_salesrep_id = SR.salesrep_id (+) and
TRX.org_id = SR.org_id (+) and
SR.person_id = SR_PER.person_id (+) and
TRX.trx_date BETWEEN nvl(SR_PER.effective_start_date, TRX.trx_date)
AND nvl(SR_PER.effective_end_date, TRX.trx_date) and
NVL(SR_PER.primary_flag, 'Y') = 'Y' and
SR_PER.assignment_type (+) = 'E' and
SR_PER.organization_id = SR_HRL.organization_id (+)
order by LINES.extended_amount;
/* Selects detail tax lines back from ZX by adj_id/app_id */
/* 4937059 - grouped by line_id */
CURSOR tax_lines(p_entity VARCHAR2, p_event_class VARCHAR2,
p_trx_id NUMBER, p_trx_line_id NUMBER,
p_mode VARCHAR2) IS
SELECT adjusted_doc_line_id, SUM(tax_amt) tax_amt, MAX(line_amt) line_amt
FROM zx_lines
WHERE application_id = 222
AND entity_code = p_entity
AND event_class_code = p_event_class
AND trx_id = p_trx_id
AND trx_line_id = NVL(p_trx_line_id, trx_line_id)
AND trx_level_type = DECODE(p_mode,
'APP_ED', 'LINE_EARNED',
'UNAPP_ED', 'LINE_EARNED',
'APP_UED', 'LINE_UNEARNED',
'UNAPP_UED','LINE_UNEARNED',
'LINE')
GROUP BY adjusted_doc_line_id;
SELECT adjusted_doc_line_id, SUM(tax_amt) tax_amt, MAX(line_amt) line_amt
FROM zx_detail_tax_lines_gt
WHERE application_id = 222
AND entity_code = p_entity
AND event_class_code = p_event_class
AND trx_id = p_trx_id
AND trx_line_id = NVL(p_trx_line_id, trx_line_id)
AND trx_level_type = DECODE(p_mode,
'APP_ED', 'LINE_EARNED',
'UNAPP_ED', 'LINE_EARNED',
'APP_UED', 'LINE_UNEARNED',
'UNAPP_UED','LINE_UNEARNED',
'LINE')
GROUP BY adjusted_doc_line_id;
SELECT a.adjustment_number, r.tax_recoverable_flag,a.tax_adjusted
INTO l_adj_number,
l_recov_flag,
l_tax_adjusted /* Bug 8512053 */
FROM ar_adjustments a,
ar_receivables_trx r
WHERE a.adjustment_id = p_adj_id
AND a.receivables_trx_id = r.receivables_trx_id
AND a.org_id = r.org_id;
SELECT ar_receivable_applications_s.nextval
INTO p_ra_app_id
FROM DUAL;
select cr.receipt_number,
NVL(decode(p_mode, 'APP_ED', earn.tax_recoverable_flag,
'UNAPP_ED', earn.tax_recoverable_flag,
'APP_UED', unearn.tax_recoverable_flag,
'UNAPP_UED', unearn.tax_recoverable_flag),
'N')
into l_adj_number,
l_recov_flag
from ar_cash_receipts cr,
ar_receipt_method_accounts arm,
ar_receivables_trx earn,
ar_receivables_trx unearn
where cr.cash_receipt_id = p_adj_id
and cr.receipt_method_id = arm.receipt_method_id
and arm.edisc_receivables_trx_id = earn.receivables_trx_id (+)
and arm.unedisc_receivables_trx_id = unearn.receivables_trx_id (+)
and cr.remit_bank_acct_use_id = arm.remit_bank_acct_use_id; --bug6401710
SELECT sum(least(tax_line.extended_amount, nvl(line.tax_recoverable, tax_line.extended_amount))),
sum(nvl(line.tax_recoverable,0))
INTO l_sum,
l_total_tax_recov
FROM ra_customer_trx_lines line,
ra_customer_trx_lines tax_line
WHERE line.customer_trx_id = p_target_id
AND line.line_type = 'LINE'
AND tax_line.link_to_cust_trx_line_id = line.customer_trx_line_id
AND tax_line.line_type = 'TAX';
SELECT
sum(least(tl.extended_amount,nvl(tl.line_recoverable, tl.extended_amount)) *
(1 + nvl(tl.tax_recoverable, 0)/
DECODE(tl.line_recoverable,0,1,NULL,1,
tl.line_recoverable))),
sum(nvl(tl.tax_recoverable,0))
INTO l_sum,
l_total_tax_recov
FROM ra_customer_trx_lines tl
WHERE tl.customer_trx_id = p_target_id
AND tl.customer_trx_line_id =
NVL(p_target_line_id, tl.customer_trx_line_id)
AND tl.line_type = 'LINE';
SELECT sum(amount_due_remaining)
INTO l_amt_due_remaining
FROM ar_payment_schedules ps
where ps.customer_trx_id = p_target_id
group by ps.customer_trx_id;
Insert them into the ZX structure for processing */
FOR c_tl in trx_lines(p_target_id, p_target_line_id) LOOP
IF (PG_DEBUG in ('Y','C')) THEN
debug('processing trx_line_id ' || c_tl.inv_trx_line_id);
SELECT LEAST(nvl(c_tl.tax_recoverable, sum(extended_amount)), sum(extended_amount))
INTO l_tax_amount
FROM RA_CUSTOMER_TRX_LINES
WHERE customer_trx_id = c_tl.inv_trx_id
AND link_to_cust_trx_line_id = c_tl.inv_trx_line_id
AND line_type = 'TAX';
SELECT
CUST_ACCT.party_id,
ACCT_SITE.party_site_id,
PARTY_SITE.location_id
INTO
-- ship_to_party_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(l_row),
-- ship_to_party_site_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(l_row),
-- ship_to_location_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(l_row)
FROM
hz_cust_accounts CUST_ACCT,
hz_parties PARTY,
hz_cust_acct_sites ACCT_SITE,
hz_cust_site_uses SITE_USES,
hz_party_sites PARTY_SITE
WHERE
CUST_ACCT.cust_account_id = l_cust_id AND
CUST_ACCT.party_id = PARTY.party_id AND
CUST_ACCT.cust_account_id = ACCT_SITE.cust_account_id AND
ACCT_SITE.cust_acct_site_id = SITE_USES.cust_acct_site_id AND
SITE_USES.site_use_id = l_site_use_id and
PARTY_SITE.party_site_id = ACCT_SITE.party_site_id;
/* insert det factors */
ZX_API_PUB.insert_line_det_factors (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_duplicate_line_rec => l_transaction_line_rec);
update the line and tax_recoverable columns */
IF (l_return_status_service = 'S')
THEN
/* initialize prorated tax and line amounts to zero */
p_prorated_line := 0;
/* Set out parameter totals and update LINES recoverable columns */
FOR c_tax_lines IN tax_lines(l_transaction_rec.entity_code,
l_transaction_rec.event_class_code,
l_adj_ra_id,
p_target_line_id,
p_mode) LOOP
l_lines_processed := TRUE;
UPDATE RA_CUSTOMER_TRX_LINES
SET line_recoverable = line_recoverable + l_prorated_line,
tax_recoverable = tax_recoverable + l_prorated_tax,
last_updated_by = arp_standard.profile.user_id,
last_update_date = sysdate
WHERE customer_trx_line_id = c_tax_lines.adjusted_doc_line_id;
/* Update PS and ADJ records if required */
IF NVL(p_upd_adj_and_ps, 'N') <> 'N'
THEN
update_adj_and_ps(p_upd_adj_and_ps,
p_adj_id,
p_prorated_line,
p_prorated_tax);
SELECT line.CUSTOMER_TRX_LINE_ID customer_trx_line_id,
/* sum(decode(tax.amount_includes_tax_flag,
'Y', decode(line.extended_amount, 0, 0,
tax.extended_amount), 0)) inclusive_amount,*/
sum(decode(tax.amount_includes_tax_flag,
'Y', decode(nvl(line.gross_extended_amount, line.extended_amount), 0, 0,
tax.extended_amount), 0)) inclusive_amount,--qiong fix bug :11671073
header.invoice_currency_code currency_code,
header.exchange_rate exchange_rate,
currency.precision precision,
currency.minimum_accountable_unit mau
FROM RA_CUSTOMER_TRX header,
FND_CURRENCIES currency,
RA_CUSTOMER_TRX_LINES line,
RA_CUSTOMER_TRX_LINES tax
WHERE header.CUSTOMER_TRX_ID = p_trx_id
AND line.CUSTOMER_TRX_ID = header.CUSTOMER_TRX_ID
AND line.LINE_TYPE = 'LINE'
AND tax.LINK_TO_CUST_TRX_LINE_ID = line.CUSTOMER_TRX_LINE_ID
AND tax.LINE_TYPE = 'TAX'
AND (tax.AMOUNT_INCLUDES_TAX_FLAG = 'Y' OR
(nvl(tax.AMOUNT_INCLUDES_TAX_FLAG, 'N') = 'N' AND
nvl(line.gross_extended_amount,0) <> 0 ))
AND header.INVOICE_CURRENCY_CODE = currency.CURRENCY_CODE
GROUP BY line.CUSTOMER_TRX_LINE_ID, header.INVOICE_CURRENCY_CODE,
header.EXCHANGE_RATE, currency.PRECISION,
currency.MINIMUM_ACCOUNTABLE_UNIT
UNION -- following is for lines w/out tax
SELECT line.CUSTOMER_TRX_LINE_ID customer_trx_line_id,
0 inclusive_amount,
header.invoice_currency_code currency_code,
header.exchange_rate exchange_rate,
currency.precision precision,
currency.minimum_accountable_unit mau
FROM RA_CUSTOMER_TRX header,
FND_CURRENCIES currency,
RA_CUSTOMER_TRX_LINES line
WHERE header.CUSTOMER_TRX_ID = p_trx_id
AND line.CUSTOMER_TRX_ID = header.CUSTOMER_TRX_ID
AND line.LINE_TYPE = 'LINE'
AND nvl(line.gross_extended_amount,0) <> 0
AND header.INVOICE_CURRENCY_CODE = currency.CURRENCY_CODE
AND NOT EXISTS
(SELECT 'any tax line'
FROM ra_customer_trx_lines tax
WHERE tax.customer_trx_id = line.customer_trx_id
AND tax.link_to_cust_trx_line_id =
line.customer_trx_line_id
AND tax.line_type = 'TAX');
SELECT /*+ index (line RA_CUSTOMER_TRX_LINES_N4) */
line.CUSTOMER_TRX_LINE_ID customer_trx_line_id,
sum(decode(line.extended_amount, 0, 0,
tax.extended_amount)) inclusive_amount,
header.invoice_currency_code currency_code,
header.exchange_rate exchange_rate,
currency.precision precision,
currency.minimum_accountable_unit mau
FROM RA_CUSTOMER_TRX header,
FND_CURRENCIES currency,
RA_CUSTOMER_TRX_LINES line,
RA_CUSTOMER_TRX_LINES tax
WHERE header.REQUEST_ID = p_request_id
AND NVL(header.PREVIOUS_CUSTOMER_TRX_ID, -99) =
DECODE(p_phase, 'CM', header.PREVIOUS_CUSTOMER_TRX_ID, -99)
AND line.CUSTOMER_TRX_ID = header.CUSTOMER_TRX_ID
AND line.LINE_TYPE = 'LINE'
AND line.request_id = p_request_id -- 7039838
AND tax.LINK_TO_CUST_TRX_LINE_ID = line.CUSTOMER_TRX_LINE_ID
AND tax.LINE_TYPE = 'TAX'
AND tax.AMOUNT_INCLUDES_TAX_FLAG = 'Y'
AND tax.CUSTOMER_TRX_ID = line.CUSTOMER_TRX_ID
AND header.INVOICE_CURRENCY_CODE = currency.CURRENCY_CODE
GROUP BY line.CUSTOMER_TRX_LINE_ID, header.INVOICE_CURRENCY_CODE,
header.EXCHANGE_RATE, currency.PRECISION, currency.MINIMUM_ACCOUNTABLE_UNIT;
SELECT
sob.currency_code,
c.precision,
c.minimum_accountable_unit
INTO
l_base_currency,
l_base_precision,
l_base_mau
FROM gl_sets_of_books sob,
fnd_currencies c,
ar_system_parameters sp
WHERE sob.set_of_books_id = sp.set_of_books_id
AND sob.currency_code = c.currency_code;
of the inclusive taxes and update the lines, sc, and dist
for the reduction of that amount. */
FOR trx IN c_trx(p_trx_id) LOOP
IF PG_DEBUG IN ('C','Y')
THEN
debug(trx.customer_trx_line_id || ':' ||
trx.inclusive_amount);
arp_ctl_pkg.update_amount_f_ctl_id(
trx.customer_trx_line_id,
trx.inclusive_amount,
l_new_extended_amount,
l_new_unit_selling_price,
trx.precision, trx.mau);
arp_ctls_pkg.update_amounts_f_ctl_id(
trx.customer_trx_line_id,
l_new_extended_amount,
trx.currency_code);
arp_ctlgd_pkg.update_amount_f_ctl_id(
trx.customer_trx_line_id,
l_new_extended_amount,
trx.currency_code,
l_base_currency,
trx.exchange_rate,
l_base_precision,
l_base_mau);
of the inclusive taxes and update the lines, sc, and dist
for the reduction of that amount. */
FOR trx IN c_req(p_request_id) LOOP
IF PG_DEBUG IN ('C','Y')
THEN
debug(trx.customer_trx_line_id || ':' ||
trx.inclusive_amount);
arp_ctl_pkg.update_amount_f_ctl_id(
trx.customer_trx_line_id,
trx.inclusive_amount,
l_new_extended_amount,
l_new_unit_selling_price,
trx.precision, trx.mau);
arp_ctls_pkg.update_amounts_f_ctl_id(
trx.customer_trx_line_id,
l_new_extended_amount,
trx.currency_code);
arp_ctlgd_pkg.update_amount_f_ctl_id(
trx.customer_trx_line_id,
l_new_extended_amount,
trx.currency_code,
l_base_currency,
trx.exchange_rate,
l_base_precision,
l_base_mau);
UPDATE ra_customer_trx_lines mtl
SET line_recoverable = extended_amount,
tax_recoverable = (select sum(extended_amount)
from ra_customer_trx_lines sqtl
where sqtl.link_to_cust_trx_line_id =
mtl.customer_trx_line_id
and sqtl.customer_trx_id =
mtl.customer_trx_id
and sqtl.line_type = 'TAX')
WHERE mtl.customer_trx_id = p_trx_id
AND mtl.line_type = 'LINE';
UPDATE ra_customer_trx_lines mtl
SET line_recoverable = extended_amount,
tax_recoverable = (select sum(extended_amount)
from ra_customer_trx_lines sqtl
where sqtl.link_to_cust_trx_line_id =
mtl.customer_trx_line_id
and sqtl.customer_trx_id =
mtl.customer_trx_id
and sqtl.line_type = 'TAX')
WHERE mtl.request_id = p_request_id
AND NVL(mtl.previous_customer_trx_id, -99) =
DECODE(p_phase, 'CM', mtl.previous_customer_trx_id, -99)
AND mtl.line_type = 'LINE';
SELECT
ar_tax.location_segment_id,
ar_tax.org_id,
ar_tax.tax_line_id,
ar_tax.vat_tax_id,
ar_tax.set_of_books_id,
NVL(ar_rec.gl_date, TRUNC(sysdate))
INTO
l_location_segment_id,
l_org_id,
l_tax_line_id,
l_tax_rate_id,
l_sob_id,
l_gl_date
FROM
ra_customer_trx_lines ar_tax,
ra_cust_trx_line_gl_dist ar_rec
WHERE
ar_tax.customer_trx_line_id = p_subject_id
AND ar_tax.customer_trx_id = ar_rec.customer_trx_id
AND ar_rec.account_class = 'REC'
AND ar_rec.latest_rec_flag = 'Y';
SELECT NVL(rt.tax_recoverable_flag, 'N'),
rt.receivables_trx_id
INTO l_tax_recov,
l_rec_act_id
FROM ar_cash_receipts cr,
ar_receipt_method_accounts arm,
ar_receivables_trx rt
WHERE cr.cash_receipt_id = p_receipt_id
AND cr.receipt_method_id = arm.receipt_method_id
AND cr.remit_bank_acct_use_id = arm.remit_bank_acct_use_id
AND DECODE(p_mode,
'APP_ED',arm.edisc_receivables_trx_id,
'APP_UED',arm.unedisc_receivables_trx_id) =
rt.receivables_trx_id (+);
select max(nvl(tld.discount_percent/100,0))
into g_rate_for_disc
from ra_terms_lines_discounts tld,
ra_customer_trx trx
where trx.customer_trx_id = p_trx_id
and trx.term_id = tld.term_id (+);
select trx_id, trx_line_id, message_name, message_text
from zx_validation_errors_gt
where application_id = l_trx_rec.application_id
and entity_code = l_trx_rec.entity_code
and event_class_code = l_trx_rec.event_class_code
and trx_id = l_trx_rec.trx_id;
DELETE from ZX_TRX_HEADERS_GT zx
WHERE application_id = 222
AND entity_code = 'TRANSACTIONS'
AND (trx_id, event_class_code) IN
(SELECT trx.customer_trx_id, decode(t.type, 'INV', 'INVOICE',
'CM', 'CREDIT_MEMO', 'DM','DEBIT_MEMO')
FROM ra_customer_trx trx, ra_cust_trx_types t
WHERE trx.request_id = p_request_id
AND trx.complete_flag = 'N'
AND trx.cust_trx_type_id = t.cust_trx_type_id
AND trx.org_id = t.org_id);