Protected by Copyscape Web Copyright Protection Software

Search This Blog

Saturday, June 8, 2013

List of all Purchase Orders ( Blanket/Releases/Standard) which have India Localization taxes attached to it

SELECT distinct pha.segment1 "Po Number",
       pha.creation_date "Date",
       pha.approved_date "Approved Date",
       (pla.unit_price* pla.quantity) "Amount w/o Tax",
       jpt.tax_amount "Tax Amount",
       jpll.total_amount "Total Amount",
       pla.line_num "Line Num",
       pv.vendor_name"Vendor Name",
       jrt.tax_name"Tax Name",
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) "Po Line Status",
       hou.name "Organization Name"
  FROM po_headers_all pha,
       po_lines_all pla,
       po_line_locations_all plla,
       po_vendors pv,
       JAI_PO_LINE_LOCATIONS jpll,
       hr_operating_units hou,
       JAI_RCV_TAX_V jrt,
       jai_po_taxes jpt,
       PO_LOOKUP_CODES POLC1,
       PO_LOOKUP_CODES POLC2
 WHERE pha.po_header_id=pla.po_header_id
   AND plla.po_header_id=pha.po_header_id
   AND pv.vendor_id=pha.vendor_id
   AND plla.po_line_id=pla.po_line_id
   AND pha.segment1=:P_PONUM   --'2012102020109'
   AND jpll.po_header_id=pha.po_header_id
   AND jpll.po_line_id=plla.po_line_id
   AND pha.org_id= hou.organization_id
   AND jrt.vendor_id=pha.vendor_id
   AND jrt.tax_id= jpt.tax_id
   AND jpt.po_header_id=pha.po_header_id
   AND jpt.po_line_id=pla.po_line_id
   AND PLLA.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION')
   AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
   AND POLC1.LOOKUP_CODE(+) = NVL(PLLA.CLOSED_CODE, 'OPEN')
   AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
   AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
   AND pha.po_header_id=plla.po_header_id
--AND jrt.tax_type=jpt.tax_type
--AND jpll.tax_category_id=jpt.tax_category_id
/*GROUP BY pha.segment1,
       pha.creation_date,
       pha.approved_date,
       jpt.tax_amount ,
       jpll.total_amount,
       pla.line_num ,
       pv.vendor_name,
       jrt.tax_name,
       DECODE(NVL(PLLA.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||
       DECODE(PLLA.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL),
       hou.name*/