SQL
QUERY for CGST and IGST IN ORACLE CLOUD FUSION R13
SELECT TO_CHAR(rcta.trx_date,
'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') trx_date
,TO_CHAR(ada.gl_date,
'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') acc_date
,rcta.trx_number trx_num
,rctta.name
trx_type
,hp.party_name
customer
,hp.state
state
,hps.party_site_name
site_name
,hca.account_number
acc_number
,hca.account_name
acc_name
,rctla.user_defined_fisc_class
hsn_ssc_code
,NVL(zptp.rep_registration_number, 'Not
Available')
tan_number
,(SELECT zr.registration_number
FROM zx_registrations zr
,zx_regimes_b zrb
WHERE zptp.party_tax_profile_id = zr.party_tax_profile_id(+)
AND zr.tax_regime_code = zrb.tax_regime_code(+)
AND zrb.regime_type_flag != 'W')
gst_number
,rctla.product_category
tax_rate
,(apsa.amount_due_original -
apsa.tax_original)
invoice_value
,apsa.tax_original
gst_amount
,apsa.amount_due_original
total
,CASE
WHEN rctta.name = 'INV_PROCESSING_FEE'
OR rctta.name = 'INV_PENALTY' OR rctta.name = 'INV_OTHER_GST'
THEN
CASE
WHEN hp.state != 'Meghlay'
THEN
apsa.tax_original
ELSE
0
END
ELSE
0
END
igst_bfs
,CASE
WHEN rctta.name =
'INV_PROCESSING_FEE' OR rctta.name = 'INV_PENALTY' OR rctta.name =
'INV_OTHER_GST'
THEN
CASE
WHEN hp.state != 'Meghlay'
THEN
0
ELSE
apsa.tax_original/2
END
ELSE
0
END
cgst_bfs
,CASE
WHEN rctta.name = 'INV_PROCESSING_FEE'
OR rctta.name = 'INV_PENALTY' OR rctta.name = 'INV_OTHER_GST'
THEN
CASE
WHEN hp.state != 'Meghlay'
THEN
0
ELSE
apsa.tax_original/2
END
ELSE
0
END
sgst_bfs
,CASE
WHEN rctta.name =
'INV_PROFESSIONAL_FEE' OR rctta.name = 'INV_GUARANTEE_FEE'
THEN
CASE
WHEN hp.state != 'Meghlay'
THEN
apsa.tax_original
ELSE
0
END
ELSE
0
END
igst_mcs
,CASE
WHEN rctta.name =
'INV_PROFESSIONAL_FEE' OR rctta.name = 'INV_GUARANTEE_FEE'
THEN
CASE
WHEN hp.state != 'Meghlay'
THEN
0
ELSE
apsa.tax_original/2
END
ELSE
0
END
cgst_mcs
,CASE
WHEN rctta.name =
'INV_PROFESSIONAL_FEE' OR rctta.name = 'INV_GUARANTEE_FEE'
THEN
CASE
WHEN hp.state != 'Meghlay'
THEN
0
ELSE
apsa.tax_original/2
END
ELSE
0
END sgst_mcs
,NVL2(gcc.segment1, gcc.segment1
||'.'||
gcc.segment2
||'.'||
gcc.segment3
||'.'||
gcc.segment4
||'.'||
gcc.segment5
||'.'||
gcc.segment6
||'.'||
gcc.segment7
||'.'||
gcc.segment8
||'.'||
gcc.segment9
||'.'||
gcc.segment10, NULL)
code_combination
FROM ra_customer_trx_all
rcta
,ra_cust_trx_types_all rctta
,ra_customer_trx_lines_all rctla
,ra_cust_trx_line_gl_dist_all ada
,gl_code_combinations gcc
,hz_cust_accounts hca
,hz_cust_site_uses_all hcsu
,hz_cust_acct_sites_all hcas
,hz_parties hp
,hz_party_sites hps
,zx_party_tax_profile zptp
,ar_payment_schedules_all apsa
WHERE rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
AND rctta.name
IN ('INV_PROCESSING_FEE'
,'INV_PROFESSIONAL_FEE'
,'INV_GUARANTEE_FEE'
,'INV_PENALTY'
,'INV_OTHER_GST'
)
AND rcta.customer_trx_id
= rctla.customer_trx_id
AND rctla.line_type
= 'LINE'
AND rctla.customer_trx_line_id
= ada.customer_trx_line_id
AND ada.code_combination_id = gcc.code_combination_id(+)
AND rcta.bill_to_customer_id
= hca.cust_account_id
AND rcta.complete_flag
= 'Y'
AND hca.party_id
= hp.party_id
AND rcta.bill_to_site_use_id
= hcsu.site_use_id
AND hcsu.cust_acct_site_id
= hcas.cust_acct_site_id
AND hcas.party_site_id
= hps.party_site_id
AND hps.party_site_id
= zptp.party_id(+)
AND rcta.customer_trx_id
= apsa.customer_trx_id(+)
AND rcta.org_id
= apsa.org_id(+)
AND ada.gl_date
BETWEEN NVL(:p_from_acc_date, ada.gl_date) AND
NVL(:p_to_acc_date,ada.gl_date)
order by rcta.trx_date,
rcta.trx_number ,rctta.name
,hp.party_name ,hp.state ,hps.party_site_name
,hca.account_number
,rctla.user_defined_fisc_class
ASC
No comments:
Post a Comment