-----------------------------------------Payment Data---------------------------------------------------------------
SELECT
AIA.INVOICE_TYPE_LOOKUP_CODE,
ASA.VENDOR_NAME,
ASA.SEGMENT1 VENDOR_NUMBER,
ASSA.VENDOR_SITE_CODE,
HOU.NAME OU,
ACA.CHECK_ID,
ACA.CHECK_NUMBER,
AIA.INVOICE_ID,
AIA.INVOICE_NUM,
AILA.LINE_NUMBER,
AIA.PAY_GROUP_LOOKUP_CODE,
AILA.ATTRIBUTE6,
AILA.ATTRIBUTE7,
AILA.ATTRIBUTE4 LC_NUMBER_DFF,
AILA.ATTRIBUTE5 COST_FACTOR_DFF,
(select ffv.description from apps.fnd_flex_values_vl ffv
where ffv.FLEX_VALUE_SET_ID = 1017300
and ffv.flex_value = aila.ATTRIBUTE5) cost_factor_dff_name,
--(select ppet.name from pon_price_element_types_vl ppet
--where aila.cost_factor_id = ppet.price_element_type_id) cost_factor_dff,
(select distinct apv.COST_FACTOR_NAME from qa_results_v v, ap_invoice_lines_v apv
where v.plan_id = 108
and v.PO_HEADER_ID = aila.PO_HEADER_ID
and apv.INVOICE_ID = aila.invoice_id
and apv.line_number = aila.line_number) cost_factor_line,
AILA.attribute_category,
(select distinct vl.DESCRIPTIVE_FLEX_CONTEXT_NAME from FND_DESCR_FLEX_CONTEXTS_VL vl
where vl.DESCRIPTIVE_FLEX_CONTEXT_CODE = aila.attribute_category) attribute_category_name,
AILA.attribute4 LC_NO,
(select distinct apv.PO_NUMBER from qa_results_v v, ap_invoice_lines_v apv
where v.plan_id = 108
and v.PO_HEADER_ID = aila.PO_HEADER_ID
and apv.INVOICE_ID = aila.invoice_id
and apv.line_number = aila.line_number) PO_NUM,
AILA.attribute_category,
AILA.attribute4 LC_NO,
(select distinct v.CHARACTER2 from qa_results_v v
where v.plan_id = 108
and v.PO_HEADER_ID = aila.PO_HEADER_ID) LC_num_NEW,
(select distinct apv.COST_FACTOR_NAME from qa_results_v v, ap_invoice_lines_v apv
where v.plan_id = 108
and v.PO_HEADER_ID = aila.PO_HEADER_ID
and apv.INVOICE_ID = aila.invoice_id
and apv.line_number = aila.line_number) cost_factor,
ACA.BANK_ACCOUNT_NAME,
ACA.STATUS_LOOKUP_CODE,
TO_CHAR(ACA.CHECK_DATE) PAYMENT_DATE,
NG_NATURAL_ACCOUNT_CODE(AIDA.DIST_CODE_COMBINATION_ID) NATURAL_ACCOUNT_CODE,
NG_NATURAL_ACCOUNT_DESC(AIDA.DIST_CODE_COMBINATION_ID) NATURAL_ACCOUNT_DESC,
SUM(AIPA.AMOUNT) PAYMENT_AMOUNT,
AIPA.EXCHANGE_RATE PAYMENT_EXCH_RATE,
--SUM(AIPA.AMOUNT * NVL(AIPA.EXCHANGE_RATE,1)) PAYMENT_AMOUNT_FUNC_CURR,
SUM((AIDA.AMOUNT * NVL(AIDA.EXCHANGE_RATE,1))) Line_amount,
SUM(((AIDA.AMOUNT * NVL(AIDA.EXCHANGE_RATE,1)) / DECODE(NG_AP_GET_INVOICE_DIST_AMOUNT(AIA.INVOICE_ID),0,1,NG_AP_GET_INVOICE_DIST_AMOUNT(AIA.INVOICE_ID)))*(AIPA.AMOUNT * NVL(AIPA.EXCHANGE_RATE,1))) Amount
FROM
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_CHECKS_ALL ACA,
AP_SUPPLIERS ASA,
AP_SUPPLIER_SITES_ALL ASSA,
HR_OPERATING_UNITS HOU
WHERE
AIPA.INVOICE_ID(+) = AIA.INVOICE_ID
AND AIPA.ORG_ID(+) = AIA.ORG_ID
AND AIA.VENDOR_ID = ASA.VENDOR_ID
AND AIA.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AIA.ORG_ID = AILA.ORG_ID
AND AILA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND AILA.ORG_ID = AIDA.ORG_ID
AND AIPA.CHECK_ID = ACA.CHECK_ID
AND AIPA.ORG_ID = ACA.ORG_ID
--AND (AILA.ATTRIBUTE6 <> 'B2B' OR AILA.ATTRIBUTE6 IS NULL)
--AND AIA.PAY_GROUP_LOOKUP_CODE = 'IMPORT COTTON'
--AND AIA.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT'
--AND ACA.CHECK_NUMBER = '1935366'
--AND AIA.INVOICE_ID = 408551
--AND AILA.LINE_NUMBER = '32'
AND AIDA.AMOUNT <> 0
AND AIDA.PARENT_REVERSAL_ID IS NULL
AND NVL(AILA.DISCARDED_FLAG,'N') = 'N'
AND ACA.STATUS_LOOKUP_CODE <> 'VOIDED'
AND AILA.LINE_TYPE_LOOKUP_CODE NOT IN ('AWT','PREPAY')
AND TRUNC(AIPA.ACCOUNTING_DATE) BETWEEN '01-JAN-2016' AND '31-OCT-2020'
-- AND AILA.ATTRIBUTE_CATEGORY = 'Inventory Information_B2B'
-- AND AIA.INVOICE_ID= 423366
GROUP BY
ASA.VENDOR_NAME,
ASA.SEGMENT1,
ASSA.VENDOR_SITE_CODE,
HOU.NAME,
ACA.CHECK_ID,
ACA.CHECK_NUMBER,
AIA.INVOICE_ID,
AIA.INVOICE_NUM,
AILA.LINE_NUMBER,
AIA.PAY_GROUP_LOOKUP_CODE,
AILA.ATTRIBUTE6,
AILA.ATTRIBUTE7,
AILA.ATTRIBUTE5,
AILA.ATTRIBUTE_CATEGORY,
AILA.ATTRIBUTE4,
AILA.PO_HEADER_ID,
AILA.INVOICE_ID,
AILA.COST_FACTOR_ID,
AIPA.EXCHANGE_RATE,
ACA.BANK_ACCOUNT_NAME,
ACA.STATUS_LOOKUP_CODE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIDA.DIST_CODE_COMBINATION_ID,
TO_CHAR(ACA.CHECK_DATE)
----------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment