SELECT pa.segment1 project_number,
pa.name project_name,
XX_GET_AGREEMENT_Num( pa.project_id) agreement_number,
ppc.customer_name,
(CASE WHEN ppc.customer_id in (4046,12040,5040,5044) THEN 'Internal'
ELSE 'External'
END) Internal_Or_External,
(SELECT FLEX_VALUE_MEANING
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID=1015618
AND DESCRIPTION IN (SELECT NAME FROM HR_ORGANIZATION_UNITS
WHERE organization_id =pa.carrying_out_organization_id)
) cost_center,
(SELECT NAME FROM hr_organization_units WHERE organization_id =pa.carrying_out_organization_id ) Business_unit,
rcta.trx_number invoice_number,
TRUNC(rcta.trx_date) invoice_date,
rcta.term_due_date invoice_due_date,
apsa.AMOUNT_DUE_REMAINING outstanding_amount,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
END) DAYS,
apsa.AMOUNT_DUE_REMAINING current_1,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END)past_Due_1_30,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_31_60,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_61_90,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_91_120,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_121_Plus
FROM RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
AR_PAYMENT_SCHEDULES_ALL apsa,
PA_PROJECTS_ALL PA,
PA_PROJECT_CUSTOMERS_V ppc
-- PA_AGREEMENTS_ALL paa,
-- PA_PROJECT_FUNDINGS ppf
WHERE 1=1
--AND RCTA.TRX_NUMBER='100000'
AND RCTA.STATUS_TRX='OP'
AND rcta.customer_trx_id =rctla.customer_trx_id
AND apsa.customer_trx_id =rcta.customer_trx_id
AND apsa.customer_trx_id =rctla.customer_trx_id
AND pa.segment1= rcta.interface_header_attribute1
AND ppc.project_id =pa.project_id
AND rcta.sold_to_customer_id =ppc.customer_id
--AND paa.agreement_id =ppf.agreement_id
--AND ppc.customer_id = paa.customer_id
--AND substr(paa.description,2,5)=pa.segment1
ORDER BY pa.segment1 ASC;
pa.name project_name,
XX_GET_AGREEMENT_Num( pa.project_id) agreement_number,
ppc.customer_name,
(CASE WHEN ppc.customer_id in (4046,12040,5040,5044) THEN 'Internal'
ELSE 'External'
END) Internal_Or_External,
(SELECT FLEX_VALUE_MEANING
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID=1015618
AND DESCRIPTION IN (SELECT NAME FROM HR_ORGANIZATION_UNITS
WHERE organization_id =pa.carrying_out_organization_id)
) cost_center,
(SELECT NAME FROM hr_organization_units WHERE organization_id =pa.carrying_out_organization_id ) Business_unit,
rcta.trx_number invoice_number,
TRUNC(rcta.trx_date) invoice_date,
rcta.term_due_date invoice_due_date,
apsa.AMOUNT_DUE_REMAINING outstanding_amount,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120 THEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date))
END) DAYS,
apsa.AMOUNT_DUE_REMAINING current_1,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 1 AND 30 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END)past_Due_1_30,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 31 AND 60 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_31_60,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 61 AND 90 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_61_90,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) BETWEEN 91 AND 120 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_91_120,
(CASE WHEN (TRUNC(sysdate) - TRUNC(rcta.term_due_date)) >=120 THEN apsa.AMOUNT_DUE_REMAINING
ELSE NULL
END) past_due_121_Plus
FROM RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
AR_PAYMENT_SCHEDULES_ALL apsa,
PA_PROJECTS_ALL PA,
PA_PROJECT_CUSTOMERS_V ppc
-- PA_AGREEMENTS_ALL paa,
-- PA_PROJECT_FUNDINGS ppf
WHERE 1=1
--AND RCTA.TRX_NUMBER='100000'
AND RCTA.STATUS_TRX='OP'
AND rcta.customer_trx_id =rctla.customer_trx_id
AND apsa.customer_trx_id =rcta.customer_trx_id
AND apsa.customer_trx_id =rctla.customer_trx_id
AND pa.segment1= rcta.interface_header_attribute1
AND ppc.project_id =pa.project_id
AND rcta.sold_to_customer_id =ppc.customer_id
--AND paa.agreement_id =ppf.agreement_id
--AND ppc.customer_id = paa.customer_id
--AND substr(paa.description,2,5)=pa.segment1
ORDER BY pa.segment1 ASC;
No comments:
Post a Comment