AR Selected InvoicesPrintDescriptionQueries t…

     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;
  • January 4, 2017 | 13 views
  • Comments