Introduction
This Post illustrates the steps required to PO Blanket details along with receipt and Indian tax details in Oracle EBS R12.
Script to PO Blanket details along with receipt and Indian tax details.
SELECT QUANTITY_BILLED,SHIP_TO_LOCATION,BILL_TO_LOCATION,RECEIPT_QUNT,QUANTITY_RECEIVED,RECEIPT_NUM,
ORGANIZATION_NAME,SEGMENT1,PO_QTY,PO_VAL,QUANTITY_COMMITTED,
CLOSED_CODE,CREATION_DATE,UNIT_MEAS_LOOKUP_CODE,
UNIT_PRICE,CURRENCY_CODE,QUANTITY,LINE_NUM,STATUS,VENDOR_NAME,VENDOR_SITE_CODE,ITEM_NAME,ITEM_DESCRIPTION,DESTINATION_TYPE_CODE,CONCATENATED_SEGMENTS,IGST_AMT,SGST_AMT,
CGST_AMT,NON_GST_AMT,NVL((IGST_AMT+SGST_AMT+CGST_AMT+NON_GST_AMT),0)TOTAL_TAX,nvl((PO_VAL+IGST_AMT+SGST_AMT+CGST_AMT+NON_GST_AMT),0)TOTAL_AMT FROM ( SELECT pll.QUANTITY_BILLED,
(select LOCATION from hz_cust_site_uses_all where site_use_id=ph.SHIP_TO_LOCATION_ID
)SHIP_TO_LOCATION,(select LOCATION from hz_cust_site_uses_all where site_use_id=ph.BILL_TO_LOCATION_ID
)BILL_TO_LOCATION,(select rt.quantity
from apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.transaction_type = ‘RECEIVE’
— AND rsh.receipt_num=’20051′
AND pl.po_line_id=rsl.po_line_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
— and
— group by rt.po_line_id,rt.ORGANIZATION_ID
)RECEIPT_QUNT,(select RSL.QUANTITY_RECEIVED
from apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.transaction_type = ‘RECEIVE’
— AND rsh.receipt_num=’20051′
AND pl.po_line_id=rsl.po_line_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
— and
— group by rt.po_line_id,rt.ORGANIZATION_ID
)QUANTITY_RECEIVED, (select RSh.receipt_num
from apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.transaction_type = ‘RECEIVE’
— AND rsh.receipt_num=’20051′
AND pl.po_line_id=rsl.po_line_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
— and
— group by rt.po_line_id,rt.ORGANIZATION_ID
)receipt_num,(select ORGANIZATION_NAME from org_organization_definitions where organization_id=pll.SHIP_TO_ORGANIZATION_ID)ORGANIZATION_NAME,
ph.segment1, (SELECT SUM (quantity_committed)
FROM po_lines_all
WHERE po_header_id = ph.po_header_id) po_qty,nvl((pll.quantity*unit_price),0) po_val,
pl.quantity_committed, pll.closed_code, ph.creation_date,
pl.unit_meas_lookup_code, pl.unit_price, currency_code, pll.quantity,
pl.line_num, po_headers_sv3.get_po_status (ph.po_header_id) status,
(SELECT vendor_name
FROM po_vendors
WHERE vendor_id = ph.vendor_id) vendor_name, (SELECT vendor_site_code
FROM po_vendor_sites_All
WHERE vendor_site_id = ph.vendor_site_id) vendor_site_code,
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = pl.item_id
AND organization_id = pll.SHIP_TO_ORGANIZATION_ID) item_name,
pl.item_description, pda.destination_type_code, concatenated_segments,
NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
–and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type = ‘RECEIVE’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE LIKE ‘IGST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
— and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type=’RETURN TO VENDOR’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE LIKE ‘IGST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id),0)IGST_AMT,
NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
–and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type = ‘RECEIVE’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE LIKE ‘SGST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
— and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type=’RETURN TO VENDOR’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE LIKE ‘SGST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id),0)SGST_AMT,
NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
–and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type = ‘RECEIVE’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE LIKE ‘CGST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
— and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type=’RETURN TO VENDOR’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE LIKE ‘CGST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id),0)CGST_AMT,
NVL( ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
–and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type = ‘RECEIVE’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE NOT LIKE ‘%GST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id)- ( select NVL(sum(unround_tax_amt_tax_curr),0)
FROM jai_tax_lines ,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl
where rsh.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_header_id=rsh.shipment_header_id
AND rsl.po_header_id=rt.po_header_id
AND rsl.po_line_id=rt.po_line_id
AND pl.po_line_id=rsl.po_line_id
AND rsl.po_line_location_id=rt.po_line_location_id
AND rt.ORGANIZATION_ID= pll.SHIP_TO_ORGANIZATION_ID
AND rt.transaction_type = ‘RECEIVE’
— and rsh.receipt_num=’20051′
— WHERE 1= 1
AND trx_type=’RETURN TO VENDOR’
AND entity_code = ‘RCV_TRANSACTION’
AND TAX_RATE_CODE NOT LIKE ‘%GST%’
AND trx_id = rsh.shipment_header_id
AND trx_line_id= rsl.shipment_line_id),0)NON_GST_AMT
FROM po_headers_all ph,
po_lines_all pl,
po_line_locations_all pll,
po_distributions_all pda,
gl_code_combinations_kfv gcc,
po_releases_all pra/*, ( SELECT DISTINCT j.trx_id, j.trx_line_id, J.DET_FACTOR_ID, — TAX_RATE_CODE,
nvl(TAX_RATE_PERCENTAGE,0) TAX_RATE_PERCENTAGE,
nvl((SELECT sum(unround_tax_amt_tax_curr)
FROM jai_tax_lines
WHERE 1= 1
AND trx_id = J.trx_id AND trx_line_id= j.trx_line_id AND DET_FACTOR_ID = J.DET_FACTOR_ID
AND TAX_RATE_CODE LIKE ‘IGST%’),0) IGST_TAX_AMT,
nvl((SELECT sum(unround_tax_amt_tax_curr)
FROM jai_tax_lines
WHERE 1= 1
AND trx_id = J.trx_id AND trx_line_id= j.trx_line_id AND DET_FACTOR_ID = J.DET_FACTOR_ID
AND TAX_RATE_CODE NOT LIKE ‘IGST%’ and TAX_RATE_CODE LIKE ‘SGST%’),0) SGST_TAX_AMT,
nvl((SELECT sum(unround_tax_amt_tax_curr)
FROM jai_tax_lines
WHERE 1= 1
AND trx_id = J.trx_id AND trx_line_id= j.trx_line_id AND DET_FACTOR_ID = J.DET_FACTOR_ID
AND TAX_RATE_CODE NOT LIKE ‘IGST%’ and TAX_RATE_CODE LIKE ‘CGST%’),0) CGST_TAX_AMT,
nvl((select sum(nvl(unround_tax_amt_tax_curr,0))
from jai_tax_lines where tax_rate_code not like ‘%GST%’
AND trx_id = j.trx_id AND trx_line_id = j.trx_line_id and DET_FACTOR_ID = j.DET_FACTOR_ID),0) NON_GST_TAX_AMT,
j.recoverable_flag
FROM jai_tax_lines j
WHERE 1= 1
— &LP_CREATION_DATE1
— AND trunc(trx_date) between :P_FDATE and :P_TDATE
AND j.trx_type = ‘RECEIVE’
AND j.entity_code = ‘RCV_TRANSACTION’
AND J.TAX_RATE_CODE LIKE ‘%GST%’
)TAX,rcv_shipment_lines rsl*/
WHERE 1 = 1
AND pll.po_header_id = ph.po_header_id
AND pll.po_line_id = pl.po_line_id
AND ph.po_header_id = pl.po_header_id
AND pda.po_header_id = ph.po_header_id
AND pda.po_line_id = pl.po_line_id
AND pda.line_location_id = pll.line_location_id
— AND ph.segment1 = ‘2189689’
AND gcc.code_combination_id = pda.code_combination_id
AND ph.po_header_id = pra.po_header_id
AND ph.type_lookup_code IN (‘BLANKET’)
and ph.vendor_id=NVL(:p_vendor_id,ph.vendor_id)
and ph.po_header_id=NVL(:p_po_header, ph.po_header_id)
–AND TRUNC(PH.CREATION_DATE) BETWEEN :P_ORIG_FROM_DT AND :P_ORIG_TO_DT
&lp_creation_date
AND pll.po_release_id = pra.po_release_id
ORDER BY line_num)
where 1=1
&LP_PENDING_QTY;
What we expect in the script.
This script helps us to comprehend how
PO Blanket details along with receipt and Indian tax details. A couple of tables which is being used in the scripts are
po_headers_all,po_lines_all,po_line_locations_all, po_distributions_all,gl_code_combinations_kfv etc.
Summary
This Post described the script PO Blanket details along with receipt and Indian tax details in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
