GRN Dump /Po Register Report -PO,Receipts,AP / Excel
SELECT
pha.segment1 "PO Number",
To_char(pha.creation_date, 'DD-MM-YYYY') "PO Creation Date",
pha.currency_code "PO Currency",
pha.created_by "PO Created By",
pha.rate "PO Exchange Rate",
pos.segment1 "Supplier Code",
pos.vendor_name "Supplier Name",
pla.line_num "PO Line Number",
pla.attribute1 "NFA Number",
pla.quantity "PO Quantity",
pla.note_to_vendor "PO Specific Instruction",
pla.quantity * pla.unit_price "PO Gross Amount",
pda.recoverable_tax "PO GST Amt",
pda.recoverable_tax + (pla.quantity * pla.unit_price) "PO Amount",
igst.igst_rate "PO IGST Percent",
igst.igst_amount "PO IGST Amt",
cgst.cgst_rate "PO CGST Percent",
cgst.cgst_amount "PO CGST Amt",
sgst.sgst_rate "PO SGST Percent",
sgst.sgst_amount "PO SGST Amt",
ship.GRN_No "GRN No",
ship.GRN_Date "GRN Date",
ship.GRN_Quantity "GRN Quantity",
ship.GRN_Created "GRN Created",
ship.GRN_Create_User "GRN Create_User",
ship.Grn_Rev_Number "Grn Rev Number",
ship.Grn_Rev_Date "Grn Rev Date",
ship.Rec_Created_By "Rec Created By",
ship.GRN_Remarks "GRN Remarks",
PO.Grn_Currency_Code "Grn_Currency_Code",
grn.GRN_amount "GRN_amount",
PO.GRN_GST_Amt "GRN_GST_Amt",
PO.AP_Invoice_Num "AP Invoice Num",
PO.AP_Vouch_No "AP Vouch No",
PO.Invoice_Line_Number "Invoice Line Number",
NVL(igst_invoice.igst_amount,0) + NVL(cgst_invoice.cgst_amount,0) + NVL(sgst_invoice.sgst_amount,0) + PO.GRN_DR_AMT "AP Line Amount",
PO.AP_Voucher_Created "AP Voucher Created",
PO.AP_invoice_date "AP invoice date",
PO.AP_Exchange_Rate "AP Exchange Rate",
PO.AP_Invoice_Amt "AP Invoice Amt",
PO.GRN_DR_AMT "GRN DR AMT",
PO.TAX_Class "TAX Class",
PO.TAX_Class_Name "TAX Class Name",
PO.TAX_Chap "TAX Chap",
PO.TAX_CHAP_Name "TAX CHAP Name",
PO.TAX_ENV "TAX ENV",
PO.TAX_ENV_name "TAX ENV name",
PO.Payment_Date "Payment Date",
PO.Amount_Paid "Amount Paid",
PO.Payment_Method_Type "Payment Method Type",
PO.Cheque_UTR "Cheque UTR",
po.Payment_Document_Name "Payment Document Name",
(gcc1.segment1
||'-'
|| gcc1.segment2
||'-'
|| gcc1.segment3
||'-'
|| gcc1.segment4
||'-'
|| gcc1.segment5
||'-'
|| gcc1.segment6
||'-'
||gcc1.segment7 ) "GRN_DR_ACCT",
ffvv1.description "GRN_CR_ACCT_Descr",
(gcc.segment1
||'-'
|| gcc.segment2
||'-'
|| gcc.segment3
||'-'
|| gcc.segment4
||'-'
|| gcc.segment5
||'-'
|| gcc.segment6
||'-'
||gcc.segment7 ) "GRN_CR_ACCT",
ffvv.description "GRN_DR_ACCT_Descr",
igst_invoice.igst_rate "Invoice IGST Percent",
igst_invoice.igst_amount "Invoice IGST Amt",
cgst_invoice.cgst_rate "Invoice CGST Percent",
cgst_invoice.cgst_amount "Invoice CGST Amt",
sgst_invoice.sgst_rate "Invoice SGST Percent",
sgst_invoice.sgst_amount "Invoice SGST Amt"
FROM
--po_line_locations_all plla,
poz_suppliers_v pos,
po_headers_all pha,
PO_BUYERS_VAL_V pbv,
po_lines_all pla,
po_distributions_all pda,
gl_code_combinations gcc1,
gl_code_combinations gcc,
fnd_flex_values_vl ffvv,
fnd_flex_values_vl ffvv1,
(select
rsh.receipt_num GRN_No,
To_char(rsh.expected_receipt_date, 'DD-MM-YYYY') GRN_Date,
rsl.quantity_received GRN_Quantity,
(
case when rsh.receipt_num IS NOT NULL then 'Y'
else 'N'
end
) GRN_Created,
rsh.CREATED_BY GRN_Create_User,
rsh.RA_DOC_REVISION_NUMBER Grn_Rev_Number,
To_char(rsh.RA_DOC_REVISION_DATE, 'DD-MM-YYYY') Grn_Rev_Date,
rsh.CREATED_BY Rec_Created_By,
rsh.ra_note_to_receiver GRN_Remarks,
rsl.po_header_id,
rsh.expected_receipt_date,
rsh.receipt_num
from
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
where 1=1
and rsh.shipment_header_id = rsl.shipment_header_id
) ship,
(select
aia.invoice_currency_code Grn_Currency_Code,
aia.total_tax_amount GRN_GST_Amt,
aia.invoice_num AP_Invoice_Num,
aia.voucher_num AP_Vouch_No,
aila.line_number Invoice_Line_Number,
( CASE
WHEN aia.voucher_num IS NOT NULL THEN 'Y'
ELSE 'N'
END
) AP_Voucher_Created,
To_char(aia.invoice_date, 'DD-MM-YYYY') AP_invoice_date,
aia.exchange_rate AP_Exchange_Rate,
aia.invoice_amount AP_Invoice_Amt,
aila.amount GRN_DR_AMT,
aila.product_category TAX_Class,
aila.product_category TAX_Class_Name,
aila.product_category TAX_Chap,
aila.product_category TAX_CHAP_Name,
aila.primary_intended_use TAX_ENV,
aila.primary_intended_use TAX_ENV_name,
To_char(aipa.accounting_date, 'DD-MM-YYYY') Payment_Date,
aipa.amount_inv_curr Amount_Paid,
UPPER(aca.payment_method_code) Payment_Method_Type,
aca.check_number Cheque_UTR,
cpd.payment_document_name Payment_Document_Name,
aia.po_header_id,
aila.po_line_id,
aia.invoice_id,
aila.line_number,
aia.invoice_num,
aia.invoice_date
from
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ce_payment_documents cpd,
ap_invoice_distributions_all aida,
rcv_shipment_lines rsl
where 1=1
and rsl.po_line_id=aila.po_line_id
and aia.invoice_id = aila.invoice_id(+)
and aila.line_type_lookup_code (+)in ('ITEM','ACCRUAL')
AND aia.invoice_id = aipa.invoice_id(+)
AND aca.check_id(+) = aipa.check_id
AND aida.invoice_id = aia.invoice_id(+)
AND cpd.payment_document_id(+) = aca.payment_document_id
and aida.line_type_lookup_code (+)in ('ITEM','ACCRUAL')
and aia.cancelled_date is null
--and((aia.invoice_num in (:PO_INV_NUM)) or (least (:PO_INV_NUM) is null))
--and aia.invoice_num = 'UAT Test 61'
)po,
(select
rsl.quantity_received,
aila.unit_price,
aia.invoice_id,
sum(rsl.quantity_received * aila.unit_price) over (partition by aia.invoice_id) GRN_amount,
aia.po_header_id
from
rcv_shipment_lines rsl,
ap_invoice_lines_all aila,
ap_invoices_all aia
where 1=1
and aia.invoice_id = aila.invoice_id
and rsl.po_line_id=aila.po_line_id
) grn,
(SELECT DISTINCT
trx_id,
trx_line_id,
trx_line_number,
Abs (unrounded_tax_amt) igst_amount,
tax_rate igst_rate,
Abs (unrounded_taxable_amt) igst_taxable
FROM
zx_lines
WHERE
entity_code = 'PURCHASE_ORDER'
AND tax LIKE '%IN IGST%'
AND application_id = 201
) igst,
(SELECT DISTINCT
trx_id,
trx_line_id,
trx_line_number,
Abs (unrounded_tax_amt) cgst_amount,
tax_rate cgst_rate,
Abs (unrounded_taxable_amt) cgst_taxable
FROM
zx_lines
WHERE
entity_code = 'PURCHASE_ORDER'
AND tax LIKE '%IN CGST%'
AND application_id = 201
) cgst,
(SELECT DISTINCT
trx_id,
trx_line_id,
trx_line_number,
Abs (unrounded_tax_amt) sgst_amount,
tax_rate sgst_rate,
Abs (unrounded_taxable_amt) sgst_taxable
FROM
zx_lines
WHERE
entity_code = 'PURCHASE_ORDER'
AND tax LIKE '%IN SGST%'
AND application_id = 201
) sgst,
(SELECT DISTINCT
trx_id,
trx_line_id,
trx_line_number,
Abs (unrounded_tax_amt) igst_amount,
tax_rate igst_rate,
Abs (unrounded_taxable_amt) igst_taxable
FROM
zx_lines
WHERE
entity_code = 'AP_INVOICES'
AND tax LIKE '%IN IGST%'
AND application_id = 200
) igst_invoice,
(SELECT DISTINCT
trx_id,
trx_line_id,
trx_line_number,
Abs (unrounded_tax_amt) cgst_amount,
tax_rate cgst_rate,
Abs (unrounded_taxable_amt) cgst_taxable
FROM
zx_lines
WHERE
entity_code = 'AP_INVOICES'
AND tax LIKE '%IN CGST%'
AND application_id = 200
) cgst_invoice,
(SELECT DISTINCT
trx_id,
trx_line_id,
trx_line_number,
Abs (unrounded_tax_amt) sgst_amount,
tax_rate sgst_rate,
Abs (unrounded_taxable_amt) sgst_taxable
FROM
zx_lines
WHERE
entity_code = 'AP_INVOICES'
AND tax LIKE '%IN SGST%'
AND application_id = 200
) sgst_invoice
WHERE 1 = 1
and pda.CODE_COMBINATION_ID=gcc1.CODE_COMBINATION_ID
and pda.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
AND ffvv1.flex_value(+) = gcc1.segment3
AND ffvv1.enabled_flag(+) = 'Y'
AND ffvv.flex_value(+) = gcc.segment3
AND ffvv.enabled_flag(+) = 'Y'
AND pha.po_header_id = sgst.trx_id(+)
AND pla.po_line_id = sgst.trx_line_id(+)
AND pha.po_header_id = cgst.trx_id(+)
AND pla.po_line_id = cgst.trx_line_id(+)
AND pha.po_header_id = igst.trx_id(+)
AND pla.po_line_id = igst.trx_line_id(+)
AND po.invoice_id = igst_invoice.trx_id(+)
AND po.line_number = igst_invoice.trx_line_number(+)
AND po.invoice_id = sgst_invoice.trx_id(+)
AND po.line_number = sgst_invoice.trx_line_number(+)
AND po.invoice_id = cgst_invoice.trx_id(+)
AND po.line_number = cgst_invoice.trx_line_number(+)
and pha.po_header_id=grn.po_header_id(+)
and pha.po_header_id=po.po_header_id(+)
and pla.po_line_id=po.po_line_id(+)
AND pha.po_header_id=ship.po_header_id(+)
AND pha.vendor_id = pos.vendor_id
and pha.po_header_id = pla.po_header_id
AND pha.agent_id=pbv.person_id
AND pda.po_line_id = pla.po_line_id
--AND pbv.display_name='LTI GTPL SCM User'
AND ( Coalesce(NULL,:P_PO_NUM) IS NULL
OR pha.segment1 IN ( :P_PO_NUM ) )
AND ( ( Trunc(pha.creation_date) >= (:PO_FROM_DATE ) )
OR ( Least (:PO_FROM_DATE) IS NULL ) )
AND ( ( Trunc(pha.creation_date) <= (:PO_TO_DATE ) )
OR ( Least (:PO_TO_DATE) IS NULL ) )
and ((trunc(ship.expected_receipt_date)>= (:GRN_FROM_DATE)) OR (least (:GRN_FROM_DATE) is null))
and ((trunc(ship.expected_receipt_date)<= (:GRN_TO_DATE)) OR (least (:GRN_TO_DATE) is null))
and ((trunc(po.invoice_date)>= (:INV_FROM_DATE)) OR (least (:INV_FROM_DATE) is null))
and ((trunc(po.invoice_date)<= (:INV_TO_DATE)) OR (least (:INV_TO_DATE) is null))
and ((pos.VENDOR_NAME in (:PO_SUPP_NAME)) or (least (:PO_SUPP_NAME) is null))
and ((pos.segment1 in (:PO_SUPPLIER_CODE)) or (least (:PO_SUPPLIER_CODE) is null))
and ((pla.ATTRIBUTE1 in (:PO_NFA_NUM)) or (least (:PO_NFA_NUM) is null))
and ((ship.receipt_num in (:PO_REC_NUM)) or (least (:PO_REC_NUM) is null))
and ((po.invoice_num in (:PO_INV_NUM)) or (least (:PO_INV_NUM) is null))
No comments:
Post a Comment