Payment Schedule report query
SELECT
upper(ALC.DISPLAYED_FIELD) C_PAYMENT_METHOD,
APS.DUE_DATE C_DUE_DATE,
--decode(:h_sort_by_alternate, 'Y', PV.VENDOR_NAME_ALT, 'N', PV.VENDOR_NAME) C_SUPPLIER_NAME,
--decode(:h_sort_by_alternate, 'Y', PVS.VENDOR_SITE_CODE_ALT, 'N', PVS.VENDOR_SITE_CODE) C_SUPPLIER_SITE,
PV.VENDOR_NAME C_SUPPLIER_NAME_DISP,
PVS.VENDOR_SITE_CODE C_SUPPLIER_SITE_CODE_DISP,
ai.invoice_id invoice_id,
--AP1.INVOICE_AMOUNT TDS_INV_AMOUNT,
AI.INVOICE_DATE C_INVOICE_DATE,
AI.INVOICE_NUM INVOICE_NUM,
APS.GROSS_AMOUNT C_INVOICE_AMOUNT,
JIAT.TDS_INVOICE_NUM TDS_invoice_num,
JIAT.TDS_AMOUNT TDS_AMOUNT,
JIAT.TDS_TAX_RATE,
JITC.TAX_NAME C_TDS_CODE_NAME,
APS.AMOUNT_REMAINING C_SCHEDULED_AMOUNT,
/* ------------------------------------------
decode(sign(instr(AI.INVOICE_NUM, 'TDS-SI')),0,AI.INVOICE_NUM,null) not_Tds_si_Number,
decode(sign(instr(AI.INVOICE_NUM, 'TDS-SI')),0,APS.GROSS_AMOUNT,null) not_Tds_si_amount,
decode(sign(instr(AI.INVOICE_NUM, 'TDS-SI')),1,AI.INVOICE_NUM,null) Tds_si_Number,
decode(sign(instr(AI.INVOICE_NUM, 'TDS-SI')),1,APS.GROSS_AMOUNT,null) Tds_si_amount,
--JIAT.TDS_TAX_ID C_TAX_ID,
--JIAT.TDS_SECTION C_SECTION,
--JITC.TAX_RATE C_TDS_TAX_RATE,
-------------------------------------------*/
AI.invoice_type_lookup_code,
AI.DESCRIPTION C_INVOICE_DESCRIPTION,
--AID.DESCRIPTION C_LINE_DESCRIPTION,
AI.GL_DATE C_GL_DATE,
TO_CHAR(AI.GL_DATE,'MONTH') C_MONTH,
AI.DOC_CATEGORY_CODE C_VOUCHER_NO,
AI.INVOICE_CURRENCY_CODE C_CURRENCY_CODE,
PVS.ADDRESS_LINE1||', '||PVS.ADDRESS_LINE2||', '||PVS.ADDRESS_LINE3||', '||PVS.CITY||', '||PVS.STATE||', '||PVS.ZIP C_VENDOR_ADDRESS,
PV.SEGMENT1 C_PO_NUM
FROM
AP_LOOKUP_CODES ALC,
AP_PAYMENT_SCHEDULES APS,
AP_INVOICES AI,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
JA_IN_AP_TDS_INVOICES JIAT,
JA_IN_TAX_CODES JITC,
(SELECT * FROM AP_INVOICES WHERE invoice_type_lookup_code='STANDARD' ) AP1
WHERE
--APS.DUE_DATE <= :p_end_due_date and
nvl(APS.HOLD_FLAG,'N') <> 'Y' and -- 3153608
APS.PAYMENT_STATUS_FLAG in ('N', 'P') and --3893248
--AI.PAYMENT_CURRENCY_CODE = :p_payment_currency and
--(ALC.LOOKUP_CODE = :p_payment_method or :p_payment_method is null) and
ALC.LOOKUP_TYPE = 'PAYMENT METHOD' and
ALC.LOOKUP_CODE = APS.PAYMENT_METHOD_LOOKUP_CODE and
--(AI.PAY_GROUP_LOOKUP_CODE = :p_pay_group or :p_pay_group is null) and
--((APS.PAYMENT_PRIORITY between :p_priority_range_low and :p_priority_range_high)
-- or (:p_priority_range_low is null and :p_priority_range_high is null)) and
--(AI.VENDOR_ID = :p_supplier_name or :p_supplier_name is null) and
APS.INVOiCE_ID = AI.INVOICE_ID and
PV.VENDOR_ID = AI.VENDOR_ID and
PVS.VENDOR_SITE_ID = AI.VENDOR_SITE_ID and
to_chaR(AI.invoice_id)=ap1.attribute1 and
ap1.invoice_num like ai.invoice_num||'-TDS-SI%' and
ai.invoice_id = JIAT.invoice_id and
JIAT.TDS_TAX_ID=JITC.TAX_ID and
--and--ap1.attribute1='139675' and
--ap1.invoice_type_lookup_code='STANDARD' AND
AI.CANCELLED_DATE is NULL --and
-- Bug 957915
-- AI.APPROVED_AMOUNT != 0 and
-- Bug 1150311
--AP_INVOICES_PKG.GET_APPROVAL_STATUS
--(AI.INVOICE_ID, AI.INVOICE_AMOUNT, AI.PAYMENT_STATUS_FLAG, AI.INVOICE_TYPE_LOOKUP_CODE) IN ('APPROVED', 'UNPAID')
UNION
SELECT
upper(ALC.DISPLAYED_FIELD) C_PAYMENT_METHOD,
APS.DUE_DATE C_DUE_DATE,
--decode(:h_sort_by_alternate, 'Y', PV.VENDOR_NAME_ALT, 'N', PV.VENDOR_NAME) C_SUPPLIER_NAME,
--decode(:h_sort_by_alternate, 'Y', PVS.VENDOR_SITE_CODE_ALT, 'N', PVS.VENDOR_SITE_CODE) C_SUPPLIER_SITE,
PV.VENDOR_NAME C_SUPPLIER_NAME_DISP,
PVS.VENDOR_SITE_CODE C_SUPPLIER_SITE_CODE_DISP,
ai.invoice_id invoice_id,
--AP1.INVOICE_AMOUNT TDS_INV_AMOUNT,
AI.INVOICE_DATE C_INVOICE_DATE,
AI.INVOICE_NUM INVOICE_NUM,
APS.GROSS_AMOUNT C_INVOICE_AMOUNT,
NULL TDS_invoice_num,
NULL TDS_AMOUNT,
NULL tds_tax_rate,
NULL C_TDS_CODE_NAME,
APS.AMOUNT_REMAINING C_SCHEDULED_AMOUNT,
AI.invoice_type_lookup_code,
AI.DESCRIPTION C_INVOICE_DESCRIPTION,
--AID.DESCRIPTION C_LINE_DESCRIPTION,
AI.GL_DATE C_GL_DATE,
TO_CHAR(AI.GL_DATE,'MONTH') C_MONTH,
AI.DOC_CATEGORY_CODE C_VOUCHER_NO,
AI.INVOICE_CURRENCY_CODE C_CURRENCY_CODE,
PVS.ADDRESS_LINE1||', '||PVS.ADDRESS_LINE2||', '||PVS.ADDRESS_LINE3||', '||PVS.CITY||', '||PVS.STATE||', '||PVS.ZIP C_VENDOR_ADDRESS,
PV.SEGMENT1 C_PO_NUM
FROM
AP_LOOKUP_CODES ALC,
AP_PAYMENT_SCHEDULES APS,
AP_INVOICES AI,
PO_VENDORS PV,
PO_VENDOR_SITES PVS
--(SELECT * FROM AP_INVOICES WHERE invoice_type_lookup_code='STANDARD' ) AP1 --and invoice_num like invoice_num||'-TDS-SI%' ) AP1
WHERE
--APS.DUE_DATE <= :p_end_due_date and
nvl(APS.HOLD_FLAG,'N') <> 'Y' and -- 3153608
APS.PAYMENT_STATUS_FLAG in ('N', 'P') and --3893248
--AI.PAYMENT_CURRENCY_CODE = :p_payment_currency and
--(ALC.LOOKUP_CODE = :p_payment_method or :p_payment_method is null) and
ALC.LOOKUP_TYPE = 'PAYMENT METHOD' and
ALC.LOOKUP_CODE = APS.PAYMENT_METHOD_LOOKUP_CODE and
--(AI.PAY_GROUP_LOOKUP_CODE = :p_pay_group or :p_pay_group is null) and
--((APS.PAYMENT_PRIORITY between :p_priority_range_low and :p_priority_range_high)
-- or (:p_priority_range_low is null and :p_priority_range_high is null)) and
--(AI.VENDOR_ID = :p_supplier_name or :p_supplier_name is null) and
APS.INVOiCE_ID = AI.INVOICE_ID and
PV.VENDOR_ID = AI.VENDOR_ID and
PVS.VENDOR_SITE_ID = AI.VENDOR_SITE_ID and
--to_chaR(AI.invoice_id)=ap1.attribute1(+) and--ap1.attribute1='139675' and
--ap1.invoice_type_lookup_code='STANDARD' AND
AI.CANCELLED_DATE is NULL --and
-- Bug 957915
-- AI.APPROVED_AMOUNT != 0 and
-- Bug 1150311
--AP_INVOICES_PKG.GET_APPROVAL_STATUS
--(AI.INVOICE_ID, AI.INVOICE_AMOUNT, AI.PAYMENT_STATUS_FLAG, AI.INVOICE_TYPE_LOOKUP_CODE) IN ('APPROVED', 'UNPAID')
and ai.invoice_id NOT in
(SELECT
ai.invoice_id invoice_id
FROM
AP_LOOKUP_CODES ALC,
AP_PAYMENT_SCHEDULES APS,
AP_INVOICES AI,
PO_VENDORS PV,
PO_VENDOR_SITES PVS,
JA_IN_AP_TDS_INVOICES JIAT,
JA_IN_TAX_CODES JITC,
(SELECT * FROM AP_INVOICES WHERE invoice_type_lookup_code='STANDARD' ) AP1
WHERE
nvl(APS.HOLD_FLAG,'N') <> 'Y' and -- 3153608
APS.PAYMENT_STATUS_FLAG in ('N', 'P') and --3893248
ALC.LOOKUP_TYPE = 'PAYMENT METHOD' and
ALC.LOOKUP_CODE = APS.PAYMENT_METHOD_LOOKUP_CODE and
APS.INVOiCE_ID = AI.INVOICE_ID and
PV.VENDOR_ID = AI.VENDOR_ID and
PVS.VENDOR_SITE_ID = AI.VENDOR_SITE_ID and
to_chaR(AI.invoice_id)=ap1.attribute1 and
ap1.invoice_num like ai.invoice_num||'-TDS-SI%' and
ai.invoice_id = JIAT.invoice_id and
JIAT.TDS_TAX_ID=JITC.TAX_ID and
AI.CANCELLED_DATE is NULL --and
)
order by invoice_num
No comments:
Post a Comment