ISO Receipt Register -GST Query

SELECT shipment_num, shipped_date, stnno, receipt_num,
rcvd_date, send_orgid,

send_orgname,
recv_orgid, recv_org_name, gst_no, segment1, description,

lot_num,
pri_rec_uom, uomcode, LINK, rcv_qty, iso_price, iso_price_uom,

NVL
(tax_amount, 0) tax_amount,

((rcv_qty *
iso_price) + NVL (tax_amount, 0)) stnvalue, option1, lrno,

lrdate, freight

FROM (SELECT
shipment_num, shipped_date, stnno, receipt_num, rcvd_date,

send_orgid, send_orgname, recv_orgid, recv_org_name, gst_no,

segment1, description, lot_num, pri_rec_uom, uomcode, rcv_qty,

iso_price_uom,

CASE

WHEN

–NVL(iso_price_uom, ‘NA’) <> NVL(UOMCODE, ‘NA’) AND

NVL (iso_price_uom, ‘NA’) =

NVL (uommain, ‘NA’)

THEN iso_price / LINK

ELSE
iso_price

END
iso_price,

tax_amount,

CASE

WHEN
UPPER (:opt1) = ‘SENDORG’

THEN send_orgname

WHEN
UPPER (:opt1) = ‘SHIPNO’

THEN shipment_num

WHEN
UPPER (:opt1) = ‘RCPTNO’

THEN receipt_num

WHEN
UPPER (:opt1) = ‘RCPTDATE’

THEN TO_CHAR (rcvd_date, ‘dd-mon-yyyy’)

END
option1,

lrno,
lrdate, freight, LINK

FROM (SELECT
a.shipment_num, a.receipt_num,

c.transaction_date rcvd_date,

a.ship_to_org_id recv_orgid, e.NAME recv_org_name,

i.gst_no, f.organization_id send_orgid,

f.NAME send_orgname, a.shipped_date, d.segment1,

d.description, g.primary_quantity rcvd_qty, g.lot_num,

c.quantity rcv_qty, c.unit_of_measure rec_uom,

c.primary_unit_of_measure
pri_rec_uom,

a.shipment_header_id, b.shipment_line_id,

c.transaction_id, b.requisition_line_id,

b.item_id AS inventory_item_id, h.LINK, h.uomcode,

i.tax_amount, h.uommain

FROM
rcv_shipment_headers a,

rcv_shipment_lines b,

rcv_transactions c,

(SELECT inventory_item_id, segment1,
description

FROM mtl_system_items

WHERE organization_id = 83) d,

hr_all_organization_units e,

(SELECT a.vat_reg_no tin_no, a.organization_id,

b.location_code
NAME, a.location_id

FROM jai_cmn_inventory_orgs a,

hr_locations b,

hr_all_organization_units c

— ja_in_hr_organization_units changed for R12

WHERE a.organization_id =
b.inventory_organization_id

AND b.inactive_date IS NULL

AND a.location_id = b.location_id

AND a.organization_id = c.organization_id

AND a.location_id = c.location_id) f,

rcv_lot_transactions g

(SELECT trx_id, trx_line_id,
trx_number,

first_party_primary_reg_num gst_no,

organization_id,

SUM
(rounded_tax_amt_tax_curr) tax_amount,

trx_loc_line_id

FROM jai_tax_lines_all

WHERE trx_type = ‘RECEIVE’

AND data_source = ‘BASE_TABLE’

AND entity_code = ‘RCV_TRANSACTION’

AND event_class_code = ‘RECEIVING’

AND applied_from_entity_code =

‘SALES_ORDER_ISSUE’

AND applied_from_event_class_code =

‘SALES_ORDER_ISSUE’

AND TRUNC (trx_date) BETWEEN :fdate AND :tdate

GROUP BY trx_id,

trx_line_id,

trx_number,

first_party_primary_reg_num,

organization_id,

trx_loc_line_id) i

—wsh_new_deliveries g

WHERE a.shipment_header_id =
b.shipment_header_id

AND
b.shipment_header_id = c.shipment_header_id

AND
b.shipment_line_id = c.shipment_line_id

AND
i.trx_id (+)= c.shipment_header_id

AND
i.trx_line_id (+)= c.shipment_line_id

— AND i.trx_line_id = c.shipment_line_id

— AND i.trx_number = a.receipt_num

AND
d.inventory_item_id = b.item_id

— AND i.trx_number(+) = a.receipt_num

AND
e.organization_id = a.ship_to_org_id

AND
a.organization_id = f.organization_id

AND
b.from_organization_id = f.organization_id

AND
b.to_organization_id = e.organization_id

— AND b.shipment_line_id=g.shipment_line_id

— AND c.shipment_line_id=g.shipment_line_id

AND
c.transaction_id = g.transaction_id(+)

AND
c.transaction_id = i.trx_loc_line_id(+)

AND
d.segment1 = h.appscode

AND
a.ship_to_org_id = :orgid

AND
c.transaction_type = ‘RECEIVE’

AND
TRUNC (c.transaction_date) BETWEEN :fdate AND :tdate

AND
TRUNC (c.transaction_date) >= ’04-nov-2011′

AND
a.receipt_source_code = ‘INTERNAL ORDER’

— and a.receipt_num in (‘216040’)–206397

)order
by option1

)