Introduction
This Post Is Described about the RMA Receipt Query-GST In oracle apps R12.
select orgid,ORGCODE, ORGNAME, account_number, party_name,city, LINE_NUMBER, trx_number,trx_date, location, src_number,src_date,rma_gross,TAX_RATE_PERCENTAGE,
interface_header_attribute2,reason_code,CGST,SGST,IGST,VAT,CST,(nvl(rma_gross,0) + nvl(CGST,0) + nvl(SGST,0) + nvl(IGST,0) + nvl(VAT,0) + nvl(CST,0)) Total
from( SELECT orgid, ORGCODE, account_number, party_name, city, LINE_NUMBER, trx_number,trx_date,
location, src_number,src_date,rma_gross,TAX_RATE_PERCENTAGE,ORGNAME,
interface_header_attribute2,reason_code,
MAX(CGST) CGST,MAX(SGST) SGST,MAX(IGST) IGST,MAX(VAT) VAT,MAX(CST) CST
FROM(
SELECT orgid, ORGCODE, account_number, party_name,city,LINE_NUMBER,trx_number,TRUNC(trx_date) trx_date ,
location, src_number,TRUNC(src_date) src_date,rma_gross,TAX_RATE_PERCENTAGE,ORGNAME,
interface_header_attribute2,reason_code,
DECODE(SUBSTR(tax_rate_code,1,4),’CGST’,UNROUND_TAX_AMT_FUN_CURR) CGST,
DECODE(SUBSTR(tax_rate_code,1,4),’SGST’,UNROUND_TAX_AMT_FUN_CURR) SGST,
DECODE(SUBSTR(tax_rate_code,1,4),’IGST’,UNROUND_TAX_AMT_FUN_CURR) IGST,
DECODE(SUBSTR(tax_rate_code,1,3),’VAT’,UNROUND_TAX_AMT_FUN_CURR) VAT,
DECODE(SUBSTR(tax_rate_code,1,3),’CST’,UNROUND_TAX_AMT_FUN_CURR) CST
FROM(
select orgid, (SELECT ORGANIZATION_CODE FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_ID=orgid) ORGCODE,
(SELECT ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_ID=orgid) ORGNAME,
account_number,party_name,city,
crn1.LINE_NUMBER,crn1.trx_number,crn1.trx_date,
location,
rma_gross, TAX_RATE_CODE,TAX_RATE_PERCENTAGE,UNROUND_TAX_AMT_FUN_CURR,
shipment_header_id,transaction_id,
src_number,src_date,CRN1.interface_header_attribute2,CRN1.reason_code,
return_attribute1,
bill_to_customer_id,line_id
from (
select a.interface_header_attribute10 orgid, h.account_number,B.LINE_NUMBER,A.interface_header_attribute2,A.reason_code,
decode(i.organization_name_phonetic,null,i.party_name,i.organization_name_phonetic) party_name,i.city,
a.trx_number, trunc(a.trx_date) trx_date,
c.location,
abs(b.revenue_amount) rma_gross,
j.return_attribute1,
a.bill_to_customer_id,
substr(h.account_number,1,3) div,
a.attribute3 tax_adj,
j.line_id
from
ra_customer_trx_all a,
ra_customer_trx_lines_all b,
hz_cust_site_uses_all c,
hz_cust_accounts h,
hz_parties i,
oe_order_lines_all j
where a.cust_trx_type_id in (‘2′,’1001′)
and a.interface_header_attribute10 = :P_ORG_ID
and a.trx_date between :p_from_dt and :p_to_dt
and length(account_number)=10
—and substr(h.account_number,1,5) like :div||’%’
and (upper(a.interface_header_attribute2) not like ‘%DSIN%’ or upper(a.reason_code) not like ‘%DSIN%’)
and b.line_type = ‘LINE’
—AND A.TRX_NUMBER=’12421700002′
and a.interface_header_context<>’LEGACY’
and a.bill_to_customer_id=h.cust_account_id
and a.customer_trx_id = b.customer_trx_id
and a.ship_to_site_use_id=c.site_use_id
and h.party_id=i.party_id
and b.INTERFACE_LINE_ATTRIBUTE6 = j.line_id
) crn1
left outer join
(
select a.shipment_header_id,a.transaction_id,a.src_number,a.src_date,
a.interface_header_attribute2,a.reason_code,
a.trx_number,a.trx_date,a.organization_id,oe_order_line_id,
TAX_RATE_CODE,TAX_RATE_PERCENTAGE,UNROUND_TAX_AMT_FUN_CURR
from(
select e.shipment_header_id,a.transaction_id,f.trx_number,f.trx_date,
e.receipt_num src_number,F.interface_header_attribute2,F.reason_code,
trunc(a.transaction_date) src_date,
a.organization_id,b.oe_order_line_id,a.SHIPMENT_LINE_ID
from rcv_transactions a,
rcv_shipment_lines b,
oe_order_headers_all c,
oe_order_lines_all d,
rcv_shipment_headers e,
ra_customer_trx_all f,
ra_customer_trx_lines_all g ,
where a.source_document_code=’RMA’
— and a.transaction_type=’DELIVER’
AND a.organization_id = :P_ORG_ID
and a.shipment_header_id=b.shipment_header_id
and a.shipment_line_id=b.shipment_line_id
and a.oe_order_header_id=c.header_id
and c.header_id=d.header_id
and a.oe_order_header_id=d.header_id
and a.oe_order_line_id=d.line_id
and b.item_id=d.inventory_item_id
and d.ordered_item=i.appscode
—- and j.tax_regime_id=10000
and d.line_id=g.interface_line_attribute6
and c.order_number=g.interface_line_attribute1
and g.line_type = ‘LINE’
—– and f.trx_number=’12111700007′
— AND f.TRX_NUMBER=’12421700002′
—and tax_regime_id=10000
—-AND E.receipt_num=’210443′
and a.shipment_header_id=e.shipment_header_id
and f.trx_date between :p_from_dt and :p_to_dt
and f.customer_trx_id = g.customer_trx_id
and f.cust_trx_type_id in (‘2′,’1001′)
AND A.TRANSACTION_TYPE=’RECEIVE’
and f.interface_header_attribute2 is not null
and nvl(upper(f.interface_header_attribute2),’X’) not like ‘%DSIN%’and upper(nvl(f.reason_code, ‘X’)) not like ‘%DSIN%’
) a
left outer join
JAI_TAX_LINES_ALL J
on( ENTITY_CODE=’RCV_TRANSACTION’
AND A.SHIPMENT_HEADER_ID=J.TRX_ID
AND A.SHIPMENT_LINE_ID=J.TRX_LINE_ID
AND A.TRANSACTION_ID=J.TRX_LOC_LINE_ID
)
) src
on src.trx_number = crn1.trx_number and src.trx_date=crn1.trx_date and src.organization_id=crn1.orgid and crn1.line_id=src.oe_order_line_id
)
)
GROUP BY orgid,ORGCODE,account_number,party_name,city,LINE_NUMBER,trx_number,trx_date,ORGNAME,
location, src_number,src_date,rma_gross,TAX_RATE_PERCENTAGE,interface_header_attribute2,reason_code
)
Summery
This Post described the script about to RMA Receipt Query-GST in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at Doyen.ebiz@staging.doyensys.com or use the comment section below to ask your questions.
