Purchase Register SQL Query in Oracle Fusion
In this post , I am going to share the Purchase Register SQL Query in Oracle Fusion. This Purchase register sql query helps to extract the complete Purchase order Details in oracle fusion. This Purchase register having complete details about Purchase order details , Purchase receipt details , Purchase Order Tax and Invoice details in oracle fusion. This is the complete sql query helps to extract the complete informations from Purchase Orders , PO Receipts , AP Invoices. We can use this sql query to develop the Purchase order reports in oracle fusion. Please find below the details about Purchase Register SQL Query in Oracle Fusion.
 |
| Purchase Register SQL Query in Oracle Fusion |
14 Tables used by Purchase Register SQL Query in Oracle Fusion
1.rcv_shipment_headers
2.rcv_transactions
3.po_headers_all
4.po_lines_all
5.rcv_shipment_lines
6.egp_system_items
7.poz_supplier_sites_all_m
8.poz_suppliers_v
9.hz_parties
10.hr_operating_units
11.ap_invoices_all
12.rcv_transactions
13.ap_invoice_lines_all
14.zx_lines
Detail Purchase Register SQL Query in Oracle Fusion
select rownum sr_no,p_rep_type,bu_name,location_name,
main_code,sub_code,
vendor_name,bill_no,bill_date,mrn_no,mrn_date,
to_char(stock_in_date,'DD-MON-YYYY') stock_in_date,
item_code,item_desc,terms,quantity,
correct_qty,reject_qty,accept_qty,
(accept_qty-reject_qty) deliver_qty,uom,
po_no,po_unit_price,currency_code,ex_rate,
invoice_qty,invoice_rate,invoice_amount,
(case when nvl (accept_qty, 0) = 0 then 0
else cgst_rate end) cgst_rate,
(case when nvl (accept_qty, 0) = 0 then 0
else sgst_rate end) sgst_rate,
(case when nvl (accept_qty, 0) = 0 then 0
else igst_rate end) igst_rate,
((nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)) value_inr,
round (((nvl(po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl(ex_rate, 1)
* nvl(cgst_rate, 0))/ 100,2) cgst_value,
round (((nvl(po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl(ex_rate, 1)
* nvl(sgst_rate, 0))/ 100,2) sgst_value,
round (((nvl(po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl(ex_rate, 1)
* nvl(igst_rate, 0))/ 100,2) igst_value,
( round (( (nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)
* nvl (cgst_rate, 0))/ 100,2)
+ round (( (nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)
* nvl (sgst_rate, 0))/ 100,2)
+ round (( (nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)
* nvl (igst_rate, 0))/ 100,2)) total_tax,
(( round (((nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)
* nvl (cgst_rate, 0))/ 100,2)
+ round (((nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)
* nvl (sgst_rate, 0))/ 100,2)
+ round (((nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)
* nvl (igst_rate, 0))/ 100,2))
+ ((nvl (po_unit_price, 0) * (nvl (accept_qty, 0)))
* nvl (ex_rate, 1)))total_value,
voucher_no,voucher_date,item_linked_ac_head,
transaction_id,po_line_location_id,
shipment_header_id,shipment_line_id
from (
select :p_rep_type p_rep_type,
hu.name bu_name,
hrl.location_name,
ap.segment1 main_code,
apsa.vendor_site_code sub_code,
ap.vendor_name,
rsh.shipment_num bill_no,
to_char(rsh.shipped_date,'DD-MON-YYYY') bill_date,
rsh.receipt_num mrn_no,
to_char(rct.transaction_date,'DD-MON-YYYY') mrn_date,
(select rct2.transaction_date
from rcv_transactions rct2
where rct2.shipment_line_id = rct.shipment_line_id
and rct2.transaction_type = 'DELIVER'
and rownum = 1) stock_in_date,
esi.item_number item_code,
esi.description item_desc,
(select apt.name
from ap_terms apt
where apt.term_id = poa.terms_id) terms,
rct.quantity,
nvl ((select sum (rct1.quantity)
from rcv_transactions rct1
where rct1.shipment_header_id =rct.shipment_header_id
and rct1.shipment_line_id =rct.shipment_line_id
and transaction_type = 'CORRECT'
and parent_transaction_id =rct.transaction_id),0) correct_qty,
nvl((select sum (rct1.quantity)
from rcv_transactions rct1
where rct1.shipment_header_id = rct.shipment_header_id
and rct1.shipment_line_id = rct.shipment_line_id
and transaction_type in ('REJECT')),0)
- abs (nvl ((select sum (rc1.quantity)
from rcv_transactions rct1,
rcv_transactions rc1
where rct1.shipment_header_id = rct.shipment_header_id
and rct1.shipment_line_id = rct.shipment_line_id
and rct1.transaction_type in ('RETURN TO VENDOR')
and rc1.parent_transaction_id = rct1.transaction_id
and rc1.transaction_type = 'CORRECT'
and rct1.shipment_header_id = rc1.shipment_header_id
and rct1.shipment_line_id = rc1.shipment_line_id),0))reject_qty,
(nvl (rct.quantity, 0)
- abs (nvl ((select sum (rct1.quantity)
from rcv_transactions rct1
where rct1.shipment_header_id = rct.shipment_header_id
and rct1.shipment_line_id = rct.shipment_line_id
and transaction_type = 'CORRECT'
and parent_transaction_id = rct.transaction_id),0))
- abs (nvl ((select sum (rct1.quantity)
from rcv_transactions rct1
where rct1.shipment_header_id = rct.shipment_header_id
and rct1.shipment_line_id = rct.shipment_line_id
and transaction_type in ('RETURN TO VEN')),0))
+ abs (nvl ((select sum (rc1.quantity)
from rcv_transactions rct1, rcv_transactions rc1
where rct1.shipment_header_id = rct.shipment_header_id
and rct1.shipment_line_id = rct.shipment_line_id
and rct1.transaction_type in ('RETURN TO VENDOR')
and rc1.parent_transaction_id = rct1.transaction_id
and rc1.transaction_type = 'CORRECT'
and rct1.shipment_header_id = rc1.shipment_header_id
and rct1.shipment_line_id = rc1.shipment_line_id),0))) accept_qty,
pla.uom_code uom,
poa.segment1 po_no,
rct.po_unit_price,
poa.currency_code,
nvl(rct.currency_conversion_rate,
nvl((select cmt.currency_conversion_rate from cmr_rcv_transactions cmt
where cmt.external_system_reference = 'FUSION'
and cmt.external_system_ref_id = rct.transaction_id
and rownum = 1),1)) as ex_rate,
(select apa.quantity_invoiced
from ap_invoice_lines_all apa, ap_invoices_all api
where apa.po_header_id = rct.po_header_id
and apa.po_line_id = rct.po_line_id
and api.invoice_type_lookup_code = 'STANDARD'
and apa.line_type_lookup_code='ITEM'
and apa.rcv_transaction_id = rct.transaction_id
and api.invoice_id = apa.invoice_id
and rownum = 1) invoice_qty,
(select apa.unit_price
from ap_invoice_lines_all apa, ap_invoices_all api
where apa.po_header_id = rct.po_header_id
and apa.po_line_id = rct.po_line_id
and api.invoice_type_lookup_code = 'STANDARD'
and apa.line_type_lookup_code='ITEM'
and apa.rcv_transaction_id = rct.transaction_id
and api.invoice_id = apa.invoice_id
and rownum = 1) invoice_rate,
(select apa.amount
from ap_invoice_lines_all apa, ap_invoices_all api
where apa.po_header_id = rct.po_header_id
and apa.po_line_id = rct.po_line_id
and api.invoice_type_lookup_code = 'STANDARD'
and apa.line_type_lookup_code='ITEM'
and apa.rcv_transaction_id = rct.transaction_id
and api.invoice_id = apa.invoice_id
and rownum = 1) invoice_amount,
(select zx.tax_rate
from zx_lines zx
where 1=1--zx.trx_id = rct.transaction_id
--and zx.trx_line_id = rct.po_line_location_id
and zx.entity_code = 'AP_INVOICES'
and zx.applied_to_line_id = rct.po_line_location_id
and zx.tax like '%CGST%' and rownum = 1) cgst_rate,
(select zx.unrounded_tax_amt
from zx_lines zx
where 1=1--zx.trx_id = rct.transaction_id
--and zx.trx_line_id = rct.po_line_location_id
and zx.entity_code = 'AP_INVOICES'
and zx.applied_to_line_id = rct.po_line_location_id
and zx.tax like '%CGST%' and rownum = 1) cgst_value,
(select zx.tax_rate
from zx_lines zx
where 1=1--zx.trx_id = rct.transaction_id
--and zx.trx_line_id = rct.po_line_location_id
and zx.entity_code = 'AP_INVOICES'
and zx.applied_to_line_id = rct.po_line_location_id
and zx.tax like '%SGST%' and rownum = 1) sgst_rate,
(select zx.unrounded_tax_amt
from zx_lines zx
where 1=1--zx.trx_id = rct.transaction_id
--and zx.trx_line_id = rct.po_line_location_id
and zx.entity_code = 'AP_INVOICES'
and zx.applied_to_line_id = rct.po_line_location_id
and zx.tax like '%SGST%' and rownum = 1) sgst_value,
(select zx.tax_rate
from zx_lines zx
where 1=1--zx.trx_id = rct.transaction_id
--and zx.trx_line_id = rct.po_line_location_id
and zx.entity_code = 'AP_INVOICES'
and zx.applied_to_line_id = rct.po_line_location_id
and zx.tax like '%IGST%' and rownum = 1) igst_rate,
(select zx.unrounded_tax_amt
from zx_lines zx
where 1=1--zx.trx_id = rct.transaction_id
--and zx.trx_line_id = rct.po_line_location_id
and zx.entity_code = 'AP_INVOICES'
and zx.applied_to_line_id = rct.po_line_location_id
and zx.tax like '%IGST%' and rownum = 1) igst_value,
(select api.doc_sequence_value
from ap_invoice_lines_all apa, ap_invoices_all api
where apa.po_header_id = rct.po_header_id
and apa.po_line_id = rct.po_line_id
and apa.rcv_transaction_id = rct.transaction_id
and api.invoice_type_lookup_code = 'STANDARD'
and api.invoice_id = apa.invoice_id
and rownum = 1) voucher_no,
(select to_char (api.creation_date, 'DD-MON-YYYY')
from ap_invoice_lines_all apa, ap_invoices_all api
where apa.po_header_id = rct.po_header_id
and apa.po_line_id = rct.po_line_id
and apa.rcv_transaction_id = rct.transaction_id
and api.invoice_type_lookup_code = 'STANDARD'
and api.invoice_id = apa.invoice_id
and rownum = 1) voucher_date,
(select description from fnd_flex_values_vl fv
where fv.flex_value_set_id = '100002'
and (select gcc.segment5
from gl_code_combinations gcc,po_distributions_all pda
where 1 = 1
and gcc.code_combination_id = pda.code_combination_id
and pda.po_header_id = rct.po_header_id
and pda.po_line_id = rct.po_line_id
and rownum = 1) = fv.flex_value and rownum = 1) item_linked_ac_head,
rct.transaction_id,
rct.po_line_location_id,
rsh.shipment_header_id,
rsl.shipment_line_id
from rcv_shipment_headers rsh,
rcv_transactions rct,
po_headers_all poa,
po_lines_all pla,
rcv_shipment_lines rsl,
egp_system_items esi,
poz_supplier_sites_all_m apsa,
poz_suppliers_v ap,
hz_parties hzp,
hr_operating_units hu,
(select distinct lo.location_code,
lo.location_name,
lo.inventory_organization_id,
lo.address_line_1,
lo.address_line_2,
lo.town_or_city,
lo.postal_code,
lo.region_2,
lo.country
from hr_locations lo,
(select inv.organization_id
from hr_operating_units hr, inv_org_parameters inv
where hr.organization_id = inv.business_unit_id
and trunc (hr.date_to) >= trunc (sysdate)) ou
where lo.inventory_organization_id in (ou.organization_id)) hrl
where 1 = 1
and rsh.shipment_header_id = rct.shipment_header_id
and rct.transaction_type = 'RECEIVE'
and poa.po_header_id = rct.po_header_id
and rsl.shipment_line_id = rct.shipment_line_id
and pla.po_header_id = poa.po_header_id
and pla.po_header_id = rct.po_header_id
and pla.po_line_id = rsl.po_line_id
and pla.po_line_id = rct.po_line_id
and esi.organization_id = rsl.to_organization_id
and esi.inventory_item_id = rsl.item_id
and apsa.vendor_id = rsh.vendor_id
and apsa.vendor_site_id = rsh.vendor_site_id
and ap.vendor_id = rct.vendor_id
and hzp.party_id = ap.party_id
and poa.prc_bu_id = hu.organization_id
and rct.organization_id = hrl.inventory_organization_id
and ((poa.currency_code = 'INR' and :p_rep_type = 'Domestic')
or (poa.currency_code != 'INR' and :p_rep_type = 'Imported'))
and rsl.to_organization_id = (select inv.organization_id
from inv_org_parameters inv,hr_operating_units hu
where hu.organization_id = inv.business_unit_id
and inv.organization_id = rsl.to_organization_id
and hu.name = nvl(:p_org,hu.name))
and (ap.vendor_name in (:p_vendor_name) or 'All' in (:p_vendor_name || 'All'))
and (esi.item_number in (:p_item_code) or 'All' in (:p_item_code || 'All'))
)
where to_char(stock_in_date) >= :p_from_date
and to_char(stock_in_date) <= :p_to_date
 |
| Purchase Register SQL Query in Oracle Fusion |
0 comments:
Post a Comment