APPS.POS_AP_INVOICES_PKG SQL Statements

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

Line: 23

         SELECT DISTINCT ph.segment1
         FROM   ap_invoice_distributions_all aid,
                po_distributions_all    pd,
                po_headers_all          ph
         WHERE  aid.invoice_id         	= l_invoice_id
         AND    aid.po_distribution_id 	= pd.po_distribution_id
         AND    pd.po_header_id     	= ph.po_header_id
         AND    ph.type_lookup_code 	= 'STANDARD'
         UNION ALL
         SELECT DISTINCT (ph.segment1||'-'||pr.release_num)
         FROM   ap_invoice_distributions_all aid,
                po_distributions_all    pd,
                po_headers_all          ph,
                po_releases_all		pr
         WHERE  aid.invoice_id         	= l_invoice_id
         AND    aid.po_distribution_id 	= pd.po_distribution_id
         AND    pr.po_release_id	= pd.po_release_id
         AND    ph.po_header_id		= pr.po_header_id
         AND    ph.type_lookup_code 	= 'BLANKET';

Line: 83

	select DISTINCT RSH.PACKING_SLIP packing_slip
	FROM   ap_invoice_distributions_all aid,
	  po_distributions_all     pd,
	  rcv_shipment_headers    rsh,
	  rcv_shipment_lines      rsl
	WHERE  aid.invoice_id          = l_invoice_id
	  AND aid.po_distribution_id  = pd.po_distribution_id
	  AND pd.LINE_LOCATION_ID  = rsl.po_line_location_id
	  AND    rsl.shipment_header_id = rsh.shipment_header_id
	  AND    rsh.packing_slip is not null
	union
	select DISTINCT RSL.PACKING_SLIP packing_slip
	FROM   ap_invoice_distributions_all aid,
	  po_distributions_all     pd,
	  rcv_shipment_headers    rsh,
	  rcv_shipment_lines      rsl
	WHERE  aid.invoice_id          = l_invoice_id
	  AND aid.po_distribution_id  = pd.po_distribution_id
	  AND pd.LINE_LOCATION_ID  = rsl.po_line_location_id
	  AND    rsl.shipment_header_id = rsh.shipment_header_id
	  AND    rsl.packing_slip is not null;

Line: 145

	select DISTINCT RSH.PACKING_SLIP packing_slip
	FROM   ap_invoice_distributions_all aid,
	  po_distributions_all     pd,
	  rcv_shipment_headers    rsh,
	  rcv_shipment_lines      rsl
	WHERE  aid.invoice_id          = l_invoice_id
	  AND aid.po_distribution_id  = pd.po_distribution_id
	  AND pd.LINE_LOCATION_ID  = rsl.po_line_location_id
	  AND    rsl.shipment_header_id = rsh.shipment_header_id
	  AND    rsh.packing_slip is not null
	union
	select DISTINCT RSL.PACKING_SLIP packing_slip
	FROM   ap_invoice_distributions_all aid,
	  po_distributions_all     pd,
	  rcv_shipment_headers    rsh,
	  rcv_shipment_lines      rsl
	WHERE  aid.invoice_id          = l_invoice_id
	  AND aid.po_distribution_id  = pd.po_distribution_id
	  AND pd.LINE_LOCATION_ID  = rsl.po_line_location_id
	  AND    rsl.shipment_header_id = rsh.shipment_header_id
	  AND    rsl.packing_slip is not null;

Line: 212

    SELECT due_date
    FROM AP_PAYMENT_SCHEDULES_ALL
    WHERE invoice_id = l_invoice_id;

Line: 267

     SELECT alc.displayed_field
     from   ap_holds_All aha,
     	    ap_lookup_codes alc
     where  alc.lookup_type = 'HOLD CODE'
	    and aha.hold_lookup_code = alc.lookup_code (+)
            and aha.invoice_id = l_invoice_id
            and aha.release_lookup_code is null
     order by aha.creation_date desc;

Line: 326

     select distinct  rsh.receipt_num, rsh.SHIPMENT_HEADER_ID
     from ap_invoice_lines_all al,
		rcv_transactions rt,
		rcv_shipment_headers rsh
     where al.invoice_id = l_invoice_id
        	and al.rcv_transaction_id = rt.transaction_id
		and rt.SHIPMENT_HEADER_ID =  rsh.SHIPMENT_HEADER_ID;

Line: 335

/*	SELECT distinct rsh.SHIPMENT_HEADER_ID, rsh.receipt_num
	FROM   ap_invoice_distributions_all aid,
		rcv_transactions rt,
		rcv_shipment_headers rsh
	WHERE  aid.invoice_id =  l_invoice_id
        	and aid.rcv_transaction_id = rt.transaction_id
		and rt.SHIPMENT_HEADER_ID =  rsh.SHIPMENT_HEADER_ID;

Line: 399

        select distinct ph.segment1, ph.po_header_id, null
        from ap_invoice_lines_all ail,
             po_headers_all ph
        where ail.invoice_id = l_invoice_id and
              ail.po_release_id is null and
              ph.po_header_id = ail.po_header_id   and
              ph.type_lookup_code       = 'STANDARD'
        union all
        select distinct (ph.segment1||'-'||pr.release_num), ph.po_header_id, pr.po_release_id
        from ap_invoice_lines_all ail,
             po_headers_all          ph,
             po_releases_all            pr
        WHERE  ail.invoice_id           = l_invoice_id and
             pr.po_release_id   =  ail.po_release_id and
             ph.po_header_id            = ail.po_header_id and
             ph.type_lookup_code        = 'BLANKET' ;

Line: 417

/*       SELECT DISTINCT ph.segment1,ph.po_header_id,null
         FROM   ap_invoice_distributions_all aid,
                po_distributions_all    pd,
                po_headers_all          ph
         WHERE  aid.invoice_id          = l_invoice_id
         AND    aid.po_distribution_id  = pd.po_distribution_id
         AND    pd.po_header_id         = ph.po_header_id
         AND    ph.type_lookup_code     = 'STANDARD'
         UNION ALL
         SELECT DISTINCT (ph.segment1||'-'||pr.release_num),ph.po_header_id, pr.po_release_id
         FROM   ap_invoice_distributions_all aid,
                po_distributions_all    pd,
                po_headers_all          ph,
                po_releases_all         pr
         WHERE  aid.invoice_id          = l_invoice_id
         AND    aid.po_distribution_id  = pd.po_distribution_id
         AND    pr.po_release_id        = pd.po_release_id
         AND    ph.po_header_id         = pr.po_header_id
         AND    ph.type_lookup_code     = 'BLANKET';

Line: 481

         select (0 - sum(nvl(amount,0)))
         into   amount_withheld
         from   ap_invoice_distributions_all --ap_invoice_distributions
         where  invoice_id = l_invoice_id
         and    line_type_lookup_code = 'AWT';

Line: 504

         select nvl(sum(amount),0)
         INTO retainage_amount1
         from ap_invoice_lines_All
         where invoice_id = l_invoice_id
         and LINE_TYPE_LOOKUP_CODE = 'RETAINAGE RELEASE';

Line: 510

         SELECT nvl(sum(amount),0)
         INTO retainage_amount2
         FROM ap_invoice_distributions_all aid
         WHERE aid.invoice_id = l_invoice_id
         AND aid.line_type_lookup_code = 'RETAINAGE'
         AND EXISTS
           (SELECT 'X' FROM ap_invoice_lines_all ail
           WHERE ail.invoice_id = l_invoice_id
           AND ail.line_number = aid.invoice_line_number
           AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE'
           );

Line: 538

         select nvl(sum(amount),0)
         INTO prepay_amount1
         from ap_invoice_lines_All
         where invoice_id = l_invoice_id
         and LINE_TYPE_LOOKUP_CODE = 'PREPAY';

Line: 544

         SELECT nvl(sum(amount),0)
         INTO prepay_amount2
         FROM ap_invoice_distributions_all aid
         WHERE aid.invoice_id = l_invoice_id
         AND aid.line_type_lookup_code = 'PREPAY'
         AND EXISTS
           (SELECT 'X' FROM ap_invoice_lines_all ail
           WHERE ail.invoice_id = l_invoice_id
           AND ail.line_number = aid.invoice_line_number
           AND ail.line_type_lookup_code <> 'PREPAY'
           );

Line: 571

         select nvl(sum(amount),0)
         INTO amount1
         from ap_invoice_lines_All
         where invoice_id = l_invoice_id
         and LINE_TYPE_LOOKUP_CODE = 'TAX' ;

Line: 594

         select nvl(sum(amount),0)
         INTO amount1
         from ap_invoice_lines_All
         where invoice_id = l_invoice_id;

Line: 599

         SELECT nvl(sum(amount),0)
         INTO retainage_amount2
         FROM ap_invoice_distributions_all aid
         WHERE aid.invoice_id = l_invoice_id
         AND aid.line_type_lookup_code = 'RETAINAGE'
         AND EXISTS
           (SELECT 'X' FROM ap_invoice_lines_all ail
           WHERE ail.invoice_id = l_invoice_id
           AND ail.line_number = aid.invoice_line_number
           AND ail.line_type_lookup_code <> 'RETAINAGE RELEASE'
           );

Line: 611

         SELECT nvl(sum(amount),0)
         INTO prepay_amount2
         FROM ap_invoice_distributions_all aid
         WHERE aid.invoice_id = l_invoice_id
         AND aid.line_type_lookup_code = 'PREPAY'
         AND EXISTS
           (SELECT 'X' FROM ap_invoice_lines_all ail
           WHERE ail.invoice_id = l_invoice_id
           AND ail.line_number = aid.invoice_line_number
           AND ail.line_type_lookup_code <> 'PREPAY'
           );

Line: 642

 	SELECT ac.check_number,
 	       ac.check_id,
 	       ac.check_Date
 	FROM   ap_invoice_payments_all aip,
 	       ap_checks_all           ac
 	WHERE  aip.invoice_id       = l_invoice_id
 	AND aip.check_id         = ac.check_id;

Line: 705

 	SELECT distinct ac.check_number,
 	       ac.check_id,
 	       ac.check_Date
--, 	       alc2.displayed_field
 	FROM   ap_invoice_payments_all aip,
 	       ap_checks_all           ac
--,  	       ap_lookup_codes     alc2
 	WHERE  aip.invoice_id       = l_invoice_id
 	AND aip.check_id         = ac.check_id;

Line: 789

 	SELECT distinct ac.check_number,
 	       ac.check_id,
-- 	       ac.check_Date,
 	       alc2.displayed_field
 	FROM   ap_invoice_payments_all aip,
 	       ap_checks_all           ac,
 	       ap_lookup_codes     alc2
 	WHERE  aip.invoice_id       = l_invoice_id
 	AND aip.check_id         = ac.check_id
 	AND    alc2.lookup_type(+)     = 'PAYMENT METHOD'
 	AND    alc2.lookup_code(+)     = ac.payment_method_lookup_code;

Line: 802

 	SELECT distinct ac.check_Date
 	FROM   ap_invoice_payments_all aip,
 	       ap_checks_all           ac
 	WHERE  aip.invoice_id       = l_invoice_id
 	AND aip.check_id         = ac.check_id;

Line: 876

     select count(*)
     into l_count
     from ap_holds_all
     where invoice_id = l_invoice_id
     and release_lookup_code is null;