Query for PO Reconciliation for India GST
SELECT poh.segment1
PO_Number,
pol.line_num
Po_line_number,
gcc.segment2,
SUM (NVL (rrsl.ACCOUNTED_DR, 0))
rcv_acc_dr,
SUM (NVL (rrsl.ACCOUNTED_CR, 0))
rcv_acc_cr,
SUM (NVL (rrsl.ACCOUNTED_DR, 0)) - SUM (NVL (rrsl.ACCOUNTED_CR, 0))
rcv_period_act,
SUM (NVL (xal.ACCOUNTED_DR, 0))
xla_acc_dr,
SUM (NVL (xal.ACCOUNTED_CR, 0))
xla_acc_cr,
SUM (NVL (xal.ACCOUNTED_DR, 0)) - SUM (NVL (xal.ACCOUNTED_CR, 0))
PO_Item_AMount,
(SELECT SUM (NVL (ENTERED_DR, 0)) - SUM (NVL (ENTERED_CR, 0))
FROM JAI_RCV_JOURNAL_ENTRIES rcv_tax_j,
gl_code_combinations_kfv glcc,
rcv_shipment_lines rsl1
WHERE rcv_tax_j.shipment_line_id = rsl1.shipment_line_id
AND glcc.CODE_COMBINATION_ID = rcv_tax_j.CODE_COMBINATION_ID
AND glcc.segment1 = '11550'
AND glcc.segment2 = '200035'
AND po_header_id = poh.po_header_id --365141--2003
AND po_line_id = pol.po_line_id) --353731--2005)
PO_tax_PRE_GST_IND,
(SELECT NVL (SUM (ACCOUNTED_DR), 0) - NVL (SUM (ACCOUNTED_CR), 0)
FROM jai_tax_journal_entries a,
gl_code_combinations_kfv glcc,
jai_tax_det_factors tax_det,
rcv_shipment_lines rsl2
WHERE glcc.code_combination_id = a.code_combination_id
AND glcc.segment1 = '11550'
AND glcc.segment2 = '200035'
AND rsl2.po_header_id = poh.po_header_id --365141--2003
AND rsl2.po_line_id = pol.po_line_id
AND a.tax_det_factor_id = tax_det.DET_FACTOR_ID
AND TRX_ID = rsl2.SHipment_header_id
AND trx_line_id = rsl2.Shipment_line_id)PO_TAX_POST_GST_IND,
(SELECT SUM (WRITE_OFF_AMOUNT)
FROM cst_write_offs write_off,
po_distributions_all pdo3,
gl_code_combinations_kfv glcc
WHERE write_off.PO_DISTRIBUTION_ID = pdo3.PO_DISTRIBUTION_ID
AND glcc.CODE_COMBINATION_ID = write_off.ACCRUAL_ACCOUNT_ID
AND glcc.segment1 = '11550'
AND glcc.segment2 = '200035'
AND po_header_id = poh.po_header_id
AND po_line_id = pol.po_line_id)
po_write_off_amt,
(SELECT SUM (NVL (xdl.UNROUNDED_ACCOUNTED_DR, 0))
- SUM (NVL (xdl.UNROUNDED_ACCOUNTED_CR, 0))
FROM ap_invoice_distributions_all apid,
ap_invoices_all api,
po_distributions_all pdo2,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations_kfv glcc
WHERE 1 = 1
AND pdo2.po_header_id = poh.po_header_id --'11550000763'
AND pdo2.po_line_id = pol.po_line_id --1
AND apid.po_distribution_id = pdo2.po_distribution_id
AND apid.line_type_lookup_code = 'ACCRUAL'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =
apid.invoice_distribution_id
AND xdl.application_id = 200
AND xdl.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xal.code_combination_id = glcc.code_combination_id
AND api.invoice_id = apid.invoice_id
--AND api.cancelled_date IS NULL
AND glcc.segment1 = '11550'
AND glcc.segment2 = '200035')
inv_amt,
(SELECT SUM (NVL (xdl.UNROUNDED_ACCOUNTED_DR, 0))
- SUM (NVL (xdl.UNROUNDED_ACCOUNTED_CR, 0))
FROM ap_invoice_distributions_all apid,
ap_invoices_all api,
po_distributions_all pdo2,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations_kfv glcc
WHERE 1 = 1
AND pdo2.po_header_id = poh.po_header_id --'11550000763'
AND pdo2.po_line_id = pol.po_line_id --1
AND apid.po_distribution_id = pdo2.po_distribution_id
AND apid.line_type_lookup_code = 'MISCELLANEOUS'
AND xdl.SOURCE_DISTRIBUTION_ID_NUM_1 =
apid.invoice_distribution_id
AND xdl.application_id = 200
AND xdl.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xal.code_combination_id = glcc.code_combination_id
AND api.invoice_id = apid.invoice_id
--AND api.cancelled_date IS NULL
AND glcc.segment1 = '11550'
AND glcc.segment2 = '200035')
inv_tax_amt
FROM rcv_receiving_sub_ledger rrsl,
po_headers_all poh,
rcv_transactions rcvt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_lines_all pol,
xla_distribution_links xdl,
xla_ae_headers xah,
xla_ae_lines xal,
gl_code_combinations_kfv gcc
WHERE 1=1 --AND rrsl.reference4 = '11550000763'
AND rrsl.ACCOUNTING_LINE_TYPE = 'Accrual'
--AND rrsl.ACCOUNTING_LINE_TYPE = 'MISCELLANEOUS'
AND rrsl.SET_OF_BOOKS_ID = 2177
AND poh.po_header_id = rrsl.REFERENCE2
AND rcvt.TRANSACTION_ID = rrsl.rcv_TRANSACTION_ID
AND rsh.SHIPMENT_HEADER_ID = rcvt.SHIPMENT_HEADER_ID
AND rsh.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND rsl.SHIPMENT_line_ID = rcvt.SHIPMENT_line_ID
AND rcvt.po_header_id = poh.po_header_id
AND rcvt.po_line_id = pol.po_line_id
AND xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id
AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
AND xah.ae_header_id = xdl.ae_header_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.code_combination_id = gcc.code_combination_id
AND gcc.segment1 = '11550'
AND gcc.segment2 = '200035'
GROUP BY poh.segment1,
pol.line_num,
gcc.segment2,
poh.po_header_id,
pol.po_line_id
ORDER BY poh.segment1, pol.line_num, gcc.segment2
No comments:
Post a Comment