[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_SALES_TAX_REPORT (
chart_of_accounts_id in number,
trx_date_low in date,
trx_date_high in date,
gl_date_low in date,
gl_date_high in date,
state_low in varchar2,
state_high in varchar2,
currency_low in varchar2,
currency_high in varchar2,
exemption_status in varchar2,
lp_gltax_where in varchar2,
where_gl_flex in varchar2,
show_deposit_children in varchar2,
detail_level in varchar2,
posted_status in varchar2,
show_cms_adjs_outside_date in varchar2,
request_id in number,
user_id in number,
mesg out NOCOPY varchar2,
success out NOCOPY boolean) is
h_sob_id NUMBER;
select_statement VARCHAR2(30000);
select_trx_cols VARCHAR2(5000);
select_trx_from VARCHAR2(1000);
select_trx_where VARCHAR2(5000);
select_adj_cols VARCHAR2(5000);
select_adj_from VARCHAR2(1000);
select_adj_where VARCHAR2(5000);
select_trx_cols :=
'SELECT trx.invoice_currency_code, party.tax_reference, decode(party.party_type, ''ORGANIZATION'', party.sic_code,NULL), trx.ship_date_actual, trx.fob_point, '||
'decode(types.type,''CM'',nvl(othertrx.trx_number,''On Account''),trx.trx_number), lk.meaning, types.type, '||
'decode( types.type, ''INV'', 10, ''DM'', 15, ''CM'', 20, 30), '||
'decode( types.type, ''CM'', trx.trx_number, othertrx.trx_number), '||
'to_number(null),to_number(null),to_number(null),to_char(null),'||
'decode( types.type, ''CM'', nvl(othertrx.trx_date, trx.trx_date), trx.trx_date), '||
'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
'decode( types.type, ''INV'', ''INVOICE'',''DM'', ''INVOICE'',''CREDIT MEMO''), '||
'decode( types.type, ''CM'', nvl(othertrx.customer_trx_id,-1*trx.customer_trx_id),trx.customer_trx_id), '||
'trx.customer_trx_id, trx.batch_source_id,0, line.customer_trx_line_id , '||
'line.line_number, line.description, line.extended_amount, tax.line_number, '||
'tax.customer_trx_line_id, tax.tax_rate, vat.tax_code, '||
'line.tax_vendor_return_code, vat.tax_type, '||
'nvl(ex.customer_exemption_number,line.tax_exempt_number), '||
'nvl(lk2.meaning,lk3.meaning), '||
'decode(lk2.meaning, null, decode( lk3.meaning, null, null, 100),ex.percent_exempt), '||
'nvl(decode(tax.global_attribute_category, ''VERTEX'', '||
'nvl(tax.global_attribute2, 0) + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0), '||
' ''AVP'', nvl(tax.global_attribute2, 0) + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0), '||
'tax.extended_amount),0), '||
' tax.item_exception_rate_id, loc_assign.loc_id, '||
'loc.postal_code, tax.sales_tax_id, '||
'decode(gltax.code_combination_id, null, ''N'', ''Y''), '||
'decode(dist.gl_posted_date, null, ''Unposted'', ''Posted''), '||
'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
'line.uom_code, line.quantity_invoiced, line.unit_selling_price, '||
'line.tax_precedence, line.sales_order_source, line.sales_order, '||
'line.sales_order_revision, line.sales_order_line, line.sales_order_date, '||
'line.inventory_item_id, '||
'trx.attribute_category, trx.attribute1, trx.attribute2, '||
'trx.attribute3, trx.attribute4, trx.attribute5, '||
'trx.attribute6, trx.attribute7, trx.attribute8, '||
'trx.attribute9, trx.attribute10, trx.attribute11, '||
'trx.attribute12, trx.attribute13, trx.attribute14, '||
'trx.attribute15, '||
'line.attribute_category, line.attribute1, line.attribute2, '||
'line.attribute3, line.attribute4, line.attribute5, '||
'line.attribute6, line.attribute7, line.attribute8, '||
'line.attribute9, line.attribute10, line.attribute11, '||
'line.attribute12, line.attribute13, line.attribute14, '||
'line.attribute15, types.name ';
select_trx_where :=
'WHERE trx.previous_customer_trx_id = othertrx.customer_trx_id(+) '||
'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
'AND c.party_id = party.party_id ' ||
'AND acct_site.party_site_id = party_site.party_site_id ' ||
'AND loc.location_id = party_site.location_id ' ||
'AND loc.location_id = loc_assign.location_id ' ||
'AND upper(loc.state) between :state_low_q and :state_high_q '||
'AND loc.country = ''US'' ' ||
'AND trx.cust_trx_type_id = types.cust_trx_type_id '||
'AND types.type = lk.lookup_code '||
'AND types.type in ( ''CM'', ''INV'', ''DM'' ) '||
'AND lk.lookup_type = ''INV/CM/ADJ'' '||
'AND cy.currency_code = trx.invoice_currency_code '||
'AND c.cust_account_id = trx.bill_to_customer_id '||
'AND dist.customer_trx_id = trx.customer_trx_id '||
'AND dist.account_class = ''REC'' '||
'AND trx.customer_trx_id = line.customer_trx_id '||
'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
'AND line.line_type = ''LINE'' '||
'AND tax.line_type(+) = ''TAX'' '||
'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1) '||
'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1) '||
'AND lk2.lookup_code(+) = ex.reason_code '||
'AND lk2.lookup_type(+) = ''TAX_REASON'' '||
'AND lk3.lookup_type(+) = ''TAX_REASON'' '||
'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
'AND dist.gl_date between :gl_date_low_q and :gl_date_high_q '||
'AND dist.latest_rec_flag = ''Y'''||
'AND trx.complete_flag = ''Y'''||
'AND taxdist.customer_trx_line_id(+) = tax.customer_trx_line_id '||
'AND nvl(taxdist.code_combination_id,-1) = gltax.code_combination_id(+) ';
select_adj_cols :=
'SELECT trx.invoice_currency_code, party.tax_reference, decode(party.party_type, ''ORGANIZATION'',party.sic_code,NULL), trx.ship_date_actual, trx.fob_point, '||
'trx.trx_number, ''Adjustment'', ''ADJ'', 30, adj.adjustment_number, '||
'adj.line_adjusted, adj.tax_adjusted, adj.freight_adjusted, adj.type, adj.apply_date, '||
'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
'''ADJUSTMENT'', trx.customer_trx_id, trx.customer_trx_id, trx.batch_source_id, '||
'adj.adjustment_id, line.customer_trx_line_id, line.line_number, line.description,'||
'line.extended_amount, tax.line_number, tax.customer_trx_line_id,tax.tax_rate,'||
'vat.tax_code, tax.tax_vendor_return_code, vat.tax_type, '||
'nvl(ex.customer_exemption_number,line.tax_exempt_number),'||
'nvl(lk2.meaning,lk3.meaning), '||
'decode(lk2.meaning, null,decode( lk3.meaning, null, null, 100), ex.percent_exempt),'||
'nvl(tax.extended_amount,0), tax.item_exception_rate_id, loc_assign.location_id, '||
'loc.postal_code, tax.sales_tax_id,''Y'','||
'decode(adj.gl_posted_date, null, ''Unposted'', ''Posted'') ,'||
'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
'line.uom_code, line.quantity_invoiced, line.unit_selling_price, '||
'line.tax_precedence, line.sales_order_source, line.sales_order, '||
'line.sales_order_revision, line.sales_order_line, line.sales_order_date, '||
'line.inventory_item_id, '||
'trx.attribute_category, trx.attribute1, trx.attribute2, '||
'trx.attribute3, trx.attribute4, trx.attribute5, '||
'trx.attribute6, trx.attribute7, trx.attribute8, '||
'trx.attribute9, trx.attribute10, trx.attribute11, '||
'trx.attribute12, trx.attribute13, trx.attribute14, '||
'trx.attribute15, '||
'line.attribute_category, line.attribute1, line.attribute2, '||
'line.attribute3, line.attribute4, line.attribute5, '||
'line.attribute6, line.attribute7, line.attribute8, '||
'line.attribute9, line.attribute10, line.attribute11, '||
'line.attribute12, line.attribute13, line.attribute14, '||
'line.attribute15, to_char(null) ';
select_adj_where :=
'WHERE trx.customer_trx_id = adj.customer_trx_id '||
'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
'AND c.party_id = party.party_id ' ||
'AND acct_site.party_site_id = party_site.party_site_id ' ||
'AND loc.location_id = party_site.location_id ' ||
'AND loc.location_id = loc_assign.location_id ' ||
'AND upper(loc.state) between :state_low_q and :state_high_q '||
'AND loc.country = ''US'''||
'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
'AND c.cust_account_id = trx.bill_to_customer_id '||
'AND trx.customer_trx_id = line.customer_trx_id '||
'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
'AND cy.currency_code = trx.invoice_currency_code '||
'AND line.line_type = ''LINE'''||
'AND tax.line_type(+) = ''TAX'''||
'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1)'||
'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1)'||
'AND lk2.lookup_code(+) = ex.reason_code '||
'AND lk2.lookup_type(+) = ''TAX_REASON'''||
'AND lk3.lookup_type(+) = ''TAX_REASON'''||
'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
'AND adj.gl_date between :gl_date_low_q and :gl_date_high_q '||
'AND adj.code_combination_id = cc.code_combination_id '||
'AND cc.chart_of_accounts_id = :chart_of_accounts_id_q '||
'AND adj.chargeback_customer_trx_id is null '||
'AND adj.approved_by is not null' ;
select_trx_cols :=
'SELECT distinct trx.invoice_currency_code, party.tax_reference, decode(party.party_type, ''ORGANIZATION'', party.sic_code, NULL), trx.ship_date_actual, trx.fob_point, '||
'decode(types.type,''CM'',nvl(othertrx.trx_number,''On Account''),trx.trx_number), lk.meaning, types.type, '||
'decode( types.type, ''INV'', 10, ''DM'', 15, ''CM'', 20, 30), '||
'decode( types.type, ''CM'', trx.trx_number, othertrx.trx_number), '||
'to_number(null),to_number(null),to_number(null),to_char(null),'||
'decode( types.type, ''CM'', nvl(othertrx.trx_date, trx.trx_date), trx.trx_date), '||
'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
'decode( types.type, ''INV'', ''INVOICE'',''DM'', ''INVOICE'',''CREDIT MEMO''), '||
'decode( types.type, ''CM'', nvl(othertrx.customer_trx_id,-1*trx.customer_trx_id),trx.customer_trx_id), '||
'trx.customer_trx_id, trx.batch_source_id,0, null , '||
'null, null, '||
'null,null,null,null, '||
'null, null, null, '||
'null, '|| -- tassa oli ex
'null, '||
'null, '||
'null, null, loc_assign.location_id, '||
'loc.postal_code, null, '||
'null, '||
'decode(dist.gl_posted_date, null, ''Unposted'', ''Posted''), '||
'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
'null,null,null, '||
'null,null,null, '||
'null,null,null,null, '||
'trx.attribute_category, trx.attribute1, trx.attribute2, '||
'trx.attribute3, trx.attribute4, trx.attribute5, '||
'trx.attribute6, trx.attribute7, trx.attribute8, '||
'trx.attribute9, trx.attribute10, trx.attribute11, '||
'trx.attribute12, trx.attribute13, trx.attribute14, '||
'trx.attribute15, '||
'null,null,null,null,null,null,null,null,null,null, '||
'null,null,null,null,null,null,types.name ';
select_trx_where :=
'WHERE trx.previous_customer_trx_id = othertrx.customer_trx_id(+) '||
'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
'AND c.party_id = party.party_id ' ||
'AND acct_site.party_site_id = party_site.party_site_id ' ||
'AND loc.location_id = party_site.location_id ' ||
'AND loc.location_id = loc_assign.location_id ' ||
'AND upper(loc.state) between :state_low_q and :state_high_q '||
'AND loc.country = ''US'' ' ||
'AND trx.cust_trx_type_id = types.cust_trx_type_id '||
'AND types.type = lk.lookup_code '||
'AND types.type in ( ''CM'', ''INV'', ''DM'' ) '||
'AND lk.lookup_type = ''INV/CM/ADJ'' '||
'AND cy.currency_code = trx.invoice_currency_code '||
'AND c.cust_account_id = trx.bill_to_customer_id '||
'AND dist.customer_trx_id = trx.customer_trx_id '||
'AND dist.account_class = ''REC'' '||
'AND trx.customer_trx_id = line.customer_trx_id '||
'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
'AND line.line_type = ''LINE'' '||
'AND tax.line_type(+) = ''TAX'' '||
'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1) '||
'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1) '||
'AND lk2.lookup_code(+) = ex.reason_code '||
'AND lk2.lookup_type(+) = ''TAX_REASON'' '||
'AND lk3.lookup_type(+) = ''TAX_REASON'' '||
'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
'AND dist.gl_date between :gl_date_low_q and :gl_date_high_q '||
'AND dist.latest_rec_flag = ''Y'''||
'AND trx.complete_flag = ''Y'''||
'AND taxdist.customer_trx_line_id(+) = tax.customer_trx_line_id '||
'AND nvl(taxdist.code_combination_id,-1) = gltax.code_combination_id(+) ';
select_adj_cols :=
'SELECT distinct trx.invoice_currency_code,party.tax_reference, decode(party.party_type,''ORGANIZATION'', party.sic_code,NULL), trx.ship_date_actual, trx.fob_point, '||
'trx.trx_number, ''Adjustment'', ''ADJ'', '||
'30, '||
'adj.adjustment_number, '||
'adj.line_adjusted, adj.tax_adjusted, adj.freight_adjusted, adj.type, '||
'adj.apply_date, '||
'substrb(party.party_name,1,50), c.account_number, su.location, nvl(su.tax_code, c.tax_code), '||
'''ADJUSTMENT'', '||
'trx.customer_trx_id, '||
'trx.customer_trx_id, trx.batch_source_id, adj.adjustment_id, null,'||
'null, null, '||
'null,null,null,null, '||
'null, null, null, '||
'null, '|| -- tassa oli ex
'null, '||
'null, '||
'null, null, loc_assign.location_id, '||
'loc.postal_code, null,''Y'', '||
'decode(adj.gl_posted_date, null, ''Unposted'', ''Posted''), '||
'trx.ship_to_customer_id, trx.ship_to_site_use_id, trx.bill_to_customer_id, '||
'trx.bill_to_site_use_id, trx.sold_to_customer_id, trx.sold_to_site_use_id, '||
'trx.waybill_number, trx.purchase_order, trx.purchase_order_revision, '||
'trx.exchange_rate_type, trx.exchange_date, trx.exchange_rate, trx.ship_via, '||
'null,null,null, '||
'null,null,null, '||
'null,null,null,null, '||
'trx.attribute_category, trx.attribute1, trx.attribute2, '||
'trx.attribute3, trx.attribute4, trx.attribute5, '||
'trx.attribute6, trx.attribute7, trx.attribute8, '||
'trx.attribute9, trx.attribute10, trx.attribute11, '||
'trx.attribute12, trx.attribute13, trx.attribute14, '||
'trx.attribute15, '||
'null,null,null,null,null,null,null,null,null,null, '||
'null,null,null,null,null,null,to_char(null) ';
select_adj_where :=
'WHERE trx.customer_trx_id = adj.customer_trx_id '||
'AND nvl(trx.ship_to_site_use_id, trx.bill_to_site_use_id) = su.site_use_id '||
'AND upper(loc.state) between :state_low_q and :state_high_q '||
'AND loc.country = ''US'''||
'AND su.cust_acct_site_id = acct_site.cust_acct_site_id '||
'AND c.party_id = party.party_id ' ||
'AND acct_site.party_site_id = party_site.party_site_id ' ||
'AND loc.location_id = party_site.location_id ' ||
'AND loc.location_id = loc_assign.location_id ' ||
'AND c.cust_account_id = trx.bill_to_customer_id '||
'AND trx.customer_trx_id = line.customer_trx_id '||
'AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id(+) '||
'AND cy.currency_code = trx.invoice_currency_code '||
'AND line.line_type = ''LINE'''||
'AND tax.line_type(+) = ''TAX'''||
'AND vat.vat_tax_id(+) = nvl(tax.vat_tax_id,-1)'||
'AND ex.tax_exemption_id(+) = nvl(tax.tax_exemption_id,-1)'||
'AND lk2.lookup_code(+) = ex.reason_code '||
'AND lk2.lookup_type(+) = ''TAX_REASON'''||
'AND lk3.lookup_type(+) = ''TAX_REASON'''||
'AND lk3.lookup_code(+) = line.tax_exempt_reason_code '||
'AND adj.gl_date between :gl_date_low_q and :gl_date_high_q '||
'AND adj.code_combination_id = cc.code_combination_id '||
'AND cc.chart_of_accounts_id = :chart_of_accounts_id_q '||
'AND adj.chargeback_customer_trx_id is null '||
'AND adj.approved_by is not null' ;
select decode(state_low, null, min(location_segment_value), state_low),
decode(state_high, null, max(location_segment_value), state_high)
into h_state_low, h_state_high
from ar_location_values v, ar_system_parameters p
where v.location_segment_qualifier = 'STATE'
and v.location_structure_id = p.location_structure_id;
select min(trx_date)
into trx_date_low_1
from ra_customer_trx;
select nvl(min(gl_date), trx_date_low_1)
into trx_date_low_2
from ar_adjustments;
select max(trx_date)
into trx_date_high_1
from ra_customer_trx;
select nvl(max(gl_date), trx_date_high_1)
into trx_date_high_2
from ar_adjustments;
select min(gl_date)
into gl_date_low_1
from ra_cust_trx_line_gl_dist;
select nvl(min(gl_date), gl_date_low_1)
into gl_date_low_2
from ar_adjustments;
select max(gl_date)
into gl_date_high_1
from ra_cust_trx_line_gl_dist;
select nvl(max(gl_date), gl_date_high_1)
into gl_date_high_2
from ar_adjustments;
select_trx_from :=
'FROM fnd_currencies cy, ra_cust_trx_types types, ar_lookups lk, hz_cust_accounts c, hz_parties party '||
'ar_vat_tax vat, ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, '||
'ra_customer_trx_lines line, ra_customer_trx_lines tax, hz_cust_acct_sites acct_site, '||
'hz_party_sites party_site, hz_loc_assignments loc_assign, hz_locations loc, ' ||
'hz_cust_site_uses su, ra_customer_trx othertrx, ra_cust_trx_line_gl_dist taxdist, '||
'gl_code_combinations gltax, ra_cust_trx_line_gl_dist dist, '||
'ra_customer_trx trx ';
select_adj_from :=
'FROM gl_code_combinations cc, ar_adjustments adj, hz_cust_accounts c, hz_parties party, ar_vat_tax vat, '||
'ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, fnd_currencies cy, '||
'ra_customer_trx_lines line, ra_customer_trx_lines tax, ra_customer_trx trx, '||
'hz_cust_site_uses su, hz_cust_acct_sites acct_site, ' ||
'hz_locations loc, hz_loc_assignments loc_assign, hz_party_sites party_site ';
select_trx_from :=
'FROM fnd_currencies cy, ra_cust_trx_types types, ar_lookups lk, hz_cust_accounts c, hz_parties party, '||
'ar_vat_tax vat, ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, '||
'ra_customer_trx_lines line, ra_customer_trx_lines tax, ' ||
' hz_cust_acct_sites acct_site, hz_locations loc, hz_loc_assignments loc_assign, hz_party_sites party_site, '||
'hz_cust_site_uses su, ra_customer_trx othertrx, ra_cust_trx_line_gl_dist taxdist, '||
'gl_code_combinations gltax, '||
'ra_customer_trx trx,ra_cust_trx_line_gl_dist dist ';
select_adj_from :=
'FROM hz_cust_accounts c, hz_parties party, ar_vat_tax vat, '||
'ra_tax_exemptions ex, ar_lookups lk2, ar_lookups lk3, fnd_currencies cy, '||
'ra_customer_trx_lines line, ra_customer_trx_lines tax, ra_customer_trx trx, '||
'hz_cust_site_uses su, hz_cust_acct_sites acct_site, hz_party_sites party_site, ' ||
'hz_locations loc, hz_loc_assignments loc_assign, gl_code_combinations cc, ar_adjustments adj ';
select_statement := select_trx_cols ||
select_trx_from ||
select_trx_where ||
trx_date_range ||
where_exemption_status ||
where_currency ||
where_trx_flex||
gl_posted_status ||
union_d ||
select_adj_cols ||
select_adj_from ||
select_adj_where ||
trx_date_range_adj ||
where_exemption_status ||
where_currency||
where_adj_flex ||
gl_posted_status_adj ;
select fcr.last_update_login into h_login_id
from fnd_concurrent_requests fcr
where fcr.request_id = h_request_id;
select name, currency_code into h_sob_name, h_base_currency
from gl_sets_of_books
where set_of_books_id = h_sob_id;
select_statement,
DBMS_SQL.V7);
insert into ar_sales_tax_rep_itf
(request_id, sob_name, base_currency, posting_status, ship_to_state,
ship_to_county, ship_to_province, ship_to_city, ship_to_postal_code,
ship_to_customer_name, ship_to_customer_number, ship_to_customer_type,ship_to_address1,
ship_to_address2, ship_to_address3, ship_to_address4,
bill_to_state,
bill_to_county, bill_to_province, bill_to_city, bill_to_postal_code,
bill_to_customer_name, bill_to_customer_number, bill_to_customer_type, bill_to_address1,
bill_to_address2, bill_to_address3, bill_to_address4,
sold_to_state,
sold_to_county, sold_to_province, sold_to_city, sold_to_postal_code,
sold_to_customer_name, sold_to_customer_number, sold_to_customer_type, sold_to_address1,
sold_to_address2, sold_to_address3, sold_to_address4,
invoice_number,class, adjustment_number, inv_or_adj_date, line_number,
description, line_amount, tax_line_number, sic_code, invoice_currency_code,
tax_rate, tax_code, exempt_number, exempt_reason, tax_amount ,
ship_date_actual, fob_point, tax_reference,
waybill_number, purchase_order, purchase_order_revision, exchange_rate_type,
exchange_date, exchange_rate, ship_via, transaction_type,
uom, quantity_invoiced , unit_selling_price,
tax_precedence, sales_order_source, sales_order,
sales_order_revision, sales_order_line, sales_order_date, footnote,
inventory_item, item_description, total_lines_amount, total_tax_amount,
exempt_amount,taxable_amount,
HEADER_CATEGORY, HEADER_ATTRIBUTE1, HEADER_ATTRIBUTE2,
HEADER_ATTRIBUTE3, HEADER_ATTRIBUTE4, HEADER_ATTRIBUTE5,
HEADER_ATTRIBUTE6, HEADER_ATTRIBUTE7, HEADER_ATTRIBUTE8,
HEADER_ATTRIBUTE9, HEADER_ATTRIBUTE10, HEADER_ATTRIBUTE11,
HEADER_ATTRIBUTE12, HEADER_ATTRIBUTE13,
HEADER_ATTRIBUTE14, HEADER_ATTRIBUTE15,
LINE_CATEGORY, LINE_ATTRIBUTE1, LINE_ATTRIBUTE2,
LINE_ATTRIBUTE3, LINE_ATTRIBUTE4, LINE_ATTRIBUTE5,
LINE_ATTRIBUTE6, LINE_ATTRIBUTE7, LINE_ATTRIBUTE8,
LINE_ATTRIBUTE9, LINE_ATTRIBUTE10, LINE_ATTRIBUTE11,
LINE_ATTRIBUTE12, LINE_ATTRIBUTE13, LINE_ATTRIBUTE14,
LINE_ATTRIBUTE15,
last_updated_by,last_update_login, created_by, creation_date, last_update_date)
values
(h_request_id, h_sob_name, h_base_currency, c_posted, c_ship_to_state,
c_ship_to_county, c_ship_to_province, c_ship_to_city, c_ship_to_postal_code,
c_ship_to_cust_name, c_ship_to_cust_number, c_ship_to_customer_type, c_ship_to_address1,
c_ship_to_address2, c_ship_to_address3, c_ship_to_address4,
c_bill_to_state,
c_bill_to_county, c_bill_to_province, c_bill_to_city, c_bill_to_postal_code,
c_bill_to_cust_name, c_bill_to_cust_number, c_bill_to_customer_type, c_bill_to_address1,
c_bill_to_address2, c_bill_to_address3, c_bill_to_address4,
c_sold_to_state,
c_sold_to_county, c_sold_to_province, c_sold_to_city, c_sold_to_postal_code,
c_sold_to_cust_name, c_sold_to_cust_number, c_sold_to_customer_type, c_sold_to_address1,
c_sold_to_address2, c_sold_to_address3, c_sold_to_address4,
c_inv_number, c_inv_type, c_adj_number, c_inv_date,
c_line_number, c_description, h_line_amount, c_tax_line_number, c_sic_code, c_currency,
c_tax_rate, c_vat_code, c_exempt_number, c_exempt_reason, h_tax_amount,
c_ship_date_actual, c_fob_point, c_tax_reference,
c_waybill_number, c_purchase_order, c_purchase_order_revision, h_exchange_rate_type,
c_exchange_rate_date, c_exchange_rate, c_ship_via, c_type_name,
c_uom_code, c_quantity_invoiced , c_unit_selling_price,
c_tax_precedence, c_sales_order_source, c_sales_order,
c_sales_order_revision, c_sales_order_line, c_sales_order_date, c_comment,
h_inventory_item, h_item_description, h_total_lines_amount, h_total_tax_amount,
h_exemption_amount, h_taxable_amount,
c_header_category, c_header_attr1,
c_header_attr2, c_header_attr3, c_header_attr4,
c_header_attr5, c_header_attr6, c_header_attr7,
c_header_attr8, c_header_attr9, c_header_attr10,
c_header_attr11, c_header_attr12, c_header_attr13,
c_header_attr14, c_header_attr15, c_line_category,
c_line_attr1, c_line_attr2, c_line_attr3,
c_line_attr4, c_line_attr5, c_line_attr6,
c_line_attr7, c_line_attr8, c_line_attr9,
c_line_attr10, c_line_attr11, c_line_attr12,
c_line_attr13, c_line_attr14, c_line_attr15,
h_login_id, h_login_id, h_login_id, sysdate, sysdate);
fc_text => 'Concurrent request completed successfully, '||to_char(c_counter)||' row(s) inserted.',
fc_buffer => in_mesg);
end INSERT_SALES_TAX_REPORT;
ARRX_SALES_TAX_REP.insert_sales_tax_report (
chart_of_accounts_id => chart_of_accounts_id,
trx_date_low => trx_date_low,
trx_date_high => trx_date_high,
gl_date_low => gl_date_low,
gl_date_high => gl_date_high,
state_low => state_low,
state_high => state_high,
currency_low => currency_low,
currency_high => currency_high,
exemption_status => exemption_status,
lp_gltax_where => lp_gltax_where,
where_gl_flex => where_gl_flex,
show_deposit_children => show_deposit_children,
detail_level => detail_level,
posted_status => posted_status,
show_cms_adjs_outside_date => show_cms_adjs_outside_date,
request_id => request_id,
user_id => user_id,
mesg => errbuf,
success => h_success);
select sum(abs(decode(l.line_type, 'LINE', l.extended_amount, 0))),
sum(abs(decode(l.line_type, 'TAX', decode(l.global_attribute_category, 'VERTEX',
nvl(l.global_attribute2, 0) + nvl(l.global_attribute4, 0) + nvl(l.global_attribute6, 0),
'AVP', nvl(l.global_attribute2, 0) + nvl(l.global_attribute4, 0) + nvl(l.global_attribute6, 0),
l.extended_amount), 0))),
sum(abs(decode(l.line_type, 'FREIGHT', l.extended_amount, 0))),
sum(decode(l.line_type, 'LINE', 1, 0)),
sum(decode(l.line_type, 'TAX', 1, 0)),
sum(decode(l.line_type, 'FREIGHT', 1, 0))
from ra_customer_trx_lines l
where customer_trx_id = fc_cust_trx_id ;
select sum(decode(line_type, 'TAX', decode(global_attribute_category, 'VERTEX',
nvl(global_attribute2, 0) + nvl(global_attribute4, 0) + nvl(global_attribute6, 0),
'AVP', nvl(global_attribute2, 0) + nvl(global_attribute4, 0) + nvl(global_attribute6, 0),
extended_amount),0)),
sum(decode(line_type, 'LINE', extended_amount,0))
from ra_customer_trx_lines
where customer_trx_id = fc_cust_trx_id
and line_type in ( 'LINE', 'TAX');
select sum(trx.extended_amount)
from ra_customer_trx_lines trx
where trx.customer_trx_id = fc_cust_trx_id
and trx.line_type = 'LINE'
and trx.customer_trx_line_id in
( select tax.link_to_cust_trx_line_id
from ra_customer_trx_lines tax, ra_tax_exemptions ex
where tax.customer_trx_id = fc_cust_trx_id
and tax.line_type = 'TAX'
and tax.tax_exemption_id = ex.tax_exemption_id
and ex.status = fc_exemption_status );
select sum(decode(tax.global_attribute_category, 'VERTEX', nvl(tax.global_attribute2, 0)
+ nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0), 'AVP',
nvl(tax.global_attribute2, 0) + nvl(tax.global_attribute4, 0) + nvl(tax.global_attribute6, 0),
tax.extended_amount))
from ra_customer_trx_lines tax, ra_tax_exemptions ex
where tax.customer_trx_id = fc_cust_trx_id
and tax.line_type = 'TAX'
and tax.tax_exemption_id = ex.tax_exemption_id
and ex.status = fc_exemption_status ;
SELECT substrb(party.party_name,1,50),
c.account_number,
decode(c.customer_type,'I','Internal','R','External'),
loc.address1, loc.address2, loc.address3,
loc.address4, loc.city , loc.postal_code,
loc.state, loc.province,loc.county
from
ra_customer_trx trx, hz_cust_accounts c,
hz_parties party,
hz_cust_site_uses su, hz_cust_acct_sites acct_site,
hz_locations loc, hz_party_sites party_site
where c.cust_account_id = fc_customer_id_in
AND su.site_use_id = fc_site_use_id
AND c.party_id = party.party_id
AND su.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND trx.customer_trx_id = fc_customer_trx_id;
select min(decode(taxdist.code_combination_id,null,'N','Y'))
into warn_gltax_range
from ra_cust_trx_line_gl_dist taxdist, gl_code_combinations cc
where customer_trx_line_id = c_trx_id
AND taxdist.code_combination_id = cc.code_combination_id;
select min(decode(taxdist.code_combination_id,null,'N','Y'))
into warn_gltax_range
from ra_cust_trx_line_gl_dist taxdist, gl_code_combinations cc,
ra_customer_trx trx, ra_customer_trx_lines tax
where taxdist.customer_trx_line_id = tax.customer_trx_line_id
AND tax.line_type = 'TAX'
AND trx.customer_trx_id = tax.customer_trx_id
AND trx.customer_trx_id = c_trx_id
AND taxdist.code_combination_id = cc.code_combination_id;
select user_conversion_type into rate_type
from gl_daily_conversion_types
where conversion_type = c_exchange_rate_type;
select description into item_description
from mtl_system_items
where inventory_item_id = fc_inventory_item_id
and organization_id = fc_organization_id;
select segment1, segment2, segment3, segment4, segment5, segment6, segment7, segment8,
segment9, segment10, segment11, segment12, segment13, segment14, segment15, segment16,
segment17, segment18, segment19, segment20
into s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20
from mtl_system_items
where inventory_item_id = fc_inventory_item_id
and organization_id = fc_organization_id;
select min(customer_trx_line_id) into min_tax_line_id
from ra_customer_trx_lines
where link_to_cust_trx_line_id = fc_trx_line_id
and line_type = 'TAX';
select count(*) into cnt_tax_lines from ra_customer_trx_lines
where link_to_cust_trx_line_id = fc_trx_line_id
and line_type = 'TAX';
select count(*) into cnt_inv_lines from ra_customer_trx_lines
where customer_trx_id = f_trx_id
and line_type = 'LINE';
SELECT customer_trx_line_id from ra_customer_trx_lines
WHERE customer_trx_id = fn_trx_id
AND line_type = 'LINE'
AND line_number = fn_cnt_lines;
SELECT customer_trx_line_id, extended_amount from ra_customer_trx_lines
WHERE customer_trx_id = fg_trx_id
AND line_type = 'LINE';
SELECT customer_trx_line_id, tax_exemption_id, tax_rate from ra_customer_trx_lines
WHERE link_to_cust_trx_line_id = cus_trx_line_id
AND line_type = 'TAX';
SELECT percent_exempt from ra_tax_exemptions
WHERE tax_exemption_id = l_tax_exemption_id;
select count(*) into cnt_tax_lines from ra_customer_trx_lines
where link_to_cust_trx_line_id = cus_trx_line_id
and line_type = 'TAX';
select nvl(precision,0), minimum_accountable_unit
into c_precision, c_mau
from fnd_currencies
where currency_code = fc_currency;