ORACLE MASTERMINDS

     AR Selected Invoices Print

Description

Queries to get AR Selected Invoices.

/* Adjustments */

SELECT line_adjusted adj_line, tax_adjusted adj_tax,

       freight_adjusted adj_freight, adj.amount adj_amount,

       l1.meaning adj_type, l2.meaning adj_status,

       adj.reason_code adj_reason_code, trx.trx_number adj_trx_number,

       TYPES.TYPE adj_trx_type, l3.meaning adj_trx_type_name,

       trx.trx_date adj_trx_date

  FROM ar_adjustments_all adj

       ,ra_customer_trx_all trx

       ,ra_cust_trx_types_all TYPES

       ,ar_lookups l1

       ,ar_lookups l2

       ,ar_lookups l3

 WHERE l1.lookup_type = 'ADJUSTMENT_TYPE'

   AND l2.lookup_type = 'APPROVAL_TYPE'

   AND l3.lookup_type = 'INV/CM'

   AND adj.status = l2.lookup_code

   AND adj.TYPE = l1.lookup_code

   AND trx.cust_trx_type_id = TYPES.cust_trx_type_id

   AND TYPES.TYPE = l3.lookup_code

   AND adj.adjustment_id = :customer_trx_id

   AND adj.customer_trx_id = trx.customer_trx_id

   /* Bank Details */

SELECT abb.bank_name, abb.address_line1, abb.city, NULL, abb.bank_number,

       aba.bank_account_num

  FROM ce_bank_accounts aba, ce_bank_branches_v abb

 WHERE abb.branch_party_id = aba.bank_branch_id

   AND aba.bank_account_id = :cf_bank_id;

/* Commitment Adjustment */

SELECT SUM (amount) commit_this_invoice

  FROM ar_adjustments_all

 WHERE adjustment_type = 'C'

   AND (   (    (customer_trx_id = :customer_trx_id)

            AND (subsequent_trx_id IS NULL)

           )

        OR subsequent_trx_id = :customer_trx_id

       )

/* Line Details */

SELECT   c.customer_trx_id line_customer_trx_id,

         c.customer_trx_line_id line_customer_trx_line_id,

         c.memo_line_id memo_line_id,

         DECODE (c2.line_number, NULL, c.line_number, NULL) line_number,

         c.line_type line_type,

         NVL (c.translated_description, c.description) line_item_description,

         NVL (c.quantity_ordered, c.quantity_invoiced) line_qty_ordered,

         NVL (c.quantity_invoiced, c.quantity_credited) line_qty_invoiced,

         u.unit_of_measure line_uom,

         NVL (c.unit_selling_price,

              c.gross_unit_selling_price

             ) line_unit_selling_price,

         NVL (c.extended_amount,

              c.gross_extended_amount) line_extended_amount,

         c.extended_amount line_net_amount, c.sales_order line_sales_order,

         c.sales_order_date line_sales_order_date, c.tax_rate line_tax_rate,

         c.vat_tax_id line_vat_tax_id,

         c.tax_exemption_id line_tax_exemption_id,

         c.sales_tax_id line_location_rate_id,

         c.tax_precedence line_tax_precedence,

         DECODE (TO_CHAR (c2.line_number),

                 NULL, 'N',

                 'Y'

                ) line_is_a_child_flag,

         NVL (c.link_to_cust_trx_line_id,

              c.customer_trx_line_id) link_to_line,

         DECODE (c.line_type, 'LINE', 0, 1) line_child_indicator,

         NVL (c.link_to_cust_trx_line_id, -1) link_to_cust_trx_line_id,

         DECODE (msi.item_type, 'FRT', 'A', 'A') line_of_type_frt,

         DECODE (c.link_to_cust_trx_line_id,

                 '', c.line_number,

                 c2.line_number

                ) order_by1,

         1 dummy, c.amount_includes_tax_flag line_tax_inclusive

    FROM ra_customer_trx_lines_all c,

         ra_customer_trx_lines_all c2,

         mtl_units_of_measure_vl u,

         mtl_system_items_b msi

   WHERE c.customer_trx_id = :customer_trx_id

     AND c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)

     AND c.uom_code = u.uom_code(+)

     AND c.inventory_item_id = msi.inventory_item_id(+)

     AND msi.organization_id(+) = :so_organization_id

ORDER BY DECODE (c2.line_number,

                 NULL, DECODE (c.line_type,

                               'LINE', c.line_number * 10000 + 0,

                               'TAX', c.line_number * 10000 + 8000,

                               100000000000

                              ),

                   DECODE (c2.line_type,

                           'LINE', c2.line_number * 10000 + 0,

                           'TAX', c2.line_number * 10000 + 8000,

                           c2.line_number * 10000 + 9000

                          )

                 + DECODE (c.line_type,

                           'LINE', 0,

                           'TAX', 8000,

                           'FREIGHT', 9000

                          )

                 + c.line_number

                )

/* Reg Details */

SELECT hl.address_line_1 reg_addr1, hl.address_line_2 reg_addr2,

       hl.address_line_3 reg_addr3, hl.town_or_city reg_addr4,

       hl.postal_code reg_addr5, hl.telephone_number_1 reg_tel,

       hl.telephone_number_3 reg_fax, hl.telephone_number_3 reg_email

  FROM hr_locations hl,

       hr_organization_units hon,

       hrfg_operating_units hou,

       hr_legal_entities hle

 WHERE hl.location_id = hon.location_id

   AND hou.location_id = hl.location_id

   AND hon.organization_id = hou.operating_units_id

   AND hou.business_group_id = hle.business_group_id

   AND hou.operating_units_id = :org_id1;

/* Remit Customer Details */

SELECT loc.address1 remit_address1, loc.address2 remit_address2,

       loc.address3 remit_address3, loc.address4 remit_address4,

       loc.city remit_city, loc.state remit_state,

       loc.postal_code remit_postal_code, loc.country remit_country,

       loc.province remit_province,

       acct_site.cust_acct_site_id remit_address_id

  FROM hz_cust_acct_sites_all acct_site,

       hz_party_sites party_site,

       hz_locations loc

 WHERE acct_site.cust_acct_site_id = :remit_to_control_id

   AND acct_site.party_site_id = party_site.party_site_id

   AND loc.location_id = party_site.location_id;

/* Shiping Details */

SELECT party.party_name ship_cust_name, loc.address1 ship_address1,

       loc.address2 ship_address2, loc.address3 ship_address3,

       loc.address4 ship_address4, loc.city ship_city,

       NVL (loc.state, loc.province) ship_state,

       loc.postal_code ship_postal_code, loc.country ship_country,

       u.site_use_id ship_site_id, c.cust_account_id ship_customer_id,

       u.tax_reference ship_site_tax_reference,

       party.tax_reference ship_cust_tax_reference, loc.state ship_to_state,

       loc.province ship_to_province

  FROM hz_cust_accounts c,

       hz_parties party,

       hz_cust_acct_sites_all a,

       hz_party_sites party_site,

       hz_locations loc,

       hz_cust_site_uses_all u

 WHERE u.cust_acct_site_id = a.cust_acct_site_id

   AND a.party_site_id = party_site.party_site_id

   AND loc.location_id = party_site.location_id

   AND u.site_use_id = :ship_to_site_use_id

   AND c.cust_account_id = :ship_to_customer_id

   AND c.party_id = party.party_id

/* Invoice Summary */

SELECT   c.line_type inv_tax_type, l.meaning inv_tax_type_name,

         NVL (c.translated_description,

              c.description

             ) inv_tax_line_description,

         SUM (c.extended_amount) inv_tax_extended_amount,

         c.tax_rate inv_tax_rate,

         v.amount_includes_tax_flag inv_tax_inclusive_flag,

         c.tax_exemption_id inv_tax_exemption_id,

         c.sales_tax_id inv_tax_location_rate_id,

         c.tax_precedence inv_tax_precedence,

         SUM (c_line.extended_amount) euro_taxable_amount

    FROM ra_customer_trx_lines_all c,

         ar_lookups l,

         ar_vat_tax_vl v,

         ra_customer_trx_lines_all c_line

   WHERE c.customer_trx_id = :customer_trx_id

     AND c.line_type = l.lookup_code

     AND l.lookup_type = 'STD_LINE_TYPE'

     AND c.vat_tax_id = v.vat_tax_id(+)

     AND c_line.customer_trx_line_id(+) = c.link_to_cust_trx_line_id

GROUP BY c.line_type,

         l.meaning,

         NVL (c.translated_description, c.description),

         c.tax_rate,

         c.tax_exemption_id,

         c.sales_tax_id,

         v.amount_includes_tax_flag,

         c.tax_precedence

ORDER BY c.tax_precedence, c.tax_rate;