Sunday, 19 June 2011

Query for PO with receipts with the applied taxes in 11i

SELECT   pv.vendor_name "Supplier"            ,
         pvsa.vendor_site_code "Supplier site",
         pha.segment1 "PO No"                 , (
         CASE
                  WHEN pha.type_lookup_code = 'BLANKET'
                  THEN
                           (SELECT release_num
                           FROM    po_releases_all pra
                           WHERE   pra.po_release_id = plla.po_release_id
                           )
                  ELSE NULL
         END) "Release No"                                                            ,
         pla.line_num "PO Line No"                                                    ,
         rsh.receipt_num "Receipt NO"                                                 ,
         rsl.line_num "Receipt Line No"                                               ,
         msib.segment1 Item                                                           ,
         rsl.unit_of_measure "Unit Of Measure"                                        ,
         rsl.quantity_received "Receive Qty"                                          ,
         rsh.attribute_category "Receipt Category"                                    ,
         rsh.attribute1 "Excise Invoice No"                                           ,
         TRUNC(to_date(rsh.attribute2,'yyyy/mm/dd HH24:MI:SS')) "Excise Invoice date" ,
         jrtl.currency Currency                                                       ,
         jrtl.tax_name "Tax Name"                                                     ,
         jrtl.tax_rate "Tax Rate"                                                     ,
         jrtl.tax_amount "Tax Amount"                                                 ,
         gcc.concatenated_segments "Code combination"
FROM     rcv_shipment_headers rsh        ,
         rcv_shipment_lines rsl          ,
         ja.ja_in_receipt_tax_lines jrtl ,
         po_headers_all pha              ,
         po_lines_all pla                ,
         po_line_locations_all plla      ,
         mtl_system_items_b msib         ,
         ja.JA_IN_RCV_JOURNALS_B jrj     ,
         gl_code_combinations_kfv gcc    ,
         po_vendors pv                   ,
         po_vendor_sites_all pvsa
WHERE    1                       =1
AND      pv.vendor_id            =pvsa.vendor_id
AND      pv.vendor_id            =pha.vendor_id
AND      pvsa.vendor_site_id     =pha.vendor_site_id
AND      rsh.shipment_header_id  = rsl.shipment_header_id
AND      jrtl.shipment_header_id = rsh.shipment_header_id
AND      rsl.shipment_line_id    = jrtl.shipment_line_id
AND      pha.org_id              = pla.org_id
AND      plla.org_id             = pla.org_id
AND      pha.org_id              = &OU_ID
AND      pha.po_header_id        = pla.po_header_id
AND      pha.po_header_id        = rsl.po_header_id
AND      pla.po_header_id        = plla.po_header_id
AND      pla.po_line_id          = plla.po_line_id
AND      pla.po_line_id          = rsl.po_line_id
AND      msib.inventory_item_id  = pla.item_id
AND      msib.inventory_item_id  =rsl.item_id
AND      msib.organization_id    = rsh.ship_to_org_id
AND      rsl.po_line_location_id =plla.line_location_id
AND      rsh.ship_to_org_id      = &INV_ID
AND      plla.quantity_received  >0
AND      TRUNC(rsh.creation_date) BETWEEN to_date('&P_S_DATE','DD-Mon-YYYY') AND to_date('&P_E_DATE','DD-Mon-YYYY')
AND      jrj.receipt_num        =rsh.receipt_num
AND      jrj.shipment_line_id   =rsl.shipment_line_id
AND      jrj.entered_dr         =jrtl.tax_amount
AND      jrj.code_combination_id=gcc.code_combination_id
ORDER BY 1,2,3,4,5,6