APPS.PO_TAX_SUMMARY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

modified the select statements for the object_type 'PO' and 'REL' for all the
object_location. Accordingly, changed the where condition of these select
statement to take in to consideration of only those shipments which are not
fully cancelled */
/* Bug# 2748176 : Added the condition
             AND  nvl(cancel_flag, 'N') <> 'Y'
             AND  nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED'
   for Reqs to remove tax detail lines which have beed Cancelled.  */

-- SERVICES FPJ : Added decodes to handle amounts for service lines

    if (object_type = 'PO') then
       if (object_location = 'HEADER') then
           SELECT nvl(SUM(nvl(pod.recoverable_tax, 0) *
                  decode(pol.order_type_lookup_code, 'RATE',        -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   'FIXED PRICE',
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_recoverable_tax
           FROM   po_distributions pod,
                  po_lines pol,
                  po_line_locations pll
           WHERE  pod.po_header_id = X_header_id
            AND   pll.po_line_id = pol.po_line_id
            AND   pll.line_location_id = pod.line_location_id
            AND   pod.po_release_id is null    -- Bug 3532747
            AND   (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0) <>  0 OR
                  nvl(pll.amount,0) - nvl(pll.amount_cancelled,0) <> 0)
            AND   nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 41

           SELECT nvl(SUM(nvl(pod.recoverable_tax, 0) *
                  decode(pol.order_type_lookup_code, 'RATE',    -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   'FIXED PRICE',
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_recoverable_tax
           FROM   po_distributions pod,
                  po_lines pol,
                  po_line_locations pll
           WHERE  pod.po_line_id = X_line_id
            AND   pll.po_line_id = pol.po_line_id
            AND   pll.line_location_id = pod.line_location_id
            AND   pod.po_release_id is null    -- Bug 3532747
            AND   (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0) <>  0 OR
                  nvl(pll.amount,0) - nvl(pll.amount_cancelled,0) <> 0)
            AND   nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 60

           SELECT nvl(SUM(nvl(pod.recoverable_tax, 0) *
                  decode(pol.order_type_lookup_code, 'RATE',  -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   'FIXED PRICE',
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_recoverable_tax
           FROM   po_distributions pod,
                  po_lines pol
           WHERE  pod.line_location_id = X_shipment_id
            AND   pod.po_release_id is null    -- Bug 3532747
            AND   pod.po_line_id = pol.po_line_id;

Line: 76

           SELECT nvl(SUM(nvl(pod.recoverable_tax, 0) *
                  decode(pol.order_type_lookup_code,
                   'FIXED PRICE',                         -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_recoverable_tax
           FROM   po_distributions  pod,
                  po_lines pol,
                  po_line_locations pll
           WHERE  pod.po_release_id = X_header_id
             AND  pll.line_location_id = pod.line_location_id
             AND   pll.po_line_id = pol.po_line_id
             AND  (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0) <>  0 OR
                  nvl(pll.amount,0) - nvl(pll.amount_cancelled,0) <> 0)
             AND  nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 93

           SELECT nvl(SUM(nvl(pod.recoverable_tax, 0) *
                  decode(pol.order_type_lookup_code,
                   'FIXED PRICE',                         -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_recoverable_tax
           FROM   po_distributions pod,
                  po_lines pol
           WHERE  pod.line_location_id = X_shipment_id
            AND   pod.po_line_id = pol.po_line_id;

Line: 107

           SELECT nvl(SUM(prd.recoverable_tax), 0)
	      into X_recoverable_tax
           FROM   po_req_distributions prd,
	  	  po_requisition_lines prl
           WHERE  prd.requisition_line_id = prl.requisition_line_id
	     AND  prl.requisition_header_id = x_header_id
             AND  nvl(prl.cancel_flag, 'N') <> 'Y'
             AND  nvl(prl.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 116

           SELECT nvl(SUM(recoverable_tax), 0)
	      into X_recoverable_tax
           FROM   po_req_distributions
           WHERE  requisition_line_id = X_line_id;

Line: 135

modified the select statements for the object_type 'PO' and 'REL' for all the
object_location. Accordingly, changed the where condition of these select
statement to take in to consideration of only those shipments which are not
fully cancelled */
    if (object_type = 'PO') then
       if (object_location = 'HEADER') then
           SELECT nvl(SUM(nvl(pod.nonrecoverable_tax, 0) *
                  decode(pol.order_type_lookup_code, 'RATE',       -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   'FIXED PRICE',
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_nonrecoverable_tax
           FROM   po_distributions pod,
                  po_lines pol,
                  po_line_locations pll
           WHERE  pod.po_header_id = X_header_id
             AND   pll.po_line_id = pol.po_line_id
             AND  pll.line_location_id = pod.line_location_id
             AND   pod.po_release_id is null    -- Bug 3532747
             AND  (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0) <>  0 OR
                  nvl(pll.amount,0) - nvl(pll.amount_cancelled,0) <> 0)
             AND  nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 160

           SELECT nvl(SUM(nvl(pod.nonrecoverable_tax, 0) *
                  decode(pol.order_type_lookup_code, 'RATE',          -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   'FIXED PRICE',
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_nonrecoverable_tax
           FROM   po_distributions pod,
                  po_lines pol,
                  po_line_locations pll
           WHERE  pod.po_line_id = X_line_id
             AND   pll.po_line_id = pol.po_line_id
             AND  pll.line_location_id = pod.line_location_id
             AND   pod.po_release_id is null    -- Bug 3532747
             AND  (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0) <>  0 OR
                  nvl(pll.amount,0) - nvl(pll.amount_cancelled,0) <> 0)
             AND  nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 179

           SELECT nvl(SUM(nvl(pod.nonrecoverable_tax, 0) *
                  decode(pol.order_type_lookup_code, 'RATE',         -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   'FIXED PRICE',
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_nonrecoverable_tax
           FROM   po_distributions pod,
                  po_lines pol
           WHERE  pod.line_location_id = X_shipment_id
             AND   pod.po_release_id is null    -- Bug 3532747
             AND   pod.po_line_id = pol.po_line_id;

Line: 195

           SELECT nvl(SUM(nvl(pod.nonrecoverable_tax, 0) *
                  decode(pol.order_type_lookup_code,
                   'FIXED PRICE',                       -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_nonrecoverable_tax
           FROM   po_distributions pod,
                  po_lines pol,
                  po_line_locations pll
           WHERE  pod.po_release_id = X_header_id
             AND   pll.po_line_id = pol.po_line_id
             AND  pll.line_location_id = pod.line_location_id
             AND  (nvl(pll.quantity,0)-nvl(pll.quantity_cancelled,0) <>  0 OR
                  nvl(pll.amount,0) - nvl(pll.amount_cancelled,0) <> 0)
             AND  nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 212

           SELECT nvl(SUM(nvl(pod.nonrecoverable_tax, 0) *
                  decode(pol.order_type_lookup_code,
                   'FIXED PRICE',                         -- SERVICES FPJ
                   ((nvl(pod.amount_ordered,0) - nvl(pod.amount_cancelled,0))/nvl(pod.amount_ordered,0)),
                   ((nvl(pod.quantity_ordered,0)-nvl(pod.quantity_cancelled,0))
                   / nvl(pod.quantity_ordered,0))  )),  0)
	      into X_nonrecoverable_tax
           FROM   po_distributions pod,
                  po_lines pol
           WHERE  pod.line_location_id = X_shipment_id
            AND   pod.po_line_id = pol.po_line_id;

Line: 226

           SELECT nvl(SUM(prd.nonrecoverable_tax), 0)
	      into X_nonrecoverable_tax
           FROM   po_req_distributions prd,
	  	  po_requisition_lines prl
           WHERE  prd.requisition_line_id = prl.requisition_line_id
	     AND  prl.requisition_header_id = x_header_id
             AND  nvl(prl.cancel_flag, 'N') <> 'Y'
             AND  nvl(prl.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 235

           SELECT nvl(SUM(nonrecoverable_tax), 0)
	      into X_nonrecoverable_tax
           FROM   po_req_distributions
           WHERE  requisition_line_id = X_line_id;

Line: 289

            SELECT  NVL(
                      SUM(
                        ROUND(
                          DECODE(POL.order_type_lookup_code,
                                 'FIXED PRICE',
                                 PLL.amount - NVL(PLL.amount_cancelled, 0),
                                 (PLL.quantity -
                                  NVL(PLL.quantity_cancelled, 0)) *
                                   price_override
                                ) / x_min_acct_unit
                        ) * x_min_acct_unit
                      ), 0
                    )
            INTO    x_header_amount
            FROM    po_line_locations PLL,
                    po_lines POL
            WHERE   PLL.po_line_id = POL.po_line_id
            AND     POL.po_header_id = x_header_id
            AND     PLL.shipment_type = 'BLANKET';

Line: 315

            SELECT  NVL(
                      SUM(
                        ROUND(
                          DECODE(POL.order_type_lookup_code,
                                 'FIXED PRICE',
                                 PLL.amount - NVL(PLL.amount_cancelled, 0),
                                 (PLL.quantity -
                                  NVL(PLL.quantity_cancelled, 0)) *
                                   price_override
                                ),
                          x_precision
                        )
                      ), 0
                    )
            INTO    x_header_amount
            FROM    po_line_locations PLL,
                    po_lines POL
            WHERE   PLL.po_line_id = POL.po_line_id
            AND     POL.po_header_id = x_header_id
            AND     PLL.shipment_type = 'BLANKET';

Line: 351

            SELECT nvl(SUM(round(decode(order_type_lookup_code,'RATE',amount,'FIXED PRICE', amount,
                                        unit_price * quantity)/X_min_acct_unit)
                               *X_min_acct_unit), 0)
               into X_header_amount
            FROM   po_lines
            WHERE  po_header_id = X_header_id
              AND  nvl(cancel_flag, 'N') <> 'Y'
              AND  nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 361

            SELECT nvl(SUM(round(decode(order_type_lookup_code,'RATE',amount,'FIXED PRICE', amount,
                                 unit_price * quantity), X_precision)), 0)
               into X_header_amount
            FROM   po_lines
            WHERE  po_header_id = X_header_id
              AND  nvl(cancel_flag, 'N') <> 'Y'
              AND  nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 382

           SELECT nvl(SUM(round(decode(pol.order_type_lookup_code,'FIXED PRICE',pll.amount,
                                pll.price_override * pll.quantity)/X_min_acct_unit)
                              *X_min_acct_unit), 0)              -- SERVICES FPJ
	      into X_header_amount
           FROM   po_line_locations pll,
                  po_lines pol
           WHERE  pll.po_release_id = X_header_id
             AND  pol.po_line_id = pll.po_line_id
             AND  nvl(pll.cancel_flag, 'N') <> 'Y'
             AND  nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 394

           SELECT nvl(SUM(round(decode(pol.order_type_lookup_code,'FIXED PRICE',pll.amount,
                          pll.price_override * pll.quantity),X_precision)), 0)       -- SERVICES FPJ
              into X_header_amount
           FROM   po_line_locations pll,
                  po_lines pol
           WHERE  pll.po_release_id = X_header_id
             AND  pol.po_line_id = pll.po_line_id
             AND  nvl(pll.cancel_flag, 'N') <> 'Y'
             AND  nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 415

           SELECT nvl(SUM(round(decode(order_type_lookup_code,'RATE',amount,'FIXED PRICE', amount,
                         unit_price * quantity)/X_min_acct_unit)
                              *X_min_acct_unit), 0)             -- SERVICES FPJ
	      into X_header_amount
           FROM   po_requisition_lines
           WHERE  requisition_header_id = x_header_id
             AND  nvl(cancel_flag, 'N') <> 'Y'
             AND  nvl(closed_code, 'OPEN') <> 'FINALLY CLOSED';

Line: 425

           SELECT nvl(SUM(round(decode(order_type_lookup_code,'RATE',amount,'FIXED PRICE', amount,
                            unit_price * quantity),X_precision)), 0)      -- SERVICES FPJ
              into X_header_amount
           FROM   po_requisition_lines
           WHERE  requisition_header_id = x_header_id;

Line: 488

        SELECT  NVL(
                  SUM(
                    ROUND(
                      DECODE(POL.order_type_lookup_code,
                             'FIXED PRICE',
                             PLL.amount - NVL(PLL.amount_cancelled, 0),
                             'RATE',
                             PLL.amount - NVL(PLL.amount_cancelled, 0),
                             (PLL.quantity - NVL(PLL.quantity_cancelled, 0)) *
                               price_override
                            ) / l_min_acct_unit
                    ) * l_min_acct_unit
                  ), 0
                )
        INTO    l_line_amount
        FROM    po_line_locations PLL,
                po_lines POL
        WHERE   PLL.po_line_id = POL.po_line_id
        AND     POL.po_line_id = p_line_id
        AND     PLL.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET');

Line: 514

        SELECT  NVL(
                  SUM(
                    ROUND(
                      DECODE(POL.order_type_lookup_code,
                             'FIXED PRICE',
                             PLL.amount - NVL(PLL.amount_cancelled, 0),
                             'RATE',
                             PLL.amount - NVL(PLL.amount_cancelled, 0),
                             (PLL.quantity - NVL(PLL.quantity_cancelled, 0)) *
                               price_override
                            ),
                      l_precision
                    )
                  ), 0
                )
        INTO    l_line_amount
        FROM    po_line_locations PLL,
                po_lines POL
        WHERE   PLL.po_line_id = POL.po_line_id
        AND     POL.po_line_id = p_line_id
        AND     PLL.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET');