Introduction
This script is used Queries to get AR Selected Invoices.
Query to using for AR Selected Invoices print.
/* Adjustments */
SELECT line_adjusted adj_line, tax_adjusted adj_tax,
freight_adjusted adj_freight, adj.amount adj_amount,
l1.meaning adj_type, l2.meaning adj_status,
adj.reason_code adj_reason_code, trx.trx_number adj_trx_number,
TYPES.TYPE adj_trx_type, l3.meaning adj_trx_type_name,
trx.trx_date adj_trx_date
FROM ar_adjustments_all adj
,ra_customer_trx_all trx
,ra_cust_trx_types_all TYPES
,ar_lookups l1
,ar_lookups l2
,ar_lookups l3
WHERE l1.lookup_type = ‘ADJUSTMENT_TYPE’
AND l2.lookup_type = ‘APPROVAL_TYPE’
AND l3.lookup_type = ‘INV/CM’
AND adj.status = l2.lookup_code
AND adj.TYPE = l1.lookup_code
AND trx.cust_trx_type_id = TYPES.cust_trx_type_id
AND TYPES.TYPE = l3.lookup_code
AND adj.adjustment_id = :customer_trx_id
AND adj.customer_trx_id = trx.customer_trx_id
/* Bank Details */
SELECT abb.bank_name, abb.address_line1, abb.city, NULL, abb.bank_number,
aba.bank_account_num
FROM ce_bank_accounts aba, ce_bank_branches_v abb
WHERE abb.branch_party_id = aba.bank_branch_id
AND aba.bank_account_id = :cf_bank_id;
/* Commitment Adjustment */
SELECT SUM (amount) commit_this_invoice
FROM ar_adjustments_all
WHERE adjustment_type = ‘C’
AND ( ( (customer_trx_id = :customer_trx_id)
AND (subsequent_trx_id IS NULL)
)
OR subsequent_trx_id = :customer_trx_id
)
/* Line Details */
SELECT c.customer_trx_id line_customer_trx_id,
c.customer_trx_line_id line_customer_trx_line_id,
c.memo_line_id memo_line_id,
DECODE (c2.line_number, NULL, c.line_number, NULL) line_number,
c.line_type line_type,
NVL (c.translated_description, c.description) line_item_description,
NVL (c.quantity_ordered, c.quantity_invoiced) line_qty_ordered,
NVL (c.quantity_invoiced, c.quantity_credited) line_qty_invoiced,
u.unit_of_measure line_uom,
NVL (c.unit_selling_price,
c.gross_unit_selling_price
) line_unit_selling_price,
NVL (c.extended_amount,
c.gross_extended_amount) line_extended_amount,
c.extended_amount line_net_amount, c.sales_order line_sales_order,
c.sales_order_date line_sales_order_date, c.tax_rate line_tax_rate,
c.vat_tax_id line_vat_tax_id,
c.tax_exemption_id line_tax_exemption_id,
c.sales_tax_id line_location_rate_id,
c.tax_precedence line_tax_precedence,
DECODE (TO_CHAR (c2.line_number),
NULL, ‘N’,
‘Y’
) line_is_a_child_flag,
NVL (c.link_to_cust_trx_line_id,
c.customer_trx_line_id) link_to_line,
DECODE (c.line_type, ‘LINE’, 0, 1) line_child_indicator,
NVL (c.link_to_cust_trx_line_id, -1) link_to_cust_trx_line_id,
DECODE (msi.item_type, ‘FRT’, ‘A’, ‘A’) line_of_type_frt,
DECODE (c.link_to_cust_trx_line_id,
”, c.line_number,
c2.line_number
) order_by1,
1 dummy, c.amount_includes_tax_flag line_tax_inclusive
FROM ra_customer_trx_lines_all c,
ra_customer_trx_lines_all c2,
mtl_units_of_measure_vl u,
mtl_system_items_b msi
WHERE c.customer_trx_id = :customer_trx_id
AND c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
AND c.uom_code = u.uom_code(+)
AND c.inventory_item_id = msi.inventory_item_id(+)
AND msi.organization_id(+) = :so_organization_id
ORDER BY DECODE (c2.line_number,
NULL, DECODE (c.line_type,
‘LINE’, c.line_number * 10000 + 0,
‘TAX’, c.line_number * 10000 + 8000,
100000000000
),
DECODE (c2.line_type,
‘LINE’, c2.line_number * 10000 + 0,
‘TAX’, c2.line_number * 10000 + 8000,
c2.line_number * 10000 + 9000
)
+ DECODE (c.line_type,
‘LINE’, 0,
‘TAX’, 8000,
‘FREIGHT’, 9000
)
+ c.line_number
)
/* Reg Details */
SELECT hl.address_line_1 reg_addr1, hl.address_line_2 reg_addr2,
hl.address_line_3 reg_addr3, hl.town_or_city reg_addr4,
hl.postal_code reg_addr5, hl.telephone_number_1 reg_tel,
hl.telephone_number_3 reg_fax, hl.telephone_number_3 reg_email
FROM hr_locations hl,
hr_organization_units hon,
hrfg_operating_units hou,
hr_legal_entities hle
WHERE hl.location_id = hon.location_id
AND hou.location_id = hl.location_id
AND hon.organization_id = hou.operating_units_id
AND hou.business_group_id = hle.business_group_id
AND hou.operating_units_id = :org_id1;
/* Remit Customer Details */
SELECT loc.address1 remit_address1, loc.address2 remit_address2,
loc.address3 remit_address3, loc.address4 remit_address4,
loc.city remit_city, loc.state remit_state,
loc.postal_code remit_postal_code, loc.country remit_country,
loc.province remit_province,
acct_site.cust_acct_site_id remit_address_id
FROM hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE acct_site.cust_acct_site_id = :remit_to_control_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id;
/* Shiping Details */
SELECT party.party_name ship_cust_name, loc.address1 ship_address1,
loc.address2 ship_address2, loc.address3 ship_address3,
loc.address4 ship_address4, loc.city ship_city,
NVL (loc.state, loc.province) ship_state,
loc.postal_code ship_postal_code, loc.country ship_country,
u.site_use_id ship_site_id, c.cust_account_id ship_customer_id,
u.tax_reference ship_site_tax_reference,
party.tax_reference ship_cust_tax_reference, loc.state ship_to_state,
loc.province ship_to_province
FROM hz_cust_accounts c,
hz_parties party,
hz_cust_acct_sites_all a,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all u
WHERE u.cust_acct_site_id = a.cust_acct_site_id
AND a.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND u.site_use_id = :ship_to_site_use_id
AND c.cust_account_id = :ship_to_customer_id
AND c.party_id = party.party_id
/* Invoice Summary */
SELECT c.line_type inv_tax_type, l.meaning inv_tax_type_name,
NVL (c.translated_description,
c.description
) inv_tax_line_description,
SUM (c.extended_amount) inv_tax_extended_amount,
c.tax_rate inv_tax_rate,
v.amount_includes_tax_flag inv_tax_inclusive_flag,
c.tax_exemption_id inv_tax_exemption_id,
c.sales_tax_id inv_tax_location_rate_id,
c.tax_precedence inv_tax_precedence,
SUM (c_line.extended_amount) euro_taxable_amount
FROM ra_customer_trx_lines_all c,
ar_lookups l,
ar_vat_tax_vl v,
ra_customer_trx_lines_all c_line
WHERE c.customer_trx_id = :customer_trx_id
AND c.line_type = l.lookup_code
AND l.lookup_type = ‘STD_LINE_TYPE’
AND c.vat_tax_id = v.vat_tax_id(+)
AND c_line.customer_trx_line_id(+) = c.link_to_cust_trx_line_id
GROUP BY c.line_type,
l.meaning,
NVL (c.translated_description, c.description),
c.tax_rate,
c.tax_exemption_id,
c.sales_tax_id,
v.amount_includes_tax_flag,
c.tax_precedence
ORDER BY c.tax_precedence, c.tax_rate;
What we expect in the script.
This script helps us to comprehend how Print AR Selected Invoices. Couple of tables which are being used are ra_customer_trx_lines_all c,ar_lookups l,ar_vat_tax_vl v, etc.
Summary
This Post described the script on how Print AR Selected Invoices. In Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
