Tuesday, 24 October 2017

Delivery Challan with GST Details

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

No comments:

Post a Comment