Query to get Open Invoices/CM for Deferred date/Immediate Days
SELECT 'OPEN A/R' conv_type,
NVL((SELECT 'FUTURE DEFERRED'
from apps.ra_customer_trx_lines_All xrctl, apps.ra_cust_trx_line_gl_dist_all xrctlgd
where xrctl.customer_trx_id = rct.customer_trx_id
and xrctlgd.customer_trx_line_id = xrctl.customer_trx_line_id
and xrctlgd.account_class = 'REV'
and xrctlgd.posting_control_id = -3
and xrctlgd.original_gl_date >= trunc(to_date('10012014','MMDDYYYY'))
and rownum = 1
),'REGULAR') deferred_or_regular
, trunc(to_date('10012014','MMDDYYYY')) deferred_cutoff_date
, rct.org_id
, rct.trx_number
, rct.customer_trx_id
, rct.trx_date
, rctt.type trx_type
, (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id)
open_amount
, (select sum(yrctlgd.acctd_amount)
from apps.ra_cust_trx_line_gl_dist_all yrctlgd, apps.ra_customer_trx_lines_all yrctl
where yrctlgd.account_class = 'REV'
and yrctlgd.posting_control_id = -3
and yrctlgd.customer_trx_line_id = yrctl.customer_trx_line_id
and yrctl.customer_trx_id = rct.customer_trx_id
and yrctl.line_type = 'LINE'
and yrctl.accounting_rule_id = 1003
and yrctlgd.original_gl_date >= trunc(to_date('10012014','MMDDYYYY'))
and nvl(yrctlgd.account_set_flag,'N') <> 'Y') deferred_amount
, (select sum(aps2.amount_due_original)
FROM apps.ar_payment_Schedules_All aps2
WHERE aps2.customer_trx_id = rct.customer_trx_id) total_invoice_amount
FROM apps.ra_customer_trx_all rct, apps.ra_cust_trx_types_all rctt
WHERE rct.org_id in
--and rct.trx_date < trunc(to_date('10012014','MMDDYYYY'))
AND rctt.cust_trx_type_id = rct.cust_trx_type_id
and rctt.org_id = rct.org_id
AND (SELECT sum(aps.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps
WHERE rct.customer_trx_id = aps.customer_trx_id) <> 0
Query to get NO Open Invoices/CM for Deferred dates (Deferred Invoices/CM have also Immediate lines)
SELECT 'NO OPEN A/R', 'FUTURE DEFERRED' deferred_or_regular
, trunc(to_date('10012014','MMDDYYYY')) deferred_cutoff_date
, rct.org_id
, rct.trx_number
, rct.customer_trx_id
, rct.trx_date
, rctt.type trx_type
, (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id)
open_amount
, (select sum(yrctlgd.acctd_amount)
from apps.ra_cust_trx_line_gl_dist_all yrctlgd, apps.ra_customer_trx_lines_all yrctl
where yrctlgd.account_class = 'REV'
and yrctlgd.posting_control_id = -3
and yrctlgd.customer_trx_line_id = yrctl.customer_trx_line_id
and yrctl.customer_trx_id = rct.customer_trx_id
and yrctl.line_type = 'LINE'
and yrctl.accounting_rule_id = 1003
and yrctlgd.original_gl_date >= trunc(to_date('10012014','MMDDYYYY'))
and nvl(yrctlgd.account_set_flag,'N') <> 'Y') deferred_amount
, (select sum(aps2.amount_due_original)
FROM apps.ar_payment_Schedules_All aps2
WHERE aps2.customer_trx_id = rct.customer_trx_id) total_invoice_amount
FROM apps.ra_customer_trx_all rct, apps.ra_cust_trx_types_all rctt
WHERE rct.org_id in
--and rct.trx_date < trunc(to_date('10012014','MMDDYYYY'))
AND rctt.cust_trx_type_id = rct.cust_trx_type_id
and rctt.org_id = rct.org_id
AND (SELECT sum(aps.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps
WHERE rct.customer_trx_id = aps.customer_trx_id) = 0
AND EXISTS
(SELECT NULL
FROM apps.ra_customer_trx_lines_All rctl1
WHERE rctl1.customer_trx_id = rct.customer_Trx_id
AND rctl1.accounting_rule_id = 1003)
and (select sum(rctlgd.acctd_amount)
from apps.ra_cust_trx_line_gl_dist_all rctlgd, apps.ra_customer_trx_lines_all rctl
where rctlgd.account_class = 'REV'
and rctlgd.posting_control_id = -3
and rctlgd.customer_trx_line_id = rctl.customer_trx_line_id
and rctl.customer_trx_id = rct.customer_trx_id
and rctl.line_type = 'LINE'
and rctl.accounting_rule_id = 1003
and rctlgd.original_gl_date >= trunc(to_date('10012014','MMDDYYYY'))
and nvl(rctlgd.account_set_flag,'N') <> 'Y') <> 0;
Query to get all the Installment Invoices
SELECT a."CONV_TYPE",
a."DEFERRED_OR_REGULAR",
a."DEFERRED_CUTOFF_DATE",
a."ORG_ID",
a."TRX_NUMBER",
a."CUSTOMER_TRX_ID",
a."TRX_DATE",
a."TRX_TYPE",
a."OPEN_AMOUNT",
a."DEFERRED_AMOUNT",
a."TOTAL_INVOICE_AMOUNT",
'inst'
FROM apps.tabs_gaia_ar_conv_header_mv a
WHERE a.org_id =
AND (SELECT NVL (COUNT ( * ), 0)
FROM ra_terms_lines rtl, ra_customer_trx_all rct
WHERE rct.customer_trx_id = a.customer_trx_id
AND rtl.term_id = rct.term_id) > 1
AND NVL (open_amount, 0) > 0
AND NVL (open_amount, 0) <> NVL (total_invoice_amount, 0);
Lines View to get one Immediate Line and one Deferred Line
CREATE OR REPLACE FORCE VIEW APPS.TABS_GAIA_AR_CONV_REV_OPEN_V
(
DEFERRED_OR_REGULAR,
ORG_ID,
TRX_NUMBER,
CUSTOMER_TRX_ID,
TRX_DATE,
ACCOUNT_CLASS,
CODE_COMBINATION_ID,
DEFERRED_START_DATE,
DEFRRED_END_DATE,
ACCOUNTING_DURATION,
DEFERRED_UNPOSTED_AMOUNT
)
AS
SELECT 'DEFERRED' deferred_or_regular,
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date,
rctlgd_d.account_class,
rctlgd_d.code_combination_id,
MIN (rctlgd_d.original_gl_date) deferred_start_date,
MAX (rctlgd_d.original_gl_date) defrred_end_date,
MONTHS_BETWEEN (MAX (rctlgd_d.original_gl_date),
MIN (rctlgd_d.original_gl_date))
+ 1
accounting_duration,
SUM(DECODE (rctlgd_d.account_class,
'UNEARN', -1 * rctlgd_d.acctd_amount,
rctlgd_d.acctd_amount))
deferred_unposted_amount
FROM apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'LINE'
AND rctl_d.accounting_rule_id = 1003
AND rctlgd_d.customer_trx_line_id = rctl_d.customer_trx_line_id
AND rctlgd_d.posting_control_id = -3
AND rctlgd_d.account_class IN ('UNEARN', 'REV')
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'
AND rctlgd_d.original_gl_date >=
TRUNC (TO_DATE ('10012014', 'MMDDYYYY')) --TRUNC (TO_DATE ('05012014', 'MMDDYYYY'))
GROUP BY rctlgd_d.account_class,
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date,
rctlgd_d.code_combination_id
UNION ALL
SELECT 'IMMEDIATE',
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date -- , min(rctl_d.line_number)
-- , 'OPEN_AR_AMOUNT(IMMEDIATE)' memo_line
,
rctlgd_d.account_class,
MIN (rctlgd_d.code_combination_id),
LAST_DAY (SYSDATE) POSTING_START_DATE,
LAST_DAY (SYSDATE) POSTING_END_DATE,
NULL accounting_duration,
NVL ( (SELECT SUM (aps1.amount_due_remaining)
FROM ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id), 0)
- NVL (
(SELECT SUM (rctlgd_d.acctd_amount)
deferred_unposted_amount
FROM ra_cust_trx_line_gl_dist_all rctlgd_d,
ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'LINE'
AND rctl_d.accounting_rule_id = 1003
AND rctlgd_d.customer_trx_line_id =
rctl_d.customer_trx_line_id
AND rctlgd_d.posting_control_id = -3
AND rctlgd_d.account_class = 'REV'
AND rctlgd_d.original_gl_date >=
TRUNC (TO_DATE ('10012014', 'MMDDYYYY')) --TRUNC (TO_DATE ('05012014', 'MMDDYYYY'))
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'),
0
)
immediate_posting_amount
FROM apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'LINE'
AND NVL (rctl_d.accounting_rule_id, -1) <> 1003
AND rctlgd_d.customer_trx_line_id = rctl_d.customer_trx_line_id
AND rctlgd_d.account_class = 'REV'
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'
GROUP BY rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date,
rctlgd_d.account_class
UNION ALL
SELECT 'IMMEDIATE' deferred_or_regular,
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date,
rctlgd_d.account_class,
MAX (rctlgd_d.code_combination_id),
LAST_DAY (SYSDATE) POSTING_START_DATE,
LAST_DAY (SYSDATE) POSTING_END_DATE,
NULL accounting_duration,
SUM (rctlgd_d.acctd_amount) * -1
deferred_unposted_amount
FROM apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rct.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'LINE'
AND rctl_d.accounting_rule_id = 1003
AND rctlgd_d.customer_trx_line_id = rctl_d.customer_trx_line_id
AND rctlgd_d.posting_control_id = -3
AND rctlgd_d.account_class IN ('REV')
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'
AND rctlgd_d.original_gl_date >=
TRUNC (TO_DATE ('10012014', 'MMDDYYYY')) -- TRUNC (TO_DATE ('05012014', 'MMDDYYYY'))
AND NOT EXISTS
(SELECT 'X'
FROM apps.ra_customeR_trx_lines_all rctl2
WHERE rctl2.customer_trx_id = rct.customer_trx_id
AND rctl2.line_type = 'LINE'
AND NVL (rctl2.accounting_rule_id, -1) <> 1003)
AND NVL ( (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id),
0) = 0
GROUP BY rctlgd_d.account_class,
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date
--rctlgd_d.code_combination_id
UNION ALL
SELECT 'IMMEDIATE',
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date -- , min(rctl_d.line_number)
-- , 'OPEN_AR_AMOUNT(IMMEDIATE)' memo_line
,
rctlgd_d.account_class,
MAX (rctlgd_d.code_combination_id),
LAST_DAY (SYSDATE) POSTING_START_DATE,
LAST_DAY (SYSDATE) POSTING_END_DATE,
NULL accounting_duration,
NVL ( (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id), 0)
- NVL (
(SELECT SUM (rctlgd_d.acctd_amount)
deferred_unposted_amount
FROM apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'LINE'
AND rctl_d.accounting_rule_id = 1003
AND rctlgd_d.customer_trx_line_id =
rctl_d.customer_trx_line_id
AND rctlgd_d.posting_control_id = -3
AND rctlgd_d.account_class = 'REV'
AND rctlgd_d.original_gl_date >=
TRUNC (TO_DATE ('10012014', 'MMDDYYYY')) --TRUNC (TO_DATE ('05012014', 'MMDDYYYY'))
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'),
0
)
immediate_posting_amount
FROM apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'LINE'
AND NVL (rctl_d.accounting_rule_id, -1) = 1003
AND rctlgd_d.customer_trx_line_id = rctl_d.customer_trx_line_id
AND rctlgd_d.account_class = 'REV'
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'
AND NOT EXISTS
(SELECT 'X'
FROM apps.ra_customeR_trx_lines_all rctl2
WHERE rctl2.customer_trx_id = rct.customer_trx_id
AND rctl2.line_type = 'LINE'
AND NVL (rctl2.accounting_rule_id, -1) <> 1003)
--AND rctlgd_d.original_gl_date >=
-- trunc(to_date('07312014','MMDDYYYY'))
AND NVL ( (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id),
0) <> 0
GROUP BY rctlgd_d.account_class,
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date
UNION ALL
SELECT 'IMMEDIATE',
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date -- , min(rctl_d.line_number)
-- , 'OPEN_AR_AMOUNT(IMMEDIATE)' memo_line
,
rctlgd_d.account_class,
MIN (rctlgd_d.code_combination_id),
LAST_DAY (SYSDATE) POSTING_START_DATE,
LAST_DAY (SYSDATE) POSTING_END_DATE,
NULL accounting_duration,
NVL ( (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id), 0)
immediate_posting_amount
FROM apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'CB'
AND NVL (rctl_d.accounting_rule_id, -1) <> 1003
AND rctlgd_d.customer_trx_line_id = rctl_d.customer_trx_line_id
AND rctlgd_d.account_class = 'REV'
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'
AND NOT EXISTS
(SELECT NULL
FROM apps.ra_customer_trx_lines_all xrctl
WHERE xrctl.customer_trx_id = rct.customer_trx_id
AND xrctl.line_type = 'LINE')
GROUP BY rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date,
rctlgd_d.account_class
UNION ALL
SELECT 'IMMEDIATE',
rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date -- , min(rctl_d.line_number)
-- , 'OPEN_AR_AMOUNT(IMMEDIATE)' memo_line
,
rctlgd_d.account_class,
MIN (rctlgd_d.code_combination_id),
LAST_DAY (SYSDATE) POSTING_START_DATE,
LAST_DAY (SYSDATE) POSTING_END_DATE,
NULL accounting_duration,
NVL ( (SELECT SUM (aps1.amount_due_remaining)
FROM apps.ar_payment_Schedules_All aps1
WHERE aps1.customer_trx_id = rct.customer_trx_id), 0)
immediate_posting_amount
FROM apps.ra_customer_trx_all rct,
apps.ra_cust_trx_line_gl_dist_all rctlgd_d,
apps.ra_customer_trx_lines_all rctl_d
WHERE rctl_d.customer_trx_id = rct.customer_trx_id
AND rctl_d.line_type = 'FREIGHT'
AND NVL (rctl_d.accounting_rule_id, -1) <> 1003
AND rctlgd_d.customer_trx_line_id = rctl_d.customer_trx_line_id
AND rctlgd_d.account_class = 'FREIGHT'
AND NVL (rctlgd_d.account_set_flag, 'N') <> 'Y'
AND NOT EXISTS
(SELECT NULL
FROM apps.ra_customer_trx_lines_all xrctl
WHERE xrctl.customer_trx_id = rct.customer_trx_id
AND xrctl.line_type = 'LINE')
GROUP BY rct.org_id,
rct.trx_number,
rct.customer_trx_id,
rct.trx_date,
rctlgd_d.account_class;
Lines View
CREATE OR REPLACE FORCE VIEW APPS.TABS_GAIA_AR_CONV_LINES_V
(
ORG_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
DEFERRED_OR_REGULAR,
DEFERRED_START_DATE,
DEFRRED_END_DATE,
ACCOUNTING_DURATION,
LINE_AMOUNT
)
AS
SELECT ORG_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
DEFERRED_OR_REGULAR,
DEFERRED_START_DATE,
DEFRRED_END_DATE,
MONTHS_BETWEEN (MAX (DEFERRED_START_DATE),
MIN (DEFRRED_END_DATE))
+ 1
accounting_duration,
SUM (DEFERRED_UNPOSTED_AMOUNT) LINE_AMOUNT
FROM apps.tabs_gaia_ar_conv_rev_open_v
WHERE account_class IN ('REV', 'FREIGHT')
GROUP BY ORG_ID,
CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
DEFERRED_OR_REGULAR,
DEFERRED_START_DATE,
DEFRRED_END_DATE;
No comments:
Post a Comment