Danish Halim: AP Suppliers / Vendor Ledger Report in Oracle Apps R12 EBS ERP

Wednesday, 3 January 2018

AP Suppliers / Vendor Ledger Report in Oracle Apps R12 EBS ERP

----------- AP Suppliers / Vendor Ledger Report in Oracle Apps R12 EBS ERP ----------------

---- Basic Query ------

SELECT xx.*,
( (SELECT NVL (SUM (amount * NVL (aia.exchange_rate, 1)), 0)
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE vendor_id = xx.vendor_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = :p_in_num_ou_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
AND aia.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type,
aia.invoice_type_lookup_code)
AND aia.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE aia.payment_status_flag
END
)
AND aia.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND aila.accounting_date <=
DECODE (TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (xx.gl_date_date_type, 'MON-YY'), TO_DATE
(:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
TRUNC (xx.gl_date_date_type, 'Mon')
)
- 1)
- (SELECT NVL (SUM (amount * NVL (in_pay.exchange_rate, 1)), 0)
FROM ap_invoice_payments_all in_pay, ap_invoices_all in_inv
WHERE in_inv.invoice_id = in_pay.invoice_id
AND in_inv.vendor_id = xx.vendor_id
AND in_inv.org_id = :p_in_num_ou_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(in_inv.invoice_id,
in_inv.invoice_amount,
in_inv.payment_status_flag,
in_inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
AND in_inv.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type,
in_inv.invoice_type_lookup_code
)
AND in_inv.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE in_inv.payment_status_flag
END
)
AND in_inv.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND in_pay.accounting_date <=
DECODE (TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (xx.gl_date_date_type, 'MON-YY'), TO_DATE
(:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
TRUNC (xx.gl_date_date_type, 'Mon')
)
- 1)
) opening_bal
FROM (SELECT inv.vendor_id,
sup.segment1 || ' - ' || sup.vendor_name supplier_name,
inv.doc_sequence_value voucher_num,
DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'Validated'
) status,
TO_CHAR (inv_line.accounting_date, 'DD-Mon-YYYY') gl_date,
inv.invoice_num bill_num,
TO_CHAR (inv.invoice_date, 'DD-Mon-YYYY') bill_date,
po.segment1 po_num,
TO_CHAR (po.creation_date, 'DD-Mon-YYYY') po_date,
inv_line.line_type_lookup_code,
DECODE
(inv_line.line_type_lookup_code,
'TAX', DECODE
(inv_line.line_type_lookup_code,
'AWT', CASE
WHEN (SELECT invoice_amount
FROM ap_invoices_all aia,
ap_suppliers supl
WHERE SUBSTR
(aia.invoice_num,
INSTR (aia.invoice_num,
'-'
)
+ 2,
( INSTR
(aia.invoice_num,
'-',
1,
2
)
- 1
)
- ( INSTR
(aia.invoice_num,
'-',
1,
1
)
+ 2
)
) = inv.invoice_id
AND aia.vendor_id = supl.vendor_id
AND supl.vendor_name =
'TDS Authority'
AND ROWNUM = 1) =
(-1 * inv_line.amount
)
THEN 'TDS'
ELSE 'VDS'
END,
inv.description
)
|| '-TAX',
DECODE
(inv_line.line_type_lookup_code,
'AWT', CASE
WHEN (SELECT invoice_amount
FROM ap_invoices_all aia,
ap_suppliers supl
WHERE SUBSTR (aia.invoice_num,
INSTR (aia.invoice_num,
'-')
+ 2,
( INSTR (aia.invoice_num,
'-',
1,
2
)
- 1
)
- ( INSTR (aia.invoice_num,
'-',
1,
1
)
+ 2
)
) = inv.invoice_id
AND aia.vendor_id = supl.vendor_id
AND supl.vendor_name = 'TDS Authority'
AND ROWNUM = 1) = (-1 * inv_line.amount)
THEN 'TDS'
ELSE 'VDS'
END,
inv.description
)
) description,
NULL payment_ref_num,
CASE
WHEN inv_line.amount < 0
THEN -1
* NVL (inv_line.base_amount, inv_line.amount)
END db_amount,
CASE
WHEN inv_line.amount > 0
THEN NVL (inv_line.base_amount,
inv_line.amount
)
END cr_amount,
DECODE
(TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (inv_line.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (TRUNC (inv_line.accounting_date, 'Mon'),
'DD-Mon-YYYY'
)
) first_day,
DECODE
(TO_CHAR (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (inv_line.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (LAST_DAY (inv_line.accounting_date),
'DD-Mon-YYYY'
)
) LAST_DAY,
TO_CHAR (inv_line.accounting_date, 'Month') MONTH,
inv_line.accounting_date gl_date_date_type,
'INVOICE' trx_type
FROM ap_invoices_all inv,
ap_invoice_lines_all inv_line,
ap_suppliers sup,
po_headers_all po,
(SELECT invoice_id, po_header_id,
COUNT (po_header_id) po_count
FROM ap_invoice_lines_all line
WHERE po_header_id IS NOT NULL
GROUP BY invoice_id, po_header_id) invl
WHERE 1 = 1
AND inv.invoice_id = inv_line.invoice_id
AND inv_line.invoice_id = invl.invoice_id(+)
AND invl.po_header_id = po.po_header_id(+)
AND inv.vendor_id = sup.vendor_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
--Program Paramters
AND inv.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE inv.payment_status_flag
END
)
AND inv.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND inv.org_id = :p_in_num_ou_id
AND inv.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type, inv.invoice_type_lookup_code)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
NVL (:p_in_chr_sup_class,
NVL (vendor_type_lookup_code, 'NA')
)
AND inv_line.accounting_date
BETWEEN NVL (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
inv.gl_date
)
AND NVL (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
inv.gl_date
)
AND sup.segment1 BETWEEN NVL (:p_in_chr_sup_num_f, sup.segment1)
AND NVL (:p_in_chr_sup_num_t, sup.segment1)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
(CASE
WHEN :p_in_chr_sup_type = 'EMPLOYEE'
THEN 'EMPLOYEE'
ELSE NVL (sup.vendor_type_lookup_code, 'NA')
END
)
AND NVL (sup.vendor_type_lookup_code, 'NA') <>
(CASE
WHEN :p_in_chr_sup_type = 'OUTSIDE_SUPPLIERS'
THEN 'EMPLOYEE'
ELSE 'OUTSIDE_SUPPLIERS'
END
)
--UNION ALL
UNION ALL
SELECT inv.vendor_id,
sup.segment1 || ' - ' || sup.vendor_name supplier_name,
aca.doc_sequence_value voucher_num,
DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'Validated'
) status,
TO_CHAR (pay.accounting_date, 'DD-Mon-YYYY') gl_date,
inv.invoice_num bill_num,
TO_CHAR (inv.invoice_date, 'DD-Mon-YYYY') bill_date,
poha.segment1 po_num,
TO_CHAR (poha.creation_date, 'DD-Mon-YYYY') po_date,
NULL line_type_lookup_code, inv.description,
aca.check_number payment_ref_num,
(CASE
WHEN pay.amount > 0
THEN pay.amount * NVL (aca.exchange_rate, 1)
END
) db_amount,
(CASE
WHEN pay.amount < 0
THEN -1 * pay.amount * NVL (aca.exchange_rate, 1)
END
) cr_amount,
DECODE
(TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (pay.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (TRUNC (pay.accounting_date, 'Mon'),
'DD-Mon-YYYY'
)
) first_day,
DECODE (TO_CHAR (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (pay.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (LAST_DAY (pay.accounting_date),
'DD-Mon-YYYY'
)
) LAST_DAY,
TO_CHAR (pay.accounting_date, 'Month') MONTH,
pay.accounting_date gl_date_date_type, 'PAYMENT' trx_type
FROM ap_invoices_all inv,
ap_suppliers sup,
ap_invoice_payments_all pay,
ap_checks_all aca,
po_headers_all poha,
(SELECT invoice_id, po_header_id,
COUNT (po_header_id) po_count
FROM ap_invoice_lines_all line
WHERE po_header_id IS NOT NULL
AND line.discarded_flag != 'Y'
AND line.cancelled_flag = 'N'
GROUP BY invoice_id, po_header_id) invl
WHERE inv.vendor_id = sup.vendor_id
AND pay.invoice_id = inv.invoice_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
AND aca.check_id(+) = pay.check_id
AND inv.invoice_id = invl.invoice_id(+)
AND invl.po_header_id = poha.po_header_id(+)
AND pay.amount != 0
--Concurrent Program Paramters
AND inv.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE inv.payment_status_flag
END
)
AND inv.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND aca.org_id = :p_in_num_ou_id
AND inv.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type, inv.invoice_type_lookup_code)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
NVL (:p_in_chr_sup_class,
NVL (vendor_type_lookup_code, 'NA')
)
AND sup.segment1 BETWEEN NVL (:p_in_chr_sup_num_f, sup.segment1)
AND NVL (:p_in_chr_sup_num_t, sup.segment1)
AND pay.accounting_date
BETWEEN NVL (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
pay.accounting_date
)
AND NVL (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
pay.accounting_date
)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
(CASE
WHEN :p_in_chr_sup_type = 'EMPLOYEE'
THEN 'EMPLOYEE'
ELSE NVL (vendor_type_lookup_code, 'NA')
END
)
AND NVL (sup.vendor_type_lookup_code, 'NA') <>
(CASE
WHEN :p_in_chr_sup_type = 'OUTSIDE_SUPPLIERS'
THEN 'EMPLOYEE'
ELSE 'OUTSIDE_SUPPLIERS'
END
)) xx
WHERE 1 = 1 AND (NVL (xx.db_amount, 0) != 0 OR NVL (xx.cr_amount, 0) != 0)
ORDER BY xx.supplier_name,
TO_CHAR (TO_DATE (xx.gl_date, 'DD-MON-YYYY'), 'YYYY-MM-DD'),
TO_CHAR (TO_DATE (xx.bill_date, 'DD-MON-YYYY'), 'YYYY-MM-DD')

------ End of Basic Query ---------------

------ XML REPORT CODE QUERY -----------------

<?xml version="1.0" encoding="UTF-8" ?>



<dataTemplate name="XXBEXSUPPLIERLEDGER" version="1.0">
<properties>
<property name="xml_tag_case" value="upper" />
</properties>

<parameters>
<parameter name="P_IN_NUM_OU_ID" dataType = "number"></parameter>
<parameter name="P_IN_CHR_SUP_TYPE" dataType = "varchar2"></parameter>
<parameter name="P_IN_CHR_SUP_NUM_F" dataType = "varchar2"></parameter>
<parameter name="P_IN_CHR_SUP_NUM_T" dataType = "varchar2"></parameter>
<parameter name="P_IN_FROM_DATE" dataType = "varchar2"></parameter>
<parameter name="P_IN_TO_DATE" dataType = "varchar2"></parameter>
<parameter name="P_IN_CHR_TRANS_SOURCE" dataType = "varchar2"></parameter>
<parameter name="P_IN_CHR_TRANS_TYPE" dataType = "varchar2"></parameter>
<parameter name="P_IN_CHR_SUP_CLASS" dataType = "varchar2"></parameter>
</parameters>

<dataQuery>
<sqlStatement name="Q_INV">
<![CDATA[
SELECT xx.*,
( (SELECT NVL (SUM (amount * NVL (aia.exchange_rate, 1)), 0)
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE vendor_id = xx.vendor_id
AND aia.invoice_id = aila.invoice_id
AND aia.org_id = :p_in_num_ou_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
AND aia.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type,
aia.invoice_type_lookup_code)
AND aia.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE aia.payment_status_flag
END
)
AND aia.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND aila.accounting_date <=
DECODE (TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (xx.gl_date_date_type, 'MON-YY'), TO_DATE
(:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
TRUNC (xx.gl_date_date_type, 'Mon')
)
- 1)
- (SELECT NVL (SUM (amount * NVL (in_pay.exchange_rate, 1)), 0)
FROM ap_invoice_payments_all in_pay, ap_invoices_all in_inv
WHERE in_inv.invoice_id = in_pay.invoice_id
AND in_inv.vendor_id = xx.vendor_id
AND in_inv.org_id = :p_in_num_ou_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(in_inv.invoice_id,
in_inv.invoice_amount,
in_inv.payment_status_flag,
in_inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
AND in_inv.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type,
in_inv.invoice_type_lookup_code
)
AND in_inv.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE in_inv.payment_status_flag
END
)
AND in_inv.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND in_pay.accounting_date <=
DECODE (TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (xx.gl_date_date_type, 'MON-YY'), TO_DATE
(:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
TRUNC (xx.gl_date_date_type, 'Mon')
)
- 1)
) opening_bal
FROM (SELECT inv.vendor_id,
sup.segment1 || ' - ' || sup.vendor_name supplier_name,
inv.doc_sequence_value voucher_num,
DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'Validated'
) status,
TO_CHAR (inv_line.accounting_date, 'DD-Mon-YYYY') gl_date,
inv.invoice_num bill_num,
TO_CHAR (inv.invoice_date, 'DD-Mon-YYYY') bill_date,
po.segment1 po_num,
TO_CHAR (po.creation_date, 'DD-Mon-YYYY') po_date,
inv_line.line_type_lookup_code,
DECODE
(inv_line.line_type_lookup_code,
'TAX', DECODE
(inv_line.line_type_lookup_code,
'AWT', CASE
WHEN (SELECT invoice_amount
FROM ap_invoices_all aia,
ap_suppliers supl
WHERE SUBSTR
(aia.invoice_num,
INSTR (aia.invoice_num,
'-'
)
+ 2,
( INSTR
(aia.invoice_num,
'-',
1,
2
)
- 1
)
- ( INSTR
(aia.invoice_num,
'-',
1,
1
)
+ 2
)
) = inv.invoice_id
AND aia.vendor_id = supl.vendor_id
AND supl.vendor_name =
'TDS Authority'
AND ROWNUM = 1) =
(-1 * inv_line.amount
)
THEN 'TDS'
ELSE 'VDS'
END,
inv.description
)
|| '-TAX',
DECODE
(inv_line.line_type_lookup_code,
'AWT', CASE
WHEN (SELECT invoice_amount
FROM ap_invoices_all aia,
ap_suppliers supl
WHERE SUBSTR (aia.invoice_num,
INSTR (aia.invoice_num,
'-')
+ 2,
( INSTR (aia.invoice_num,
'-',
1,
2
)
- 1
)
- ( INSTR (aia.invoice_num,
'-',
1,
1
)
+ 2
)
) = inv.invoice_id
AND aia.vendor_id = supl.vendor_id
AND supl.vendor_name = 'TDS Authority'
AND ROWNUM = 1) = (-1 * inv_line.amount)
THEN 'TDS'
ELSE 'VDS'
END,
inv.description
)
) description,
NULL payment_ref_num,
CASE
WHEN inv_line.amount < 0
THEN -1
* NVL (inv_line.base_amount, inv_line.amount)
END db_amount,
CASE
WHEN inv_line.amount > 0
THEN NVL (inv_line.base_amount,
inv_line.amount
)
END cr_amount,
DECODE
(TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (inv_line.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (TRUNC (inv_line.accounting_date, 'Mon'),
'DD-Mon-YYYY'
)
) first_day,
DECODE
(TO_CHAR (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (inv_line.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (LAST_DAY (inv_line.accounting_date),
'DD-Mon-YYYY'
)
) LAST_DAY,
TO_CHAR (inv_line.accounting_date, 'Month') MONTH,
inv_line.accounting_date gl_date_date_type,
'INVOICE' trx_type
FROM ap_invoices_all inv,
ap_invoice_lines_all inv_line,
ap_suppliers sup,
po_headers_all po,
(SELECT invoice_id, po_header_id,
COUNT (po_header_id) po_count
FROM ap_invoice_lines_all line
WHERE po_header_id IS NOT NULL
GROUP BY invoice_id, po_header_id) invl
WHERE 1 = 1
AND inv.invoice_id = inv_line.invoice_id
AND inv_line.invoice_id = invl.invoice_id(+)
AND invl.po_header_id = po.po_header_id(+)
AND inv.vendor_id = sup.vendor_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
--Program Paramters
AND inv.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE inv.payment_status_flag
END
)
AND inv.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND inv.org_id = :p_in_num_ou_id
AND inv.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type, inv.invoice_type_lookup_code)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
NVL (:p_in_chr_sup_class,
NVL (vendor_type_lookup_code, 'NA')
)
AND inv_line.accounting_date
BETWEEN NVL (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
inv.gl_date
)
AND NVL (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
inv.gl_date
)
AND sup.segment1 BETWEEN NVL (:p_in_chr_sup_num_f, sup.segment1)
AND NVL (:p_in_chr_sup_num_t, sup.segment1)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
(CASE
WHEN :p_in_chr_sup_type = 'EMPLOYEE'
THEN 'EMPLOYEE'
ELSE NVL (sup.vendor_type_lookup_code, 'NA')
END
)
AND NVL (sup.vendor_type_lookup_code, 'NA') <>
(CASE
WHEN :p_in_chr_sup_type = 'OUTSIDE_SUPPLIERS'
THEN 'EMPLOYEE'
ELSE 'OUTSIDE_SUPPLIERS'
END
)
--UNION ALL
UNION ALL
SELECT inv.vendor_id,
sup.segment1 || ' - ' || sup.vendor_name supplier_name,
aca.doc_sequence_value voucher_num,
DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'CANCELLED', 'Cancelled',
'Validated'
) status,
TO_CHAR (pay.accounting_date, 'DD-Mon-YYYY') gl_date,
inv.invoice_num bill_num,
TO_CHAR (inv.invoice_date, 'DD-Mon-YYYY') bill_date,
poha.segment1 po_num,
TO_CHAR (poha.creation_date, 'DD-Mon-YYYY') po_date,
NULL line_type_lookup_code, inv.description,
aca.check_number payment_ref_num,
(CASE
WHEN pay.amount > 0
THEN pay.amount * NVL (aca.exchange_rate, 1)
END
) db_amount,
(CASE
WHEN pay.amount < 0
THEN -1 * pay.amount * NVL (aca.exchange_rate, 1)
END
) cr_amount,
DECODE
(TO_CHAR (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (pay.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (TRUNC (pay.accounting_date, 'Mon'),
'DD-Mon-YYYY'
)
) first_day,
DECODE (TO_CHAR (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'MON-YY'
),
TO_CHAR (pay.accounting_date, 'MON-YY'), TO_CHAR
(TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
'DD-Mon-YYYY'
),
TO_CHAR (LAST_DAY (pay.accounting_date),
'DD-Mon-YYYY'
)
) LAST_DAY,
TO_CHAR (pay.accounting_date, 'Month') MONTH,
pay.accounting_date gl_date_date_type, 'PAYMENT' trx_type
FROM ap_invoices_all inv,
ap_suppliers sup,
ap_invoice_payments_all pay,
ap_checks_all aca,
po_headers_all poha,
(SELECT invoice_id, po_header_id,
COUNT (po_header_id) po_count
FROM ap_invoice_lines_all line
WHERE po_header_id IS NOT NULL
AND line.discarded_flag != 'Y'
AND line.cancelled_flag = 'N'
GROUP BY invoice_id, po_header_id) invl
WHERE inv.vendor_id = sup.vendor_id
AND pay.invoice_id = inv.invoice_id
AND DECODE
(apps.ap_invoices_pkg.get_approval_status
(inv.invoice_id,
inv.invoice_amount,
inv.payment_status_flag,
inv.invoice_type_lookup_code
),
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Revalidation',
'Validated'
) = 'Validated'
AND aca.check_id(+) = pay.check_id
AND inv.invoice_id = invl.invoice_id(+)
AND invl.po_header_id = poha.po_header_id(+)
AND pay.amount != 0
--Concurrent Program Paramters
AND inv.payment_status_flag =
(CASE
WHEN :p_in_chr_trans_source = 'AP_INVOICE'
THEN 'N'
ELSE inv.payment_status_flag
END
)
AND inv.payment_status_flag <>
(CASE
WHEN :p_in_chr_trans_source = 'AP_PAYMENT'
THEN 'N'
ELSE 'NA'
END
)
AND aca.org_id = :p_in_num_ou_id
AND inv.invoice_type_lookup_code =
NVL (:p_in_chr_trans_type, inv.invoice_type_lookup_code)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
NVL (:p_in_chr_sup_class,
NVL (vendor_type_lookup_code, 'NA')
)
AND sup.segment1 BETWEEN NVL (:p_in_chr_sup_num_f, sup.segment1)
AND NVL (:p_in_chr_sup_num_t, sup.segment1)
AND pay.accounting_date
BETWEEN NVL (TO_DATE (:p_in_from_date,
'YYYY/MM/DD HH24:MI:SS'
),
pay.accounting_date
)
AND NVL (TO_DATE (:p_in_to_date,
'YYYY/MM/DD HH24:MI:SS'
),
pay.accounting_date
)
AND NVL (sup.vendor_type_lookup_code, 'NA') =
(CASE
WHEN :p_in_chr_sup_type = 'EMPLOYEE'
THEN 'EMPLOYEE'
ELSE NVL (vendor_type_lookup_code, 'NA')
END
)
AND NVL (sup.vendor_type_lookup_code, 'NA') <>
(CASE
WHEN :p_in_chr_sup_type = 'OUTSIDE_SUPPLIERS'
THEN 'EMPLOYEE'
ELSE 'OUTSIDE_SUPPLIERS'
END
)) xx
WHERE 1 = 1 AND (NVL (xx.db_amount, 0) != 0 OR NVL (xx.cr_amount, 0) != 0)
ORDER BY xx.supplier_name,
TO_CHAR (TO_DATE (xx.gl_date, 'DD-MON-YYYY'), 'YYYY-MM-DD'),
TO_CHAR (TO_DATE (xx.bill_date, 'DD-MON-YYYY'), 'YYYY-MM-DD')
]]>
</sqlStatement>

<sqlStatement name="Q_PARAM">
<![CDATA[
SELECT (lep.NAME) p_legal_entity,
DECODE (:p_in_chr_sup_type,
'EMPLOYEE', 'Employee',
'OUTSIDE_SUPPLIERS', 'Outside Supplier'
) p_supplier_type,
(SELECT segment1 || ' - ' || vendor_name
FROM ap_suppliers
WHERE segment1 = :p_in_chr_sup_num_f) p_supplier_name_from,
(SELECT segment1 || ' - ' || vendor_name
FROM ap_suppliers
WHERE segment1 = :p_in_chr_sup_num_t) p_supplier_name_to,
TO_CHAR (TO_DATE (:p_in_from_date, 'YYYY/MM/DD HH24:MI:SS'),
'DD-Mon-YYYY'
) p_date_from,
TO_CHAR (TO_DATE (:p_in_to_date, 'YYYY/MM/DD HH24:MI:SS'),
'DD-Mon-YYYY'
) p_date_to,
DECODE (:p_in_chr_trans_source,
'AP_INVOICE', 'Invoice Creation',
'AP_PAYMENT', 'Invoice Payment'
) p_transaction_source,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'INVOICE TYPE'
AND lookup_code = :p_in_chr_trans_type) p_transaction_type,
(SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'VENDOR TYPE'
AND lookup_code = :p_in_chr_sup_class) p_sup_classification
FROM xle_entity_profiles lep, xle_registrations reg, hr_operating_units hro
WHERE 1 = 1
AND lep.legal_entity_id = reg.source_id
AND lep.legal_entity_id = hro.default_legal_context_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND lep.transacting_entity_flag = 'Y'
AND reg.identifying_flag = 'Y'
AND hro.organization_id = :p_in_num_ou_id
]]>
</sqlStatement>
</dataQuery>

<dataStructure>
<group name="G_PARAM" dataType="varchar2" source="Q_PARAM">
<element name="P_LEGAL_ENTITY" dataType="varchar2" value="P_LEGAL_ENTITY"/>
<element name="P_SUPPLIER_TYPE" dataType="NUMBER" value="P_SUPPLIER_TYPE"/>
<element name="P_SUPPLIER_NAME_FROM" dataType="varchar2" value="P_SUPPLIER_NAME_FROM"/>
<element name="P_SUPPLIER_NAME_TO" dataType="varchar2" value="P_SUPPLIER_NAME_TO"/>
<element name="P_DATE_FROM" dataType="varchar2" value="P_DATE_FROM" />
<element name="P_DATE_TO" dataType="varchar2" value="P_DATE_TO"/>
<element name="P_TRANSACTION_SOURCE" dataType="varchar2" value="P_TRANSACTION_SOURCE"/>
<element name="P_TRANSACTION_TYPE" dataType="varchar2" value="P_TRANSACTION_TYPE"/>
<element name="P_SUP_CLASSIFICATION" dataType="varchar2" value="P_SUP_CLASSIFICATION"/>
</group>

<group name="G_INV" dataType="varchar2" source="Q_INV">
<element name="VENDOR_ID" dataType="NUMBER" value="VENDOR_ID"/>
<element name="SUPPLIER_NAME" dataType="varchar2" value="SUPPLIER_NAME"/>
<element name="VOUCHER_NUM" dataType="varchar2" value="VOUCHER_NUM"/>
<element name="GL_DATE" dataType="varchar2" value="GL_DATE"/>
<element name="BILL_NUM" dataType="varchar2" value="BILL_NUM"/>
<element name="BILL_DATE" dataType="varchar2" value="BILL_DATE" />
<element name="PO_NUM" dataType="varchar2" value="PO_NUM"/>
<element name="PO_DATE" dataType="varchar2" value="PO_DATE"/>
<element name="LINE_TYPE_LOOKUP_CODE" dataType="varchar2" value="LINE_TYPE_LOOKUP_CODE"/>
<element name="DESCRIPTION" dataType="varchar2" value="DESCRIPTION"/>
<element name="PAYMENT_REF_NUM" dataType="varchar2" value="PAYMENT_REF_NUM"/>
<element name="DB_AMOUNT" dataType="NUMBER" value="DB_AMOUNT"/>
<element name="CR_AMOUNT" dataType="NUMBER" value="CR_AMOUNT"/>
<element name="LAST_DAY" dataType="varchar2" value="LAST_DAY"/>
<element name="FIRST_DAY" dataType="varchar2" value="FIRST_DAY"/>
<element name="MONTH" dataType="varchar2" value="MONTH"/>
<element name="GL_DATE_DATE_TYPE" dataType="varchar2" value="GL_DATE_DATE_TYPE"/>
<element name="TRX_TYPE" dataType="varchar2" value="TRX_TYPE"/>
<element name="OPENING_BAL" dataType="NUMBER" value="OPENING_BAL"/>
</group>
</dataStructure>
</dataTemplate>

----------------- END OF XML REPROT CODE QUERY ---------------------
RTF file Download from Google Driver

https://drive.google.com/drive/my-drive

Xml Code :

https://drive.google.com/drive/my-drive

RTF Design

Danish Halim

Wednesday, 3 January 2018

AP Suppliers / Vendor Ledger Report in Oracle Apps R12 EBS ERP

No comments:

Post a Comment

How to change Employee Number Generation from Manual to Automatic in Oracle HRMS (When attempting to apply for a job in iRecruitment)