GST Query for Payables
SELECT jeh.period_name period_name, gll.NAME ledger_name,
a.concatenated_segments accounting_string,
jeh.doc_sequence_value doc_num, gstl.user_je_source_name je_source,
gctl.user_je_category_name je_category, jeb.NAME batch_name,
jeb.description batch_desc, jeh.NAME je_name, jeh.description je_desc,
jel.description line_desc, NULL h_desc,
jeh.external_reference external_ref, jeh.accrual_rev_flag acc_rev_flag,
jeh.currency_conversion_rate curr_conv_rate,
gll.currency_code func_curr_code, jeh.currency_code curr_code,
NVL (jel.accounted_dr, 0) accounted_dr,
NVL (jel.accounted_cr, 0) accounted_cr,
NVL (jel.accounted_dr, 0) – NVL (jel.accounted_cr, 0) net_amount,
NVL (jel.entered_dr, 0) – NVL (jel.entered_cr, 0) local_amnt,
NVL (jel.entered_dr, 0) local_amnt_dr,
NVL (jel.entered_cr, 0) local_amnt_cr,
jeh.currency_conversion_date curr_conv_date,
jeh.currency_conversion_type curr_conv_type,
jel.effective_date effective_date, jeh.posted_date posted_date,
jeh.balanced_je_flag bal_je_flag, oth.supp_name supp_cust_name,
oth.supp_code supp_cust_numb, NULL inv_cr_note_numb,
oth.po_number order_number, jel.reference_1 po_desciption,
jel.reference_1 project_number, jel.reference_1 item_number,
jel.reference_1 trans_ref_numb, oth.receipt_num receipt_num,
oth.transaction_type /* to_char(oth.transaction_date,’DD-MON-YYYY’)*/
transaction_type,
oth.boe_num boe_num, jel.code_combination_id code_combination_id,
jel.je_header_id header_id, jel.je_line_num line_num
FROM gl_code_combinations_kfv a,
gl_je_headers jeh,
gl_ledgers gll,
gl_je_lines jel,
gl_je_batches jeb,
gl_je_sources_tl gstl,
gl_je_categories_tl gctl,
(SELECT jta.tax_line_id, rt.transaction_type, jta.det_factor_id,
DECODE (jta.party_type,
‘Supplier’, asa.vendor_name,
‘Customer’, hp.party_name
) supp_name,
DECODE (jta.party_type,
‘Supplier’, asa.segment1,
‘Customer’, hp.party_number
) supp_code,
pha.segment1 po_number,
— NULL excise_invoice_no,
jta.trx_number receipt_num,
jta.trx_date receipt_date,
(SELECT TO_CHAR (jcbm.boe_id)
FROM jai_cmn_boe_matchings jcbm
WHERE jcbm.shipment_header_id =
rt.shipment_header_id
AND jcbm.shipment_line_id = rt.shipment_line_id
AND ROWNUM = 1) boe_num
FROM rcv_transactions rt,
jai_tax_lines_all jta,
— rcv_shipment_lines rsl,
po_headers_all pha,
ap_suppliers asa,
hz_cust_accounts hca,
hz_parties hp
WHERE /*jrt.transaction_id = 10026083
AND */ rt.shipment_header_id = jta.trx_id
AND rt.shipment_line_id = jta.trx_line_id
AND rt.transaction_id = jta.trx_loc_line_id
— AND rt.shipment_line_id = rsl.shipment_header_id
AND rt.po_header_id = pha.po_header_id(+)
AND asa.vendor_id(+) = rt.vendor_id
AND hca.cust_account_id(+) = jta.party_id
AND hp.party_id(+) = hca.party_id
AND jta.application_id = 707
/* AND jta.tax_line_id = 11221
AND jta.det_factor_id = 10941*/
UNION
SELECT jta.tax_line_id, rt.transaction_type, jta.det_factor_id,
DECODE (jta.party_type,
‘Supplier’, asa.vendor_name,
‘Customer’, hp.party_name
) supp_name,
DECODE (jta.party_type,
‘Supplier’, asa.segment1,
‘Customer’, hp.party_number
) supp_code,
pha.segment1 po_number,
— NULL excise_invoice_no,
jta.trx_number receipt_num,
jta.trx_date receipt_date,
(SELECT TO_CHAR (jbd.boe_doc_id)
FROM jai_boe_details jbd
WHERE jbd.boe_id = jcbm.boe_id
AND jbd.boe_detail_id = jcbm.boe_detail_id
AND ROWNUM = 1) boe_num
FROM rcv_transactions rt,
jai_tax_lines_all jta,
jai_cmn_boe_matchings jcbm,
po_headers_all pha,
ap_suppliers asa,
hz_cust_accounts hca,
hz_parties hp
WHERE /*jrt.transaction_id = 10026083
AND */ jcbm.boe_id = jta.trx_id
AND jcbm.boe_detail_id = jta.trx_line_id
AND rt.transaction_id = jcbm.transaction_id
AND rt.shipment_header_id = jcbm.shipment_header_id
AND rt.shipment_line_id = jcbm.shipment_line_id
AND rt.po_header_id = pha.po_header_id(+)
AND asa.vendor_id(+) = rt.vendor_id
AND hca.cust_account_id(+) = jta.party_id
AND hp.party_id(+) = hca.party_id
AND jta.application_id = 7000
AND jta.entity_code = ‘BILL_OF_ENTRY’
/* AND jta.tax_line_id = 11730
AND jta.det_factor_id = 11381*/
UNION
SELECT jta.tax_line_id, NULL transaction_type, jta.det_factor_id,
DECODE (jta.party_type,
‘Supplier’, asa.vendor_name,
‘Customer’, hp.party_name
) supp_name,
DECODE (jta.party_type,
‘Supplier’, asa.segment1,
‘Customer’, hp.party_number
) supp_code,
pha.segment1 po_number,
— NULL excise_invoice_no,
jta.trx_number receipt_num,
jta.trx_date receipt_date, aia.invoice_num boe_num
FROM jai_tax_lines_all jta,
ap_invoices_all aia,
ap_invoice_lines_all aila,
po_headers_all pha,
ap_suppliers asa,
hz_cust_accounts hca,
hz_parties hp
WHERE /*jrt.transaction_id = 10026083
AND */ aila.invoice_id = jta.trx_id
AND aila.line_number = jta.trx_line_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aila.po_header_id = pha.po_header_id(+)
AND asa.vendor_id(+) = aia.vendor_id
AND hca.cust_account_id(+) = jta.party_id
AND hp.party_id(+) = hca.party_id
AND jta.application_id = 200
/*AND jta.tax_line_id = 11120
AND jta.det_factor_id = 10871 */
AND jta.entity_code = ‘AP_INVOICES’
) oth
WHERE jeh.ledger_id = gll.ledger_id
AND jel.ledger_id = jeh.ledger_id
AND jel.ledger_id = :ledger_id
AND jeh.je_header_id = jel.je_header_id
AND jel.code_combination_id = a.code_combination_id
AND jeh.je_batch_id = jeb.je_batch_id
AND jeh.status = ‘P’
AND jeh.actual_flag = ‘A’
–AND gstl.user_JE_SOURCE_name = ‘Financials India’
–AND gctl.user_JE_CATEGORY_name IN ( ‘AP Validate’,’Bill of Entry’,’Return Vendor’,’Return Receive’,’PO Receiving’,’Delivery’,
— ‘Interorg Ship’,
— ‘Match Receipt’,
— ‘Payment Save’,
— ‘AP Accounting’,
— ‘AP Cancel’,
— ‘Correction’,
— ‘Payment Void’,
— ‘Prepay Apply’,
— ‘Prepay Unapply’
— )
AND jeh.je_source = gstl.je_source_name
AND jeh.je_category = gctl.je_category_name
AND a.segment1 BETWEEN NVL (:cp_segment1, a.segment1)
AND NVL (:cp_segment1_1, a.segment1)
AND a.segment4 BETWEEN NVL (:cp_segment4, a.segment4)
AND NVL (:cp_segment4_4, a.segment4)
AND TRUNC (jel.effective_date) BETWEEN NVL (:p_from_date,
TRUNC (jel.effective_date)
)
AND NVL (:p_to_date,
TRUNC (jel.effective_date)
)
–AND jeh.period_name IN (‘JUL-17’)
–AND jeh.DESCRIPTION = ‘Journal Import 233223247:’–‘Journal Import 233289126:’
AND oth.det_factor_id(+) = jel.reference_3
AND oth.tax_line_id(+) = jel.reference_4
