JA India - Inward Stock Register Report(XML Publisher)
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Inward Stock Register Report(XML Publisher)
Short Name: JAINIOSR_XML
DB package: JAI_EXTRACT_PKG
Source: India - Inward Stock Register Report(XML Publisher)
Short Name: JAINIOSR_XML
DB package: JAI_EXTRACT_PKG
Run
JA India - Inward Stock Register Report(XML Publisher) and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT b.*, (b.opening_balance_qty + b.bal_qty) balance_qty FROM (SELECT a.*, decode(a.trx_flag, 'I', -1, 'R', 1, 1) * a.qty_received qty, SUM(decode(a.trx_flag, 'I', -1, 'R', 1, 1) * a.qty_received) OVER(PARTITION BY a.item_name ORDER BY a.trx_date, rownum) bal_qty FROM (SELECT c.rep_context_entity_name operating_unit, c.rep_organization_id organization_id, c.rep_organization_name organization_name, c.rep_location_id location_id, c.rep_location_name location_name, d.trx_date trx_date, d.PRODUCT_CODE item_name, d.document_type document_type, d.entity_code entity_code, d.trx_number document_number, d.trx_line_quantity qty_received, d.party_name party_name, NULL ecc_code, d.PRODUCT_CODE item_folio_no, /*Modified by Junjian for bug#19456854 begin*/ /*CASE WHEN d.applied_to_entity_code = 'BILL_OF_ENTRY' THEN d.APPLIED_TO_TRX_ID || ' ' || d.APPLIED_TO_TRX_DATE*/ /*Modified by Junjian for bug#19456854 end*/ /*END par_boe_info,*/ /*Modified by Hongjun for bug#20362697 begin*/ CASE WHEN d.applied_to_entity_code = 'BILL_OF_ENTRY' THEN d.tax_invoice_number || ' ' || d.tax_invoice_date || d.applied_to_trx_id || ' ' || d.applied_to_trx_date ELSE d.tax_invoice_number || ' ' || d.tax_invoice_date END par_boe_info, /*Modified by Hongjun for bug#20362697 end*/ d.party_name || ' ' || d.party_address party_info, --NULL input_rec_info, -- commented out by zhiwei.xin for bug#19456668 e.ATTRIBUTE3 || e.ATTRIBUTE4 INPUT_REC_INFO, -- added by zhiwei.xin for bug#19456668 d.intended_use_desc usage, NULL other_reference, e.numeric1 opening_balance_qty, decode(d.entity_code, 'SALES_ORDER_ISSUE', 'I', 'OE_ORDER_HEADERS', 'I', 'ISSUE', 'I', 'RECEIPT', 'R', 'RCV_TRANSACTION', --'R', -- commented out by zhiwei.xin for bug#19456182 decode(d.TRX_TYPE, 'RETURN TO RECEIVING', 'I', 'R'), -- added by zhiwei.xin for bug#19456182 'R') trx_flag FROM jai_rep_context_t c, jai_rep_trx_detail_t d, jai_rep_trx_jx_ext_t e WHERE c.rep_context_id = d.rep_context_id AND c.request_id = d.request_id AND c.extract_summary_code = 'H' AND d.trx_line_quantity <> 0 AND d.detail_tax_line_id = e.detail_tax_line_id AND c.request_id = :p_conc_request_id GROUP BY c.rep_context_entity_name, c.rep_organization_id, c.rep_organization_name, c.rep_location_id, c.rep_location_name, d.trx_date, d.PRODUCT_CODE, d.document_type, d.entity_code, d.trx_type, -- added by zhiwei.xin for bug#19456182 d.trx_number, d.trx_line_quantity, d.trx_line_id, d.intended_use_desc, d.party_name, d.party_address, -- added by zhiwei.xin for bug#19456668 begin e.ATTRIBUTE3, e.ATTRIBUTE4, -- added by zhiwei.xin for bug#19456668 end. d.party_site_name, /*Modified by Junjian for bug#19456854 begin*/ d.applied_to_entity_code, d.applied_to_trx_id, d.applied_to_trx_date, /*Modified by Junjian for bug#19456854 end*/ /*Modified by Hongjun for bug#20362697 begin*/ d.tax_invoice_number, d.tax_invoice_date, /*Modified by Hongjun for bug#20362697 end*/ e.numeric1) a ) b ORDER BY b.organization_id, b.location_id, b.item_name, b.trx_date |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV Oracle | |
Inventory Orgnization |
|
LOV Oracle | |
Location |
|
LOV Oracle | |
Date From |
|
Date | |
Date To |
|
Date | |
Item Class |
|
LOV Oracle |