Introduction
This Post is Described about the Delivery Challan with GST Details in ebs r12
Query retrieves data of those inventory items which are used for promotional purposes or given as a sample items for a sales executive in order to promote the products along with the GST Details(GSTIN Number, etc…)
SELECT dcno, dcdate, description, to_organization, transport, lrno, lrdate, shippers, subinventory_code, qty,attribute10,
transaction_uom, transaction_cost,actual_cost, appscode, item_description, lot_number, mfgdt, expdt, to_org, to_add1, to_add2,
to_add3, to_city, to_pin, to_state, to_dl1, to_dl2, fm_org, fm_add1, fm_add2, fm_add3, fm_city, fm_pin, fm_state,
fm_dl1, fm_dl2, fm_lst_no, fm_cst_no, to_lst_no, to_cst_no,(actual_cost* qty) value,
(select registration_number from jai_party_regs a, jai_party_reg_lines b, hr_locations c
where 1=1
and a.party_reg_id = b.party_reg_id
and a.party_type_code = ‘IO’
and b.regime_id = 10000
and b.registration_type_code = ‘GST’
and a.party_id = c.inventory_organization_id
and a.party_site_id = c.location_id
and c.inventory_organization_id = :orgid)gst_in,hsn_code
from
(select a.header_id as dcno, trunc(b.transaction_date) as dcdate, a.description,
a.attribute1 as to_organization, a.attribute15 as transport, substr(a.attribute2,1,15) as lrno,
substr(a.attribute3,1,15) as lrdate, nvl(to_number(attribute4),0) as shippers,
b.subinventory_code, (d.transaction_quantity * -1) as qty, b.transaction_uom,b.transaction_cost, b.actual_cost,a.attribute10,
c.segment1 as appscode, c.description as item_description,
e.lot_number, (e.expiration_date – c.shelf_life_days) as mfgdt, e.expiration_date as expdt,
f.location_code as to_org, f.address_line_1 as to_add1, f.address_line_2 as to_add2, f.address_line_3 as to_add3,
f.town_or_city as to_city, f.postal_code as to_pin, f.region_1 as to_state,
f.loc_information14 as to_dl1, f.loc_information16 as to_dl2,
g.location_code as fm_org, g.address_line_1 as fm_add1, g.address_line_2 as fm_add2, g.address_line_3 as fm_add3,
g.town_or_city as fm_city, g.postal_code as fm_pin, g.region_1 as fm_state,
g.loc_information14 as fm_dl1, g.loc_information16 as fm_dl2,
h.st_reg_no as fm_lst_no, h.cst_reg_no as fm_cst_no,
to_lst_no, to_cst_no,
(SELECT SUBSTR (REPLACE(f.template_name,’ ‘,”),4,15) FROM jai_item_templ_hdr e,
jai_item_templ_hdr f
WHERE 1=1
AND e.inventory_item_id = b.inventory_item_id
AND e.entity_id = f.template_hdr_id
AND e.entity_type_code = ‘ITEM_TEMPL_ASGN’
AND e.organization_id = :orgid
AND ROWNUM =1)hsn_code
from mtl_txn_request_headers a,
(select transaction_id, transaction_source_id, inventory_item_id, subinventory_code, transaction_date,
transaction_quantity, transaction_uom, transaction_cost,actual_cost,attribute10
from mtl_material_transactions
where organization_id = :orgid
and transaction_type_id = 63) b,
(select segment1, description, inventory_item_id, shelf_life_days from mtl_system_items
where organization_id = :orgid) c,
(select transaction_id, transaction_source_id, inventory_item_id, lot_number, transaction_quantity
from mtl_transaction_lot_numbers where organization_id = :orgid) d,
(select inventory_item_id, lot_number, expiration_date
from mtl_lot_numbers
where organization_id = :orgid) e,
(select location_id, location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16
from hr_locations) f,
(select location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16, inventory_organization_id
from hr_locations) g,
(select organization_id, st_reg_no, cst_reg_no
from jai_cmn_inventory_orgs
where location_id = 0) h,
(select location_id, organization_id, st_reg_no as to_lst_no, cst_reg_no as to_cst_no
from jai_cmn_inventory_orgs ) i
where a.organization_id = :orgid
and a.header_id between :fdcno and :tdcno
and a.transaction_type_id = 63
and a.header_id = b.transaction_source_id
and b.inventory_item_id = c.inventory_item_id
and d.transaction_id = b.transaction_id
and d.transaction_source_id = a.header_id
and d.inventory_item_id = e.inventory_item_id
and d.lot_number = e.lot_number
and ltrim(a.attribute1) = ltrim(f.location_code)
and a.organization_id = g.inventory_organization_id
and h.organization_id = a.organization_id
and f.location_id = i.location_id
union
select a.header_id as dcno, b.transaction_date as dcdate, a.description,
a.attribute1 as to_organization, a.attribute15 as transport, a.attribute2 as lrno, a.attribute3 as lrdate, to_number(a.attribute4) as shippers,
b.subinventory_code, (d.transaction_quantity * -1) as qty, b.transaction_uom, b.transaction_cost,b.actual_cost,a.attribute10,
c.segment1 as appscode, c.description as item_description,
e.lot_number, (e.expiration_date – c.shelf_life_days) as mfgdt, e.expiration_date as expdt,
f.location_code as to_org, f.address_line_1 as to_add1, f.address_line_2 as to_add2, f.address_line_3 as to_add3,
f.town_or_city as to_city, f.postal_code as to_pin, f.region_1 as to_state,
f.loc_information14 as to_dl1, f.loc_information16 as to_dl2,
g.location_code as fm_org, g.address_line_1 as fm_add1, g.address_line_2 as fm_add2, g.address_line_3 as fm_add3,
g.town_or_city as fm_city, g.postal_code as fm_pin, g.region_1 as fm_state,
g.loc_information14 as fm_dl1, g.loc_information16 as fm_dl2,
h.st_reg_no as fm_lst_no, h.cst_reg_no as fm_cst_no,
‘ ‘ to_lst_no, ‘ ‘ to_cst_no,
(SELECT SUBSTR (REPLACE(f.template_name,’ ‘,”),4,15) FROM jai_item_templ_hdr e,
jai_item_templ_hdr f
WHERE 1=1
AND e.inventory_item_id = b.inventory_item_id
AND e.entity_id = f.template_hdr_id
AND e.entity_type_code = ‘ITEM_TEMPL_ASGN’
AND e.organization_id = :orgid
AND ROWNUM =1)hsn_code
from mtl_txn_request_headers a,
(select transaction_id, transaction_source_id, inventory_item_id, subinventory_code, transaction_date,
transaction_quantity, transaction_uom, transaction_cost,actual_cost,attribute10
from mtl_material_transactions
where organization_id = :orgid
and transaction_type_id = 63) b,
(select segment1, description, inventory_item_id, shelf_life_days from mtl_system_items
where organization_id = :orgid) c,
(select transaction_id, transaction_source_id, inventory_item_id, lot_number, transaction_quantity
from mtl_transaction_lot_numbers where organization_id = :orgid) d,
(select inventory_item_id, lot_number, expiration_date
from mtl_lot_numbers
where organization_id = :orgid) e,
(select location_id, location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16, inventory_organization_id, object_version_number
from hr_locations) f,
(select location_code, address_line_1, address_line_2, address_line_3, town_or_city, postal_code, region_1,
loc_information14, loc_information16, inventory_organization_id
from hr_locations) g,
(select organization_id, st_reg_no, cst_reg_no
from jai_cmn_inventory_orgs
where location_id = 0) h
where a.organization_id = :orgid
and a.header_id between :fdcno and :tdcno
and a.transaction_type_id = 63
and a.header_id = b.transaction_source_id
and b.inventory_item_id = c.inventory_item_id
and d.transaction_id = b.transaction_id
and d.transaction_source_id = a.header_id
and d.inventory_item_id = e.inventory_item_id
and d.lot_number = e.lot_number
and ltrim(a.attribute1) = ltrim(f.location_code)
and a.organization_id = g.inventory_organization_id
and h.organization_id = a.organization_id
and f.inventory_organization_id is null)
order by dcno
Got any queries?
Do drop a note by writing us at Doyen.ebiz@staging.doyensys.com or use the comment section below to ask your questions.
