[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dummy constants for use in update and lock operations |
+--------------------------------------------------------*/
AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
/* This insert copied from the logic in arp_credit_memo_module.
The idea is that copying invoice tax accounting is pretty
simple, but we need the logic to be callable at any time
rather than tied to other arp_credit_memo_module behavior */
/* 5413663 - due to concerns that this code might create
incorrect or poorly timed tax dists, I have modified
the program_id logic to use -5 instead of a valid value.
That way, we can tell if this code created the tax or
if it came from somewhere else (rev rec, autoaccounting) */
INSERT into ra_cust_trx_line_gl_dist
(
/* gl_dist_id used to be here - now populated by BRI trigger */
customer_trx_id, /* credit memo customer_trx_id */
customer_trx_line_id, /* credit memo customer_trx_line_id */
set_of_books_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
code_combination_id,
posting_control_id,
collected_tax_ccid,
ussgl_transaction_code,
org_id,
event_id
)
SELECT
ctl.customer_trx_id,
ctl.customer_trx_line_id,
ct.set_of_books_id,
sysdate,
ct.last_updated_by,
sysdate,
ct.created_by,
ct.last_update_login,
ctl.program_application_id, /* program_appl_id */
-5, /* program_id */
sysdate, /* program_update_date */
'TAX',
'N',
decode(ctl.extended_amount, 0, prev_ctlgd.percent,
round(((decode(foreign_fc.minimum_accountable_unit,
null, round(
NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount) , foreign_fc.precision),
round(
NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount)
/ foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit) /
decode(ctl.extended_amount, 0, 1, ctl.extended_amount)) *
decode(ctl.extended_amount, 0, 0, 1))
* 100, 4)), /* percent */
decode(foreign_fc.minimum_accountable_unit,
null, round(NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,
prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount), foreign_fc.precision),
round(NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,
prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount)
/ foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit
), /* amount */
decode(base_min_acc_unit, NULL,
round(decode(foreign_fc.minimum_accountable_unit,
null, round(NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,
prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount), foreign_fc.precision),
round(NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,
prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount)
/ foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit) *
nvl(ct.exchange_rate, 1),
base_precision),
round(decode(foreign_fc.minimum_accountable_unit,
null, round(NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,
prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount), foreign_fc.precision),
round(NVL(prev_ctlgd.amount /
decode(prev_ctl.extended_amount,0,1,
prev_ctl.extended_amount),1) *
decode(ctl.extended_amount,0,
decode(prev_ctl.extended_amount,0,-1,0),
ctl.extended_amount)
/ foreign_fc.minimum_accountable_unit) *
foreign_fc.minimum_accountable_unit) *
nvl(ct.exchange_rate, 1) /
base_min_acc_unit) * base_min_acc_unit),
/* acctd_amount */
rec_ctlgd.gl_date,
prev_ctlgd.code_combination_id,
-3,
prev_ctlgd.collected_tax_ccid,
ct.default_ussgl_transaction_code,
ct.org_id,
rec_ctlgd.event_id
FROM
fnd_currencies foreign_fc,
ra_customer_trx ct,
ra_customer_trx_lines ctl,
ra_cust_trx_line_gl_dist ctlgd,
ra_cust_trx_line_gl_dist rec_ctlgd, /* cm rec dist */
ra_customer_trx prev_ct,
ra_customer_trx_lines prev_ctl,
ra_cust_trx_line_gl_dist prev_ctlgd
WHERE
ct.customer_trx_id = p_customer_trx_id
AND ct.customer_trx_id = ctl.customer_trx_id
AND ctl.line_type = 'TAX'
/* Do not duplicate if already there */
AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id (+)
AND ctlgd.customer_trx_id IS NULL
/* Get CM Rec row (for gl_date) */
AND ct.customer_trx_id = rec_ctlgd.customer_trx_id (+)
AND rec_ctlgd.account_class (+) = 'REC'
AND rec_ctlgd.latest_rec_flag (+) = 'Y'
AND ct.invoice_currency_code = foreign_fc.currency_code
/* Join to the invoice */
AND ctl.previous_customer_trx_line_id
= prev_ctl.customer_trx_line_id(+)
AND prev_ctl.customer_trx_line_id
= prev_ctlgd.customer_trx_line_id(+)
AND prev_ctl.customer_trx_id = prev_ct.customer_trx_id(+)
/* 5413663 - only non-model dists */
AND prev_ctlgd.account_set_flag = 'N';
arp_util.debug(' tax dists inserted = ' || l_rows);
These rows are later used for a bulk update
of ra_cust_trx_line_gl_dist */
PROCEDURE record_tax_accounts(p_customer_trx_id IN number)
IS
CURSOR tax_line_and_dist(p_customer_trx_id NUMBER) IS
SELECT tl.customer_trx_id, -- trx_id
tl.link_to_cust_trx_line_id, -- parent line
tgl.cust_trx_line_gl_dist_id, -- tax dist ID
NVL(tgl.cust_trx_line_salesrep_id,
-99), -- SR ID (from dist)
tl.tax_line_id, -- originated tax line in ebt
tgl.amount, -- tax amount (not currently used)
tgl.account_set_flag, -- account set Y/N
zx.tax_regime_code, -- ZX tax regime code
zx.tax, -- ZX tax code
tgl.code_combination_id, -- tax account!
tl.vat_tax_id, -- Tax Rate ID
tgl.collected_tax_ccid, -- Collected Tax ccid for deferrred taxes
tgl.attribute_category,
tgl.attribute1,
tgl.attribute2,
tgl.attribute3,
tgl.attribute4,
tgl.attribute5,
tgl.attribute6,
tgl.attribute7,
tgl.attribute8,
tgl.attribute9,
tgl.attribute10,
tgl.attribute11,
tgl.attribute12,
tgl.attribute13,
tgl.attribute14,
tgl.attribute15,
tgl.comments
FROM ra_customer_trx_lines tl,
ra_cust_trx_line_gl_dist tgl,
zx_lines zx
WHERE tl.customer_trx_id = p_customer_trx_id
AND tl.line_type = 'TAX'
AND tl.customer_trx_line_id = tgl.customer_trx_line_id
AND tgl.code_combination_id <> -1 -- skip invalid accounts
-- Bug 9012585: This will have value only for deferred tax so using NVL
AND nvl(tgl.collected_tax_ccid, 0) <> -1
AND tl.tax_line_id = zx.tax_line_id;
/* Uses tax account tables to bulk update ra_cust_trx_line_gl_dist with
corrected or overridden accounts. */
PROCEDURE replace_tax_accounts
IS
l_rows NUMBER := 0;
/* Bulk update of gl_dist rows for tax...
Note that this code updates all tax accounting rows where
the new and old tax accounts are different. Additionally,
it will never bring forward an invalid account (ccid -1).
we match up the tax regime, tax, salesrep_id, account_set_flag,
and line_id. This may need to be adjusted later if we find
reasons to not preserve the original tax accounts */
IF t_customer_trx_id.EXISTS(1)
THEN
FORALL i IN t_customer_trx_id.FIRST .. t_customer_trx_id.LAST
UPDATE ra_cust_trx_line_gl_dist gld
SET code_combination_id = t_code_combination_id(i),
collected_tax_ccid = t_collected_tax_ccid(i),
comments = t_comments(i),
attribute_category = t_attribute_category(i),
attribute1 = t_attribute1(i),
attribute2 = t_attribute2(i),
attribute3 = t_attribute3(i),
attribute4 = t_attribute4(i),
attribute5 = t_attribute5(i),
attribute6 = t_attribute6(i),
attribute7 = t_attribute7(i),
attribute8 = t_attribute8(i),
attribute9 = t_attribute9(i),
attribute10 = t_attribute10(i),
attribute11 = t_attribute11(i),
attribute12 = t_attribute12(i),
attribute13 = t_attribute13(i),
attribute14 = t_attribute14(i),
attribute15 = t_attribute15(i)
WHERE customer_trx_id = t_customer_trx_id(i)
AND account_class = 'TAX'
AND cust_trx_line_gl_dist_id IN
(SELECT tgl.cust_trx_line_gl_dist_id
FROM ra_cust_trx_line_gl_dist tgl,
ra_customer_trx_lines tl,
zx_lines zx
WHERE tl.customer_trx_id = t_customer_trx_id(i)
AND tl.link_to_cust_trx_line_id =
t_customer_trx_line_id(i)
AND tl.line_type = 'TAX'
AND tl.customer_trx_line_id = tgl.customer_trx_line_id
AND tgl.account_class = 'TAX'
AND tgl.account_set_flag = t_account_set_flag(i)
AND ( tgl.code_combination_id <> t_code_combination_id(i)
-- Bug 9012585 : Honour manual override for collected_tax_ccid as well
OR NVL(tgl.collected_tax_ccid,0) <> NVL(t_collected_tax_ccid(i),0)
-- 13455779 - preserve comments and DFF too
OR t_comments(i) || t_attribute_category(i) IS NOT NULL)
AND nvl(tgl.cust_trx_line_salesrep_id, -99) =
t_cust_trx_line_salesrep_id(i)
AND tl.tax_line_id = zx.tax_line_id
AND tl.vat_tax_id = t_tax_rate_id(i)
AND tl.tax_line_id = t_tax_line_id(i));
arp_debug.debug(' distribution(s) updated = ' || l_rows);
| delete_tax_lines_from_ar
*===========================================================================*/
FUNCTION Calculate( p_customer_trx_id IN NUMBER,
p_cust_trx_line_id IN NUMBER,
p_action IN VARCHAR2,
p_line_level_action IN VARCHAR2 ) RETURN BOOLEAN IS
l_transaction_rec zx_api_pub.transaction_rec_type;
/* insert data into ebt plsql tables
arp_util.debug('calling populate_ebt_plsql_tables ');
arp_etax_util.delete_Tax_lines_from_ar(p_customer_trx_id);
p_rows_inserted => l_rows);
SELECT
TRX.org_id, -- internal_organization_id
222, -- application_id
'TRANSACTIONS', -- entity_code
p_event_class_code, -- event_class_code
p_event_type_code, -- event_type_code
p_customer_trx_id, -- trx_id
TRX.trx_date, -- trx_date
AR.set_of_books_id, -- ledger_id
TRX.invoice_currency_code, -- trx_currency_code
TRX.exchange_date, -- currency_conversion_date
TRX.exchange_rate, -- currency_conversion_rate
TRX.exchange_rate_type, -- currency_conversion_type
CURR.minimum_accountable_unit, -- minimum_accountable_unit
CURR.precision, -- precision
TRX.legal_entity_id, -- legal_entity_id
'LINE', -- trx_level_type
----p_line_level_action, -- line_level_action ?????? *****
DECODE(TRX.previous_customer_trx_id,
NULL, p_line_level_action, DECODE(INV_TT.TYPE,'DEP','RECORD_WITH_NO_TAX',p_line_level_action)), -- p_line_level_action
p_customer_trx_line_id, -- trx_line_id
-- trx_business_category
TRX.cust_trx_type_id, -- receivables_trx_type_id
'Y', -- tax_reporting_flag
'N', -- Quote_Flag
LINES.tax_classification_code, -- output_tax_classification_code
NULL, -- interface_entity_code
NULL, -- interface_line_id
LINES.line_number, -- trx_line_number
LINES.historical_flag, -- historical_flag
TRX.trx_number, -- trx_number
substrb(TRX.comments,1,240), -- trx_description
TRX.printing_original_date, -- trx_communicated_date
TRX.batch_source_id, -- batch_source_id
BS.NAME, -- batch_source_name
TRX.doc_sequence_id, -- doc_seq_id
SEQ.name, -- doc_seq_name
TRX.doc_sequence_value, -- doc_seq_value
TRX.term_due_date, -- trx_due_date
TYPES.description, -- trx_type_description
NVL(REC.gl_date, TRUNC(sysdate)), --trx_line_gl_date
DECODE(TYPES.type,
'CM', 'CREDIT_MEMO',
'DM', 'DEBIT_MEMO',
'INVOICE'), -- line_class
LINES.sales_order_date, -- trx_shipping_date
DECODE(LINES.inventory_item_id, NULL, 'MISC', 'ITEM'), -- trx_line_type
NULL, -- trx_line_date
DECODE(LINES.amount_includes_tax_flag, 'Y',
'A','N', 'N', 'S'), -- line_amt_includes_tax_flag
NVL(LINES.GROSS_EXTENDED_AMOUNT,LINES.extended_amount), -- line_amt Bug 7692158
DECODE(TYPES.type,
'CM', LINES.quantity_credited,
LINES.quantity_invoiced), -- trx_line_quantity -- Bug 8717137
LINES.unit_selling_price, -- unit_price
DECODE(LINES.previous_customer_trx_line_id,
NULL, LINES.tax_exempt_flag,
INV_L.tax_exempt_flag), -- exemption_control_flag
DECODE(LINES.previous_customer_trx_line_id,
NULL, LINES.tax_exempt_number,
INV_L.tax_exempt_number), -- exempt_certificate_number
DECODE(LINES.previous_customer_trx_line_id,
NULL, LINES.tax_exempt_reason_code,
INV_L.tax_exempt_reason_code),-- exempt_reason
NVL(LINES.inventory_item_id,
LINES.memo_line_id), -- product_id
LINES.uom_code, -- uom_code
TRX.fob_point, -- fob_point
LINES.warehouse_id, -- ship_from_party_id
HR.location_id, -- ship_from_location_id
BILL_CUST.party_id, -- bill_to_party_id
BILL_CUST.party_id, -- rounding_bill_to_party_id
BILL_AS.party_site_id, -- bill_to_party_site_id
BILL_AS.party_site_id, -- rndg_bill_to_party_site_id
BILL_LOC.location_id, -- bill_to_location_id
-- account_ccid ***see select below due to possible multiple records
-- source_application_id
-- source_entity_code
-- source_event_class_code
-- source_trx_id
-- source_lines_id
-- source_trx_level_type
-- tax_amt_included_flag
TRX.ship_to_customer_id,
TRX.ship_to_site_use_id,
LINES.ship_to_customer_id,
LINES.ship_to_site_use_id,
TRX.invoice_currency_code, -- trx_line_currency_code
CURR.precision, -- trx_line_precision
/*Bug8650264, Modified the code to pass adjusted_doc details as NULL for
Deposit and Guarantee.*/
/*Bug8731231, Modified the code to pass adjusted_doc details as NULL for
Chargeback */
DECODE(TRX.previous_customer_trx_id,
NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,222)), -- adjusted_doc_application_id
DECODE(TRX.previous_customer_trx_id,
NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,'TRANSACTIONS')),-- adjusted_doc_entity_code
/* bug6769106 vavenugo
modified the line below to pass the correct value for adjusted_doc_event_class_code based on the type of the document */
DECODE(TRX.previous_customer_trx_id,
NULL, NULL, DECODE(INV_TT.TYPE,'DM','DEBIT_MEMO','DEP',NULL,'GUAR',NULL,'CB',NULL,'INVOICE')), -- adjusted_doc_event_class_Code
DECODE(TRX.previous_customer_trx_id,
NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,TRX.previous_customer_trx_id)), -- adjusted_doc_trx_id
DECODE(LINES.previous_customer_trx_line_id, NULL, NULL,
DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,LINES.previous_customer_trx_line_id)), -- adjusted_doc_line_id
DECODE(TRX.previous_customer_trx_id,
NULL, NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,'LINE')), -- adjusted_doc_trx_level_type
DECODE(TRX.previous_customer_trx_id, NULL,
NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,INV.trx_number)), -- adjusted_doc_number
DECODE(TRX.previous_customer_trx_id, NULL,
NULL, DECODE(INV_TT.TYPE,'DEP',NULL,'GUAR',NULL,'CB',NULL,INV.trx_date)), -- adjusted_doc_date
/* 4666566 */
TRX.bill_to_customer_id,
TRX.bill_to_site_use_id,
BILL_AS.cust_acct_site_id,
DECODE(LINES.memo_line_id, NULL,
NVL(LINES.warehouse_id,to_number(pg_so_org_id)),NULL),
TRX.org_id, -- poa_party_id
HRL.location_id, -- poa_location_id
DECODE(REL_T.customer_trx_id, NULL, NULL, 222),
DECODE(REL_T.customer_trx_id, NULL, NULL, 'TRANSACTIONS'),
DECODE(REL_T.customer_trx_id, NULL, NULL,
DECODE(REL_TT.type, 'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO')),
DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.customer_trx_id),
DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_number),
DECODE(REL_T.customer_trx_id, NULL, NULL, REL_T.trx_date),
HRL.location_id, -- bill_from_location_id
ML.tax_product_category, -- bug6770861, 6874006
LINES.description,
INV_TT.allow_overapplication_flag
INTO
-- internal_organization_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERNAL_ORGANIZATION_ID(1),
-- application_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.APPLICATION_ID(1),
-- entity_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ENTITY_CODE(1),
-- event_class_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_CLASS_CODE(1),
-- event_type_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EVENT_TYPE_CODE(1),
-- trx_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_ID(1),
-- trx_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DATE(1),
-- ledger_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEDGER_ID(1),
-- trx_currency_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_CURRENCY_CODE(1),
-- currency_conversion_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_DATE(1),
-- currency_conversion_rate
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_RATE(1),
-- currency_conversion_type
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.CURRENCY_CONVERSION_TYPE(1),
-- minimum_accountable_unit
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.MINIMUM_ACCOUNTABLE_UNIT(1),
-- precision
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRECISION(1),
-- legal_entity_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LEGAL_ENTITY_ID(1),
-- trx_level_type
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LEVEL_TYPE(1),
-- line_level_action ?????? *****
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_LEVEL_ACTION(1),
-- trx_line_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_ID(1),
-- trx_business_category
-- receivables_trx_type_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RECEIVABLES_TRX_TYPE_ID(1),
-- tax_reporting_flag
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_REPORTING_FLAG(1),
-- Quote_Flag
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.QUOTE_FLAG(1),
-- output_tax_classification_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.OUTPUT_TAX_CLASSIFICATION_CODE(1),
-- interface_entity_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_ENTITY_CODE(1),
-- interface_line_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.INTERFACE_LINE_ID(1),
-- trx_line_number
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_NUMBER(1),
-- historical_flag
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.HISTORICAL_FLAG(1),
-- trx_number
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_NUMBER(1),
-- trx_description
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DESCRIPTION(1),
-- trx_communicated_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_COMMUNICATED_DATE(1),
-- batch_source_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_ID(1),
-- batch_source_name
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BATCH_SOURCE_NAME(1),
-- doc_seq_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_ID(1),
-- doc_seq_name
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_NAME(1),
-- doc_seq_value
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.DOC_SEQ_VALUE(1),
-- trx_due_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_DUE_DATE(1),
-- trx_type_description
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_TYPE_DESCRIPTION(1),
-- trx_line_gl_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_GL_DATE(1),
-- line_class
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_CLASS(1),
-- trx_shipping_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_SHIPPING_DATE(1),
-- trx_line_type
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_TYPE(1),
-- trx_line_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_DATE(1),
-- line_amt_includes_tax_flag
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT_INCLUDES_TAX_FLAG(1),
-- line_amt
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.LINE_AMT(1),
-- trx_line_quantity
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_QUANTITY(1),
-- unit_price
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UNIT_PRICE(1),
-- exemption_control_flag
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPTION_CONTROL_FLAG(1),
-- exempt_certificate_number
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_CERTIFICATE_NUMBER(1),
-- exempt_reason
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.EXEMPT_REASON_CODE(1),
-- product_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ID(1),
-- uom_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.UOM_CODE(1),
-- fob_point
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.FOB_POINT(1),
-- ship_from_party_id, location_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_PARTY_ID(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_FROM_LOCATION_ID(1),
-- bill_to_party_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_ID(1),
-- rounding_bill_to_party_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_BILL_TO_PARTY_ID(1),
-- bill_to_party_site_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_PARTY_SITE_ID(1),
-- rndg_bill_to_party_site_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_BILL_TO_PARTY_SITE_ID(1),
-- bill_to_location_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_LOCATION_ID(1),
-- SHIP TO information for later derivation
l_hdr_ship_to_cust_id,
l_hdr_ship_to_su_id,
l_line_ship_to_cust_id,
l_line_ship_to_su_id,
-- trx_line_currency_code
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_CURRENCY_CODE(1),
-- trx_line_precison
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TRX_LINE_PRECISION(1),
--adjusted_doc_application_id,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_APPLICATION_ID(1),
--adjusted_doc_entity_code,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_ENTITY_CODE(1),
--adjusted_doc_event_class_code,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_EVENT_CLASS_CODE(1),
--adjusted_doc_trx_id,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_TRX_ID(1),
--adjusted_doc_line_id,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_LINE_ID(1),
--adjusted_doc_trx_level_type,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_TRX_LEVEL_TYPE(1),
--adjusted_doc_number,
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_NUMBER(1),
--adjusted_doc_date
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ADJUSTED_DOC_DATE(1),
/* 4666566 */
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_ID(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_TO_CUST_ACCT_SITE_USE_ID(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.BILL_THIRD_PTY_ACCT_SITE_ID(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.PRODUCT_ORG_ID(1),
/* 5082548 - poo and poa values */
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_party_id(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.poa_location_id(1),
/* 6874006 - moved poo values to separate statement below */
/* 5345904 - related_doc columns */
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_application_id(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_entity_code(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_event_class_code(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_trx_id(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_number(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.related_doc_date(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.bill_from_location_id(1),
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.product_category(1),
ZX_GLOBAL_STRUCTURES_PKG.TRX_LINE_DIST_TBL.TRX_LINE_DESCRIPTION(1),
l_allow_overapp
FROM
RA_CUSTOMER_TRX TRX,
RA_CUSTOMER_TRX_LINES LINES,
RA_CUST_TRX_LINE_GL_DIST REC,
FND_CURRENCIES CURR,
FND_DOCUMENT_SEQUENCES SEQ,
AR_SYSTEM_PARAMETERS AR,
RA_BATCH_SOURCES BS,
RA_CUST_TRX_TYPES TYPES,
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,
HZ_LOCATIONS BILL_LOC,
RA_CUSTOMER_TRX INV,
RA_CUST_TRX_TYPES INV_TT,
RA_CUSTOMER_TRX_LINES INV_L,
HR_ALL_ORGANIZATION_UNITS HR,
HR_ORGANIZATION_UNITS HRL,
RA_CUSTOMER_TRX REL_T,
RA_CUST_TRX_TYPES REL_TT,
AR_MEMO_LINES_B ML
WHERE
TRX.customer_trx_id = p_customer_trx_id and
TRX.customer_trx_id = LINES.customer_trx_id and
TRX.previous_customer_trx_id = INV.customer_trx_id (+) and
LINES.previous_customer_trx_line_id = INV_L.customer_trx_line_id (+) and
INV.cust_trx_type_id = INV_TT.cust_trx_type_id (+) and
TRX.doc_sequence_id = SEQ.doc_sequence_id (+) and
LINES.customer_trx_line_id = p_customer_trx_line_id and
REC.customer_Trx_id = TRX.customer_Trx_id and
REC.account_class = 'REC' and
REC.latest_rec_flag = 'Y' and
TRX.invoice_currency_code = CURR.currency_code and
TRX.org_id = AR.org_id and
TRX.batch_source_id = BS.batch_source_id and
TRX.cust_trx_type_id = TYPES.cust_trx_type_id 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
BILL_PS.location_id = BILL_LOC.location_id and
LINES.warehouse_id = HR.organization_id (+) and
TRX.org_id = HRL.organization_id and
TRX.related_customer_trx_id = REL_T.customer_trx_id (+) and
REL_T.cust_trx_type_id = REL_TT.cust_trx_type_id (+) and
LINES.memo_line_id = ML.memo_line_id (+) and
LINES.org_id = ML.org_id(+);
SELECT ctl.inventory_item_id, ctl.memo_line_id,
ctx.cust_trx_type_id, ctx.primary_salesrep_id
INTO p_inv_item_id, p_memo_line_id, p_trx_type_id, p_salesrep_id
FROM ra_customer_trx_lines ctl, ra_customer_trx ctx
WHERE ctl.customer_trx_id = p_customer_trx_id
AND ctl.customer_trx_line_id = p_customer_trx_line_id
AND ctl.customer_trx_id=ctx.customer_trx_id;
select SR_PER.organization_id, -- poo_party_id
SR_HRL.location_id -- poo_location_id
into pg_poo_party_id, pg_poo_location_id
from JTF_RS_SALESREPS SR,
PER_ALL_ASSIGNMENTS_F SR_PER,
HR_ORGANIZATION_UNITS SR_HRL
where SR.salesrep_id = pg_salesrep_id
and SR.org_id = pg_org_id
and SR.person_id = SR_PER.person_id
and l_trx_date BETWEEN
nvl(SR_PER.effective_start_date, l_trx_date)
AND nvl(SR_PER.effective_end_date, l_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;
SELECT
CUST_ACCT.party_id,
CUST_ACCT.party_id,
ACCT_SITE.party_site_id,
ACCT_SITE.party_site_id,
LOC.location_id,
ACCT_SITE.cust_acct_site_id
INTO
-- ship_to_party_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_ID(1),
-- rounding_ship_to_party_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.ROUNDING_SHIP_TO_PARTY_ID(1),
-- ship_to_party_site_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_PARTY_SITE_ID(1),
-- rndg_ship_to_party_site_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.RNDG_SHIP_TO_PARTY_SITE_ID(1),
-- ship_to_location_id
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_TO_LOCATION_ID(1),
/* 4666566 */
-- ship_third_pty_acct_site_id (warehouse id)
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.SHIP_THIRD_PTY_ACCT_SITE_ID(1)
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,
hz_locations LOC
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
ACCT_SITE.party_site_id = PARTY_SITE.party_site_id AND
PARTY_SITE.location_id = LOC.location_id;
SELECT sum(invp.amount_line_items_remaining) /
count(distinct cml.customer_trx_line_id),
sum(invp.tax_remaining) /
count(distinct cml.customer_trx_line_id),
sum(cml.extended_amount) /
count(distinct invp.payment_schedule_id)
INTO l_inv_line_remaining,
l_inv_tax_remaining,
l_cm_line_total
FROM ra_customer_trx cm,
ra_customer_trx_lines cml,
ar_payment_schedules invp
WHERE cm.customer_trx_id = p_customer_trx_id
AND cm.customer_trx_id = cml.customer_trx_id
AND cml.line_type = 'LINE'
AND cm.previous_customer_trx_id = invp.customer_trx_id;
SELECT inv_pay.amount_line_items_remaining, inv_pay.tax_remaining,
cm_line.line_amount, cm_line.tax_amount
INTO l_inv_line_remaining, l_inv_tax_remaining, l_cmline_line_amount, l_cmline_tax_amount
FROM ar_payment_schedules inv_pay, ra_customer_trx cm, ra_cm_requests cm_line
WHERE cm.customer_trx_id = p_customer_trx_id
AND cm_line.customer_trx_id = cm.previous_customer_trx_id
AND cm_line.cm_customer_trx_id is null
AND cm.previous_customer_trx_id = inv_pay.customer_trx_id;
SELECT sum(invp.amount_line_items_remaining) /
count(distinct cml.customer_trx_line_id),
sum(invp.tax_remaining) /
count(distinct cml.customer_trx_line_id),
sum(cml.extended_amount) /
count(distinct invp.payment_schedule_id)
INTO l_inv_line_remaining,
l_inv_tax_remaining,
l_cm_line_total
FROM ra_customer_trx cm,
ra_customer_trx_lines cml,
ar_payment_schedules invp
WHERE cm.customer_trx_id = p_customer_trx_id
AND cm.customer_trx_id = cml.customer_trx_id
AND cml.line_type = 'LINE'
AND cm.previous_customer_trx_id = invp.customer_trx_id;
| delete_tax_f_ctl_id |
| |
| DESCRIPTION |
| This routine will delete one or more tax lines given the invoice line |
| of type LINE that they can all be linked too, returning old and new |
| tax amounts. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| |
| ARGUMENTS : IN: p_customer_trx_line_id |
| p_error_mode -- default 'STANDARD' |
| OUT: p_old_tax_amount |
| p_new_tax_amount |
| |
| NOTES
| |
| MODIFICATION HISTORY |
| 14-Jun-2005 Debbie Jancis Created |
| |
+===========================================================================*/
PROCEDURE delete_tax_f_ctl_id( p_customer_trx_line_id IN Number ) IS
cursor s_tax_lines_for_inv_line( p_customer_trx_line_id in number ) IS
SELECT lines.customer_trx_line_id
FROM ra_customer_trx_lines lines
WHERE link_to_cust_trx_line_id = p_customer_trx_line_id
AND line_type = 'TAX';
arp_util.debug('ARP_ETAX_SERVICES_PKG.delete_tax_f_ctl_id()+');
| Delete the account assignments and account sets associated with |
| this tax line. |
*********************************************************************/
arp_ctlgd_pkg.delete_f_ctl_id( tax.customer_trx_line_id, null, null );
| Call the table handler to delete the tax record |
*********************************************************************/
arp_ctl_pkg.delete_p( tax.customer_trx_line_id );
arp_util.debug('ARP_ETAX_SERVICES_PKG.delete_tax_f_ctl_id()-');
arp_util_tax.debug( 'EXCEPTION: arp_etax_services_pkg.delete_tax_f_ctl_id()');
END delete_tax_f_ctl_id;
| PROCEDURE Before_Update_Line
|
| DESCRIPTION
| Called from Invoice Line Entity handler. This proceudre will
| check each of the attributes of an invoice line that can affect
| tax and will return TRUE in p_recalc_tax if any of those attributes
| have changed.
|
| PARAMETERS:
| IN : p_customer_trx_line_id
| p_item_line_rec
| p_error_mode
| OUT : p_old_tax_amount
| p_new_tax_amount
| p_recalc_tax
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 13-JUN-2005 Debbie Sue Jancis Created
|
*===========================================================================*/
PROCEDURE before_update_line(
p_customer_trx_line_id IN Number,
p_line_rec IN ra_customer_trx_lines%rowtype,
p_recalc_tax OUT NOCOPY BOOLEAN ) IS
l_inventory_item_changed BOOLEAN;
arp_util.debug('ARP_ETAX_SERVICES_PKG.before_update_line()+');
arp_etax_services_pkg.delete_tax_f_ctl_id (p_customer_trx_line_id);
arp_util.debug('ARP_ETAX_SERVICES_PKG.before_update_line()-');
END before_update_line;
| FUNCTION Mark_tax_lines_deleted()
|
| DESCRIPTION
| This function will call the ETAX mark_tax_lines_deleted service. This
| API assumes that the calling code controls the commit cycle. This
| function will return a TRUE if the call to the ETAX service is
| successful, Otherwise, it will return FALSE.
|
| This should be called per invoice line.
|
| PARAMETERS:
| IN : p_customer_trx_line_id
| p_customer_trx_id
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 14-JUN-2005 Debbie Sue Jancis Created
|
*===========================================================================*/
FUNCTION Mark_Tax_Lines_Deleted( p_customer_trx_line_id IN Number,
p_customer_trx_id IN Number)
RETURN BOOLEAN IS
CURSOR TRX_Header IS
SELECT *
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
arp_util.debug('ARP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted()+');
p_action => 'UPDATE',
p_event_class_code => l_event_class_code,
p_event_type_code => l_event_type_code);
zx_api_pub.mark_tax_lines_deleted(
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,
p_transaction_line_rec => l_transaction_line_rec,
x_return_status => l_return_status_service,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
arp_util.debug('ARP_ETAX_SERVICES_PKG.Mark_Tax_Lines_Deleted()-');
END Mark_Tax_Lines_Deleted;
| PROCEDURE Before_Delete_Line
|
| DESCRIPTION
| Called from Invoice Line Entity handler. This procedure will delete
| the tax lines from ra_Customer_Trx_lines and its associated accounting
| and call the etax api's to mark the records for deletion in the ZX tables
|
| PARAMETERS:
| IN : p_customer_trx_line_id
| p_customer_trx_id
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 14-JUN-2005 Debbie Sue Jancis Created
|
*===========================================================================*/
PROCEDURE Before_Delete_Line( p_customer_trx_line_id IN Number,
p_customer_trx_id IN Number) IS
l_success BOOLEAN;
arp_util.debug('ARP_ETAX_SERVICES_PKG.before_delete_line()+');
arp_etax_services_pkg.delete_tax_f_ctl_id(
p_customer_trx_line_id => p_customer_trx_line_id);
l_success := arp_etax_services_pkg.Mark_Tax_Lines_Deleted (
p_customer_trx_line_id => p_customer_trx_line_id,
p_customer_trx_id => p_customer_trx_id);
arp_util.debug('ARP_ETAX_SERVICES_PKG.before_delete_line()-');
END Before_Delete_Line;
| Public function that will call the INSERT_LINE_DET_FACTORS or
| UPDATE_LINE_DET_FACTORS service
| This API assumes the calling code controls the commit cycle.
|
| PARAMETERS
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 17-JUN-2005 Debbie Sue Jancis Created
| 16-AUG-2005 Jon Beckett Introduced INSERT_NO_LINE and
| INSERT_NO_TAX modes for lines where
| line amount or tax amount are zero.
| 08-MAY-2006 M Raymond 5197390 - Added logic to support
| calls for lines with memo line of
| type 'TAX'
|
*===========================================================================*/
PROCEDURE Line_det_factors ( p_customer_trx_line_id IN Number,
p_customer_trx_id IN Number,
p_mode IN VARCHAR2,
p_tax_amount IN NUMBER DEFAULT NULL,
p_called_from IN VARCHAR2 DEFAULT NULL) IS
l_event_class_code zx_trx_headers_gt.event_class_code%TYPE;
IF (p_mode IN ('INSERT','INSERT_NO_TAX','INSERT_NO_TAX_EVER',
'INSERT_NO_LINE')) THEN
l_action := 'CREATE';
l_action := 'UPDATE';
IF (p_mode = 'INSERT_NO_TAX') THEN
/* 5197390 - Changed to LINE_INFO_TAX_ONLY, was
ALLOCATE_LINE_ONLY_ADJUSTMENT */
l_line_level_action := 'LINE_INFO_TAX_ONLY';
ELSIF (p_mode = 'INSERT_NO_TAX_EVER') THEN
l_line_level_action := 'RECORD_WITH_NO_TAX';
ELSIF (p_mode = 'INSERT_NO_LINE') THEN
l_tax_amount := p_tax_amount;
ELSIF (p_mode = 'INSERT') THEN
l_line_level_action := 'CREATE';
l_line_level_action := 'UPDATE';
IF (p_mode IN ('INSERT','INSERT_NO_TAX','INSERT_NO_LINE')) THEN
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);
/* Added the initialization part before calling ZX update API --- Bug - 13097079 */
ZX_GLOBAL_STRUCTURES_PKG.trx_line_dist_tbl.TAX_INVOICE_DATE(1) := FND_API.G_MISS_DATE;
ZX_API_PUB.update_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 );
| Public function that will call the UPDATE_DET_FACTORS_HDR
| This API assumes the calling code controls the commit cycle.
|
| PARAMETERS
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 20-JUN-2005 Debbie Sue Jancis Created
| 04-NOV-2005 M Raymond 4713671 - initialize header det
| factor structure to not override
| ship to (and other) columns
| 29-NOV-2005 M Raymond 4763946 - init all header det factor
| parameters to G_MISS values.
| 09-JAN-2006 M Raymond 4928019 - handle void trx for etax
*===========================================================================*/
PROCEDURE Header_det_factors ( p_customer_trx_id IN Number,
p_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_hdr_det_factors_rec zx_api_pub.header_det_factors_rec_type;
IF (p_mode = 'UPDATE') THEN
l_action := 'UPDATE';
to prevent overlay of data in update det call */
l_hdr_det_factors_rec.trx_date := FND_API.G_MISS_DATE;
SELECT
TRX.org_id, -- internal_organization_id
222, -- application_id
'TRANSACTIONS', -- entity_code
l_event_class_code, -- event_class_code
l_event_type_code, -- event_type_code
p_customer_trx_id, -- trx_id
TRX.trx_date, -- trx_date
AR.set_of_books_id, -- ledger_id
TRX.invoice_currency_code, -- trx_currency_code
TRX.exchange_date, -- currency_conversion_date
TRX.exchange_rate, -- currency_conversion_rate
TRX.exchange_rate_type, -- currency_conversion_type
CURR.minimum_accountable_unit, -- minimum_accountable_unit
CURR.precision, -- precision
TRX.legal_entity_id, -- legal_entity_id
BILL_CUST.party_id, -- rounding_bill_to_party_id
BILL_AS.party_site_id, -- rndg_bill_to_party_site_id
TRX.cust_trx_type_id, -- receivables_trx_type_id
'Y', -- tax_reporting_flag
BILL_CUST.party_id, -- bill_to_party_id
BILL_AS.party_site_id, -- bill_to_party_site_id
BILL_LOC.location_id, -- bill_to_location_id
TRX.trx_number, -- trx_number
substrb(TRX.comments,1,240), -- trx_description
TRX.printing_original_date, -- trx_communicated_date
TRX.batch_source_id, -- batch_source_id
BS.NAME, -- batch_source_name
TRX.doc_sequence_id, -- doc_seq_id
-- bug 6806843
--TYPES.name, -- doc_seq_name
SEQ.name, -- doc_seq_name
TRX.doc_sequence_value, -- doc_seq_value
TRX.term_due_date, -- trx_due_date
TYPES.description, -- trx_type_description
TRX.ship_to_customer_id,
TRX.ship_to_site_use_id,
BILL_SU.site_use_id, --bill_to_cust_acct_site_use_id
DECODE(TRX.status_trx,'VD','VD',NULL),
TRX.bill_to_customer_id, --bill_third_pty_acct_id
BILL_AS.cust_acct_site_id --bill_third_pty_acct_site_id
INTO
l_hdr_det_factors_rec.internal_organization_id,
l_hdr_det_factors_rec.application_id,
l_hdr_det_factors_rec.entity_code,
l_hdr_det_factors_rec.event_class_code,
l_hdr_det_factors_rec.event_type_code,
l_hdr_det_factors_rec.trx_id,
l_hdr_det_factors_rec.trx_date,
l_hdr_det_factors_rec.ledger_id,
l_hdr_det_factors_rec.trx_currency_code,
l_hdr_det_factors_rec.currency_conversion_date,
l_hdr_det_factors_rec.currency_conversion_rate,
l_hdr_det_factors_rec.currency_conversion_type,
l_hdr_det_factors_rec.minimum_accountable_unit,
l_hdr_det_factors_rec.precision,
l_hdr_det_factors_rec.legal_entity_id,
l_hdr_det_factors_rec.rounding_bill_to_party_id,
l_hdr_det_factors_rec.rndg_bill_to_party_site_id,
l_hdr_det_factors_rec.receivables_trx_type_id,
l_hdr_det_factors_rec.tax_reporting_flag,
l_hdr_det_factors_rec.bill_to_party_id,
l_hdr_det_factors_rec.bill_to_party_site_id,
l_hdr_det_factors_rec.bill_to_location_id,
l_hdr_det_factors_rec.trx_number,
l_hdr_det_factors_rec.trx_description,
l_hdr_det_factors_rec.trx_communicated_date,
l_hdr_det_factors_rec.batch_source_id,
l_hdr_det_factors_rec.batch_source_name,
l_hdr_det_factors_rec.doc_seq_id,
l_hdr_det_factors_rec.doc_seq_name,
l_hdr_det_factors_rec.doc_seq_value,
l_hdr_det_factors_rec.trx_due_date,
l_hdr_det_factors_rec.trx_type_description,
l_hdr_ship_to_cust_id,
l_hdr_ship_to_su_id,
l_hdr_det_factors_rec.bill_to_cust_acct_site_use_id,
l_hdr_det_factors_rec.application_doc_status,
l_hdr_det_factors_rec.bill_third_pty_acct_id,
l_hdr_det_factors_rec.bill_third_pty_acct_site_id
FROM
RA_CUSTOMER_TRX TRX,
FND_CURRENCIES CURR,
FND_DOCUMENT_SEQUENCES SEQ,
AR_SYSTEM_PARAMETERS AR,
RA_BATCH_SOURCES BS,
RA_CUST_TRX_TYPES TYPES,
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,
HZ_LOCATIONS BILL_LOC
WHERE
TRX.customer_trx_id = p_customer_trx_id and
TRX.invoice_currency_code = CURR.currency_code and
TRX.org_id = AR.org_id and
TRX.batch_source_id = BS.batch_source_id and
TRX.cust_trx_type_id = TYPES.cust_trx_type_id and
TRX.doc_sequence_id = SEQ.doc_sequence_id (+) 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
BILL_PS.location_id = BILL_LOC.location_id;
SELECT count(*)
INTO l_llst_exists
FROM ra_customer_trx_lines
WHERE customer_trx_id = p_customer_trx_id
AND line_type = 'LINE'
AND ship_to_customer_id IS NOT NULL
AND ship_to_site_use_id IS NOT NULL;
SELECT
CUST_ACCT.party_id,
CUST_ACCT.party_id,
ACCT_SITE.party_site_id,
ACCT_SITE.party_site_id,
LOC.location_id,
SITE_USES.site_use_id
INTO
l_hdr_det_factors_rec.ship_to_party_id,
l_hdr_det_factors_rec.rounding_ship_to_party_id,
l_hdr_det_factors_rec.ship_to_party_site_id,
l_hdr_det_factors_rec.rndg_ship_to_party_site_id,
l_hdr_det_factors_rec.ship_to_location_id,
l_hdr_det_factors_rec.ship_to_cust_acct_site_use_id
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,
hz_locations LOC
WHERE
CUST_ACCT.cust_account_id = l_hdr_ship_to_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_hdr_ship_to_su_id AND
ACCT_SITE.party_site_id = PARTY_SITE.party_site_id AND
PARTY_SITE.location_id = LOC.location_id;
zx_api_pub.update_det_factors_hdr(
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 => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_hdr_det_factors_rec => l_hdr_det_factors_rec
);
arp_etax_util.delete_tax_lines_from_ar(p_customer_trx_id);
p_rows_inserted => l_rows);
and do an insert (like CMM code) if the profile is yes
and otherwise call autoaccounting */
/*GGADHAMS 5125882 Added this to check whether the CM is Regular
or On Account. If it is a On Account we call autoaccounting
else if use_inv_acct profile set to Y we copy from the Invoice
otherwise we use auotaccounting*/
select previous_customer_trx_id,
DECODE(invoicing_rule_id, NULL, 'N', 'Y')
into l_is_reg_cm,
l_rules_check_flag
from ra_customer_trx
where customer_trx_id = p_customer_trx_id;
SELECT account_set_flag
INTO l_account_set_flag
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_id = p_customer_trx_id
AND account_class = 'REC'
AND latest_rec_flag = 'Y';
/* 5211848 - Once we insert accounting distributions,
we must call arp_rounding to fix the amounts on
the REC dist to reflect the new tax */
IF arp_rounding.correct_dist_rounding_errors(
NULL,
p_customer_trx_id ,
NULL,
l_dist_count,
l_error_message ,
pg_base_precision ,
pg_base_min_acc_unit ,
'ALL' ,
l_rules_check_flag,
'N' ,
pg_trx_header_level_rounding ,
'N',
'N') = 0 -- FALSE
THEN
arp_util.debug('EXCEPTION: arp_etax_services_pkg.calculate_tax()');
| then by default the tax action is 'CREATE' else it is 'UPDATE'
|
| PARAMETERS
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 14-Apr-2005 Debbie Sue Jancis Created
|
*===========================================================================*/
FUNCTION Get_Tax_Action (p_customer_trx_id IN NUMBER) RETURN VARCHAR2 IS
l_count NUMBER;
select count(customer_trx_id)
INTO l_count
FROM ra_customer_trx_lines
where customer_Trx_id = p_customer_trx_id and
line_type = 'LINE';
l_action := 'UPDATE';
arp_etax_util.delete_tax_lines_from_ar(p_customer_trx_id);
p_rows_inserted => l_rows);
no rows were inserted */
IF l_rows > 0
THEN
/*Bug 8402096 - Calling copy_inv_tax_dists() if use_invoice_accounting is yes*/
select previous_customer_trx_id
into l_is_reg_cm
from ra_customer_trx
where customer_trx_id = p_customer_trx_id;
/* Bug 8220233 - Once we insert accounting distributions,
we must call arp_rounding to fix the amounts on
the REC dist to reflect the new tax */
IF arp_rounding.correct_dist_rounding_errors(
NULL,
p_customer_trx_id ,
NULL,
l_dist_count,
l_error_message ,
pg_base_precision ,
pg_base_min_acc_unit ,
'ALL' ,
NULL,
'N' ,
pg_trx_header_level_rounding ,
'N',
'N') = 0 -- FALSE
THEN
arp_util.debug('EXCEPTION: arp_etax_services_pkg.Override_Tax_Lines()');
| o IS_TAX_LINE_DELETE_ALLOWED
| o IS_TRX_LINE_FROZEN
|
| In general, these actions are not allowed for transactions that are
| in a complete state.
|
| PARAMETERS
| p_trx_id NUMBER (customer_trx_id of target transaction)
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 03-MAR-2005 M Raymond Created
|
*===========================================================================*/
FUNCTION is_tax_update_allowed (p_customer_trx_id IN NUMBER) RETURN BOOLEAN IS
l_update boolean;
arp_util.debug('ARP_ETAX_SERVICES_PKG.is_tax_update_allowed()+');
SELECT complete_flag
INTO l_complete_flag
FROM ra_customer_trx
WHERE customer_trx_id = p_customer_trx_id;
/* trx is complete, prevent updates */
l_update := FALSE;
arp_util.debug(' updates prevented by complete_flag');
l_update := TRUE;
arp_util.debug('ARP_ETAX_SERVICES_PKG.is_tax_update_allowed()-');
return l_update;
END is_tax_update_allowed;
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;
select t.customer_trx_id,
222,
t.org_id,
'TRANSACTIONS',
DECODE(tt.type,
'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO'),
tt.type || '_COMPLETE',
t.trx_number,
tt.type,
SUM(decode(ctl.line_type, 'LINE', 1, 0))
into
l_trx_rec.trx_id,
l_trx_rec.application_id,
l_trx_rec.internal_organization_id,
l_trx_rec.entity_code,
l_trx_rec.event_class_code,
l_trx_rec.event_type_code,
l_trx_number,
l_ttype, -- 7668830
l_line_count
from ra_customer_trx t,
ra_cust_trx_types tt,
ra_customer_trx_lines ctl
where t.customer_trx_id = p_customer_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 = ctl.customer_trx_id
group by
t.customer_trx_id,
222,
t.org_id,
'TRANSACTIONS',
DECODE(tt.type,
'INV', 'INVOICE',
'DM', 'DEBIT_MEMO',
'CM', 'CREDIT_MEMO'),
tt.type || '_COMPLETE',
t.trx_number,
tt.type,
t.customer_trx_id;
| PROCEDURE - update_exchange_info
|
| DESCRIPTION
| This routine calls etax API ZX_API_PUB.update_exchange_rate to update
| the Exchange Rate, Exchange Date and Exchange Rate Type in ZX
| repository.
|
|
| PARAMETERS
| p_customer_trx_id NUMBER (customer_trx_id of transaction)
| p_exchange_rate NUMBER IN (current Exchange Rate)
| p_exchange_date DATE IN (current Exhange Date)
| p_exchange_rate_type VARCHAR2 OUT (current Exchange Rate Type)
|
|
| MODIFICATION HISTORY
| DATE Author Description of Changes
| 09-JUL-2009 Deep Gaurab Created
|
*===========================================================================*/
PROCEDURE update_exchange_info (p_customer_trx_id IN NUMBER,
p_exchange_rate IN NUMBER,
p_exchange_date IN DATE,
p_exchange_rate_type IN VARCHAR2) IS
l_success Boolean;
arp_debug.debug('arp_etax_services_pkg.update_exchange_info (+)');
p_action => 'UPDATE',
p_event_class_code => l_event_class_code,
p_event_type_code => l_event_type_code);
arp_debug.debug('Calling ZX_API_PUB.update_exchange_rate.');
ZX_API_PUB.update_exchange_rate(
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_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_rec => l_transaction_rec,
p_curr_conv_rate => p_exchange_rate,
p_curr_conv_date => p_exchange_date,
p_curr_conv_type => p_exchange_rate_type);
arp_debug.debug('ZX_API_PUB.update_exchange_rate returned error');
arp_debug.debug('arp_etax_services_pkg.update_exchange_info (-)');
END update_exchange_info;