JA India - RG23C Part I Report - draft - draft

Description
Categories: BI Publisher
Columns: Slno, Register Id, Transaction Type, Vendor Id, Vendor Site Id, Receipt Id, Organization Id, Location Id, Organization Name, Description ...
Application: Asia/Pacific Localizations
Source: India - RG23C Part I Report (XML) - Not Supported: Reserved For Future Use - Not Supported: Reserved For Future Use
Short Name: JAI23C_1_XML
DB package: JA_JAIN23C1_XMLP_PKG
SELECT a.slno,a.register_id,a.transaction_type,a.vendor_id,a.vendor_site_id,a.receipt_ref receipt_id,
a.organization_id,
a.location_id,
org.organization_name,
loc.description,
loc.address_line_1,
loc.address_line_2,
loc.address_line_3,
hr_org.excise_duty_comm,
hr_org.excise_duty_division,
hr_org.excise_duty_range,
hr_org.ec_code,
hr_org.excise_duty_circle,
a.slno,
a.transaction_date,
a.inventory_item_id,
b.description descr,
substr(a.identification_no,1,10)||' '||
substr(a.identification_mark,1,10)||' '||
substr(a.brand_name,1,10) Ident_Dtls,
DECODE(a.transaction_type,'R',a.quantity_received,'PR',a.quantity_received,'IOR',a.quantity_received,'CR',a.quantity_received,'RA',a.quantity_received,'Y',a.quantity_received) rec_qty,
DECODE(a.transaction_type,'R',a.oth_receipt_id_ref,'PR',a.oth_receipt_id_ref,'IOR',a.oth_receipt_id_ref,'CR',a.oth_receipt_id_ref,'RA',a.oth_receipt_id_ref,'Y',a.oth_receipt_id_ref) excise_invoice_id,
DECODE(a.transaction_type,'R',a.oth_receipt_date,'PR',a.oth_receipt_date,'IOR',a.oth_receipt_date,'CR',a.oth_receipt_date,'RA',a.oth_receipt_date,'Y',a.oth_receipt_date) excise_invoice_date,
DECODE(a.transaction_type,'R',SUBSTR(c.vendor_name,1,20)||' '||
SUBSTR(d.address_line1,1,10)||' '||
SUBSTR(d.address_line2,1,10)||' '||
SUBSTR(d.city,1,9)||' '||
SUBSTR(d.state,1,5)||' '||
SUBSTR(d.zip,1,6)||' '||
SUBSTR(d.province,1,10)||' '||
SUBSTR(d.country,1,10),
'RTV',SUBSTR(c.vendor_name,1,20)||' '||
SUBSTR(d.address_line1,1,10)||' '||
SUBSTR(d.address_line2,1,10)||' '||
SUBSTR(d.city,1,9)||' '||
SUBSTR(d.state,1,5)||' '||
SUBSTR(d.zip,1,6)||' '||
SUBSTR(d.province,1,10)||' '||
SUBSTR(d.country,1,10),
'V',Substr(e.organization_name,1,25)) Vend_Dtls,
SUBSTR(a.range_no,1,10) Range1,
SUBSTR(a.division_no,1,10) Division1,
a.date_of_verification,
DECODE(a.transaction_type,'I',SUBSTR(a.goods_issue_id_ref,1,10),'IOI',SUBSTR(a.goods_issue_id_ref,1,10),'PI',SUBSTR(a.goods_issue_id_ref,1,10),'RI',SUBSTR(a.goods_issue_id_ref,1,10),'RTV',SUBSTR(a.goods_issue_id_ref,1,10),'X',SUBSTR(a.goods_issue_id_ref,1,10),
'V',SUBSTR(a.goods_issue_id_ref,1,10)) goods_issue_id_ref,
DECODE(a.transaction_type,'I',a.goods_issue_date,'IOI',a.goods_issue_date,'PI',a.goods_issue_date,'RI',a.goods_issue_date,'RTV',a.goods_issue_date,'X',a.goods_issue_date,'V',a.goods_issue_date) goods_issue_date,
DECODE(a.transaction_type,'I',a.goods_issue_quantity,'PI',a.goods_issue_quantity,'IOI',a.goods_issue_quantity,
'RI',a.goods_issue_quantity,'RTV',a.goods_issue_quantity) goods_issue_qty,
SUBSTR(a.place_of_install,1,10) place_of_install,
a.date_of_installation,
a.date_of_commission,
DECODE(a.transaction_type,'I',SUBSTR(a.sales_invoice_no,1,10),'IOI',SUBSTR(a.sales_invoice_no,1,10),'PI',SUBSTR(a.sales_invoice_no,1,10),'RI',SUBSTR(a.sales_invoice_no,1,10),'RTV',SUBSTR(a.sales_invoice_no,1,10),
'X',SUBSTR(a.sales_invoice_no,1,10),
'V',SUBSTR(a.sales_invoice_no,1,10)) sales_invoice_no,
DECODE(a.transaction_type,'I',a.sales_invoice_date,'IOI',a.sales_invoice_date,'PI',a.sales_invoice_date,'RI',a.sales_invoice_date,'RTV',a.sales_invoice_date,'X',a.sales_invoice_date,'V',a.sales_invoice_date) sales_invoice_date,
DECODE(a.transaction_type,'I',a.sales_invoice_quantity,'IOI',a.sales_invoice_quantity,'PI',a.sales_invoice_quantity,
'RI',a.sales_invoice_quantity,'RTV',a.sales_invoice_quantity,'X',a.sales_invoice_quantity,
'V',a.sales_invoice_quantity) sales_invoice_qty,
DECODE(a.transaction_type,'I',SUBSTR(a.oth_receipt_id_ref,1,10),'IOI',SUBSTR(a.oth_receipt_id_ref,1,10),'PI',SUBSTR(a.oth_receipt_id_ref,1,10),'RI',SUBSTR(a.oth_receipt_id_ref,1,10),'RTV',SUBSTR(a.oth_receipt_id_ref,1,10),
'X',SUBSTR(a.oth_receipt_id_ref,1,10),
'V',SUBSTR(a.oth_receipt_id_ref,1,10)) oth_receipt_id,
DECODE(a.transaction_type,'I',a.oth_receipt_date,'IOI',a.oth_receipt_date,'PI',a.oth_receipt_date,'RI',a.oth_receipt_date,
'RTV',a.oth_receipt_date,'X',a.oth_receipt_date,
'V',a.oth_receipt_date) oth_receipt_date,
decode(a.transaction_type,'I',a.oth_receipt_quantity,'PI',a.oth_receipt_quantity,'RI',a.oth_receipt_quantity,'IOI',a.oth_receipt_quantity,'RTV',a.oth_receipt_quantity,
'X',a.oth_receipt_quantity,
'V',a.oth_receipt_quantity) oth_receipt_qty,
a.closing_balance_qty,
a2.slno a2slno2,
a2.inventory_item_id||'/'||a2.slno a2foliono2,
substr(a.remarks,1,10) remarks,a.opening_balance_qty,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_no) col17p_1,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_date) col17p_2,
decode(NVL(a.basic_ed,0) + NVL(a.additional_ed,0) + NVL(a.other_ed,0),0,a.sales_invoice_quantity) col18,a.primary_uom_code,
substr(rcv.receipt_num,1,15) Recipt_num,
JA_JAIN23C1_XMLP_PKG.cf_1formula(a.register_id,a.opening_balance_qty,a.closing_balance_qty,a.inventory_item_id,a.slno) CF_Folio,
JA_JAIN23C1_XMLP_PKG.cf_folio_a2formula(a2.inventory_item_id || '/' || a2.slno) CF_folio_a2,
JA_JAIN23C1_XMLP_PKG.cf_item_tarrifformula(a.inventory_item_id