JG Regional Invoice Format Program- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Regional Invoice Print Program
Application: Regional Localizations
Source: Regional Invoice Format Program (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZARIN_XML
DB package: JG_JGZZARIN_XMLP_PKG
Description: Regional Invoice Print Program
Application: Regional Localizations
Source: Regional Invoice Format Program (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZARIN_XML
DB package: JG_JGZZARIN_XMLP_PKG
Run
JG Regional Invoice Format Program- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
select t.bill_to_customer_id, t.bill_to_site_use_id, t.ship_to_customer_id, t.ship_to_site_use_id, t.remit_to_address_id, t.receipt_method_id, f.territory_short_name country, hcsu.tax_reference, t.customer_trx_id, t.trx_date, t.trx_number, t.comments, t.purchase_order po, to_date(t.global_attribute1, 'yyyy/mm/dd hh24:mi:ss') tax_date, l.line_number,l.description, nvl( (select nvl(product_fisc_classification, product_category) from zx_lines_det_factors where trx_number = t.trx_number and internal_organization_id = trans.org_id and trx_id = t.customer_trx_id and trx_line_id = l.customer_trx_line_id), nvl( (select mc.segment1 from mtl_categories mc, mtl_item_categories mic where mic.inventory_item_id=msi.inventory_item_id and mic.category_set_id = (select category_set_id from mtl_category_sets where category_set_name = 'STATISTICAL_CODE') and mic.organization_id = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',trans.org_id)) and mc.category_id=mic.category_id) , m.tax_product_category) ) stat_code, l.uom_code, l.quantity_invoiced, l.unit_selling_price, l.extended_amount, decode(p.tax_rate,0,tax.tax_rate_code,to_char(p.tax_rate)) tax_rate, p.vat_tax_id, p.extended_amount tax, trans.description type, t.invoice_currency_code currency, type.description p_type, l.extended_amount+p.extended_amount total, t.global_attribute3 bank_acc_num, DECODE(:P_ORDER_BY, 'TRX_NUMBER', t.TRX_NUMBER, 'CUSTOMER', hp.PARTY_NAME, 'POSTAL_CODE', hl.POSTAL_CODE, t.TRX_NUMBER) ORDER_BY, JG_JGZZARIN_XMLP_PKG.s_sformula(:INVOICE_TOTAL,t.invoice_currency_code) S_SAY_FORMULA, &trx_date CF_TRX_DATE, &tax_date CF_TAX_DATE, JG_JGZZARIN_XMLP_PKG.cf_1formula() CF_1, JG_JGZZARIN_XMLP_PKG.S_SAY_p S_SAY, JG_JGZZARIN_XMLP_PKG.CP_Ship_Cust_Name_p CP_Ship_Cust_Name, JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_1_p CP_Ship_Add_1, JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_2_p CP_Ship_Add_2, JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_3_p CP_Ship_Add_3, JG_JGZZARIN_XMLP_PKG.CP_Ship_Add_4_p CP_Ship_Add_4, JG_JGZZARIN_XMLP_PKG.CP_Ship_City_p CP_Ship_City, JG_JGZZARIN_XMLP_PKG.CP_Ship_Postal_Code_p CP_Ship_Postal_Code, JG_JGZZARIN_XMLP_PKG.CP_Ship_Country_p CP_Ship_Country, JG_JGZZARIN_XMLP_PKG.CP_Bill_Cust_Name_p CP_Bill_Cust_Name, JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_1_p CP_Bill_Add_1, JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_2_p CP_Bill_Add_2, JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_3_p CP_Bill_Add_3, JG_JGZZARIN_XMLP_PKG.CP_Bill_Add_4_p CP_Bill_Add_4, JG_JGZZARIN_XMLP_PKG.CP_Bill_City_p CP_Bill_City, JG_JGZZARIN_XMLP_PKG.CP_Bill_Postal_Code_p CP_Bill_Postal_Code, JG_JGZZARIN_XMLP_PKG.CP_Bill_Country_p CP_Bill_Country, JG_JGZZARIN_XMLP_PKG.CP_Bill_Tax_Reference_p CP_Bill_Tax_Reference, JG_JGZZARIN_XMLP_PKG.CP_Remit_Cust_p CP_Remit_Cust, JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_1_p CP_Remit_Add_1, JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_2_p CP_Remit_Add_2, JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_3_p CP_Remit_Add_3, JG_JGZZARIN_XMLP_PKG.CP_Remit_Add_4_p CP_Remit_Add_4, JG_JGZZARIN_XMLP_PKG.CP_Remit_City_p CP_Remit_City, JG_JGZZARIN_XMLP_PKG.CP_Remit_Country_p CP_Remit_Country, JG_JGZZARIN_XMLP_PKG.CP_1_p CP_1, JG_JGZZARIN_XMLP_PKG.CP_Remit_Postal_Code_p CP_Remit_Postal_Code from ra_customer_trx_lines_all l, ra_customer_trx_lines_all p, ra_cust_trx_line_gl_dist_all g, hz_cust_acct_sites_all hcas, hz_party_sites hps, hz_locations hl, hz_cust_site_uses_all hcsu, hz_cust_accounts hca, hz_parties hp, ra_customer_trx t, zx_rates_b tax, ra_cust_trx_types_all trans, ra_terms type, ar_memo_lines m, mtl_system_items msi, fnd_territories_vl f where l.customer_trx_line_id = p.link_to_cust_trx_line_id and l.customer_trx_id = t.customer_trx_id and p.customer_trx_id = t.customer_trx_id and g.customer_trx_id = t.customer_trx_id and g.account_class = 'REC' and g.latest_rec_flag = 'Y' and hcsu.site_use_id = t.bill_to_site_use_id and hcas.cust_acct_site_id = hcsu.cust_acct_site_id and hcas.cust_account_id = hca.cust_account_id and hca.party_id = hp.party_id and hcas.party_site_id = hps.party_site_id and hps.location_id = hl.location_id and hl.country = f.territory_code(+) and trans.cust_trx_type_id = t.cust_trx_type_id and t.term_id = type.term_id(+) and p.vat_tax_id = tax.tax_rate_id and l.inventory_item_id = msi.inventory_item_id(+) and l.memo_line_id = m.memo_line_id(+) and decode(:P_BATCH_ID,null,1,:P_BATCH_ID) = decode(:P_BATCH_ID,null,1,t.batch_id) and t.set_of_books_id =:P_SET_OF_BOOKS_ID and t.trx_date between nvl(:P_INVOICE_DATE_LOW,t.trx_date) and nvl(:P_INVOICE_DATE_HIGH,t.trx_date) and t.complete_flag = 'Y' &PRINT_TYPE and t.cust_trx_type_id=decode( :P_DOCUMENT_TYPE,null,t.cust_trx_type_id,:P_DOCUMENT_TYPE) and t.trx_number between decode(:P_INVOICE_NUMBER_LOW,null,:TRX_NUMBER_LOW,:P_INVOICE_NUMBER_LOW) and decode(:P_INVOICE_NUMBER_HIGH,null,:TRX_NUMBER_HIGH,:P_INVOICE_NUMBER_HIGH) and hca.cust_account_id = decode(:P_CUST_ID,null,hca.cust_account_id,:P_CUST_ID) and l.org_id=trans.org_id and p.org_id= trans.org_id and g.org_id=trans.org_id and nvl(msi.organization_id,to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',trans.org_id))) = to_number(OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',trans.org_id)) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV | |
Order By |
|
LOV Oracle | |
Batch |
|
LOV Oracle | |
Document Type |
|
LOV Oracle | |
DUPLICATE |
|
LOV Oracle | |
Invoice Number Low |
|
LOV Oracle | |
Invoice Number High |
|
LOV Oracle | |
Invoice Date Low |
|
Date | |
Invoice Date High |
|
Date | |
Customer Name |
|
LOV Oracle |