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

                )
  • October 31, 2017 | 19 views
  • Comments