Introduction
This Post is about to Sales Order details with India Localization tax in Oracle EBS R12.
Script to Sales Order details with India Localization tax
SELECT h.header_id header_id, party.party_name sold_to,
h.order_number order_number,
NVL (TO_CHAR (h.ordered_date, ‘DD-MON-YYYY’),
TO_CHAR (h.request_date, ‘DD-MON-YYYY’)
) ordered_date,
(SELECT TO_CHAR (MAX (oola.schedule_ship_date),
‘DD-MON-YYYY’
)
FROM oe_order_lines_all oola
WHERE oola.header_id = h.header_id) dispatch_date,
bill_su.LOCATION invoice_to_location,
bill_loc.address1 invoice_to_address1,
bill_loc.address2 invoice_to_address2,
bill_loc.address3 invoice_to_address3,
bill_loc.address4 invoice_to_address4,
DECODE (bill_loc.city,
NULL, NULL,
bill_loc.city || ‘, ‘
)
|| DECODE (bill_loc.state,
NULL, bill_loc.province || ‘, ‘,
bill_loc.state || ‘, ‘
)
|| DECODE (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ‘, ‘
)
|| DECODE (ft.territory_short_name,
NULL, NULL,
ft.territory_short_name || ‘.’
) invoice_to_address5,
ship_su.LOCATION ship_to_location, ship_loc.address1 ship_to_address1,
ship_loc.address2 ship_to_address2, ship_loc.address3 ship_to_address3,
ship_loc.address4 ship_to_address4,
DECODE (ship_loc.city,
NULL, NULL,
ship_loc.city || ‘, ‘
)
|| DECODE (ship_loc.state,
NULL, ship_loc.province || ‘, ‘,
ship_loc.state || ‘, ‘
)
|| DECODE (ship_loc.postal_code,
NULL, NULL,
ship_loc.postal_code || ‘, ‘
)
|| DECODE (ft.territory_short_name,
NULL, NULL,
ft.territory_short_name || ‘.’
) ship_to_address5,
h.cust_po_number reference_number, h.attribute1 transporter_name,
term.NAME terms, cust_acct.cust_account_id cust_account_id,
l.line_number line_no,
(SELECT NVL (l1.user_item_description, msi.description)
FROM oe_order_lines_all l1, mtl_system_items_b msi
WHERE l1.inventory_item_id = msi.inventory_item_id
AND l1.ship_from_org_id = msi.organization_id
AND l1.header_id = l.header_id
AND l1.line_id = l.line_id
AND l1.inventory_item_id = l.inventory_item_id) particulars,
l.unit_selling_price unit_selling_price,
l.ordered_quantity ordered_quantity,
— l.order_quantity_uom order_quantity_uom,
(SELECT primary_unit_of_measure
FROM oe_order_lines_all l2, mtl_system_items_b msi1
WHERE l2.inventory_item_id = msi1.inventory_item_id
AND l2.ship_from_org_id = msi1.organization_id
AND l2.header_id = l.header_id
AND l2.line_id = l.line_id
AND l2.inventory_item_id = l.inventory_item_id) order_quantity_uom,
(l.ordered_quantity * l.unit_selling_price) line_amount,
NVL (jcta.tax_rate, 0) tax_rate,
(SELECT NVL (SUM ( ABS (NVL (amount_due_remaining, 0))
+ ABS (NVL (tax_remaining, 0))
),
0
)
FROM ar_payment_schedules_all apsa1
WHERE 1 = 1
AND apsa1.CLASS IN (‘PMT’)
AND apsa1.customer_id = h.sold_to_org_id) customer_balance,
h.attribute2 remarks, fu.user_name prepared_by,
l.ordered_item item_code, jcca.pan_no pan_no,
jcca.cst_reg_no cst_reg_no, jcca.vat_reg_no vat_reg_no,
jcca.service_tax_regno service_tax_regno
FROM hz_parties party,
hz_cust_accounts cust_acct,
oe_order_headers_all h,
oe_order_lines_all l,
hz_cust_site_uses_all bill_su,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_cas,
hz_party_sites bill_ps,
hz_cust_site_uses_all ship_su,
hz_party_sites ship_ps,
hz_locations ship_loc,
hz_cust_acct_sites_all ship_cas,
ra_terms_tl term,
jai_om_oe_so_taxes jst,
jai_cmn_taxes_all jcta,
fnd_user fu,
jai_cmn_cus_addresses jcca,
fnd_territories_vl ft
WHERE 1 = 1
AND h.header_id = l.header_id
AND h.org_id = l.org_id
AND cust_acct.party_id = party.party_id
AND h.sold_to_org_id = cust_acct.cust_account_id
AND h.invoice_to_org_id = bill_su.site_use_id(+)
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
AND bill_cas.party_site_id = bill_ps.party_site_id(+)
AND bill_loc.location_id(+) = bill_ps.location_id
AND h.ship_to_org_id = ship_su.site_use_id(+)
AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
AND ship_cas.party_site_id = ship_ps.party_site_id(+)
AND ship_loc.location_id(+) = ship_ps.location_id
AND h.payment_term_id = term.term_id(+)
AND term.LANGUAGE(+) = USERENV (‘LANG’)
AND l.header_id = jst.header_id(+)
AND l.line_id = jst.line_id(+)
AND h.created_by = fu.user_id
AND jst.tax_id = jcta.tax_id(+)
AND jcta.tax_type IN (‘CST’, ‘VALUE ADDED TAX’)
AND cust_acct.cust_account_id = jcca.customer_id
AND ship_su.cust_acct_site_id = jcca.address_id
AND ship_su.site_use_code = ‘SHIP_TO’
AND h.flow_status_code IN (‘BOOKED’)
AND bill_loc.country = ft.territory_code(+)
AND h.order_number = :p_sales_order
Sales Order details with India Localization tax
What we expect in the script.
This script helps us to comprehend how Sales Order details with India Localization tax Couple of tables which is being used are-hz_parties,hz_cust_accounts,oe_order_headers_all,oe_order_lines_all,hz_cust_site_uses_all,hz_locations bill_loc,hz_cust_acct_sites_all,hz_party_sites etc.
Summary
This Post described the script Sales Order details with India Localization tax 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.
