/* Sample script
to fetch the Invoice Register with tax details */
SELECT glcc.segment1, glcc.segment2, glcc.segment4, glcc.segment5,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
1,
glcc.segment1
) company,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
2,
glcc.segment2
) business_unit,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
3,
glcc.segment3
) business_area,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
4,
glcc.segment4
) region,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
5,
glcc.segment5
) LOCATION,
rat.customer_trx_id cust_id, rat.cust_trx_type_id,
ral.customer_trx_line_id line_id, rat.trx_number invoice_no,
rc.customer_name customer_name,
ral.interface_line_attribute4 customer_code,
rat.ct_reference REFERENCE, rat.trx_date invoice_date,
rat.trx_date,
ral.description invoice_description, rc.customer_id,
DECODE (rtp.TYPE,
'INV', 'Invoice',
'CM', 'Credit Memo',
'DM', 'Debit Memo',
'DEP', 'Deposit',
''
) invoice_type,
rat.set_of_books_id,
NVL (REPLACE (rat.interface_header_attribute14, CHR (9), ''),
''
) branch_oe_no,
NVL (REPLACE (ral.interface_line_attribute3, CHR (9), ''),
''
) contract_number,
ral.accounting_rule_duration DURATION, ral.rule_start_date from_date,
ADD_MONTHS (TO_DATE (ral.rule_start_date),
ral.accounting_rule_duration
) todate,
rat.created_by cus_created_by, NULL meaning,
NVL (REPLACE (rat.interface_header_attribute6, CHR (9), ''),
''
) "Project",
NVL
(REPLACE (rat.interface_header_attribute15, CHR (9), ''),
''
) original_customer_name,
rat.invoice_currency_code
FROM apps.ra_customer_trx_all rat,
apps.ra_customer_trx_lines_all ral,
apps.ra_cust_trx_line_gl_dist_all rag,
apps.gl_code_combinations glcc,
apps.hr_operating_units hr,
apps.ra_customers rc,
apps.ra_cust_trx_types_all rtp
WHERE rat.customer_trx_id = ral.customer_trx_id
AND rat.org_id = ral.org_id
AND rat.customer_trx_id = rag.customer_trx_id
AND ral.customer_trx_line_id = rag.customer_trx_line_id
-- AND rag.account_class = 'REC'
AND ral.line_type <> 'TAX'
AND rag.code_combination_id = glcc.code_combination_id
AND hr.organization_id = rat.org_id
AND rat.bill_to_customer_id = rc.customer_id
AND rat.cust_trx_type_id = rtp.cust_trx_type_id
AND rtp.NAME BETWEEN NVL (p_type_from, rtp.NAME)
AND NVL (p_type_to, rtp.NAME)
AND TRUNC (rat.trx_date)
BETWEEN TO_DATE (NVL (p_from_date,
TO_CHAR (SYSDATE, 'RRRR/MM/DD hh24:mi:ss')
),
'RRRR/MM/DD hh24:mi:ss'
)
AND TO_DATE (NVL (p_to_date,
TO_CHAR (SYSDATE, 'RRRR/MM/DD hh24:mi:ss')
),
'RRRR/MM/DD hh24:mi:ss'
)
AND rc.customer_id = NVL (p_customer, rc.customer_id)
GROUP BY glcc.segment1,
glcc.segment2,
glcc.segment3,
glcc.segment4,
glcc.segment5,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
1,
glcc.segment1
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
2,
glcc.segment2
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
3,
glcc.segment3
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
4,
glcc.segment4
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
5,
glcc.segment5
),
rat.invoice_currency_code,
rat.customer_trx_id,
rc.customer_id,
ral.customer_trx_line_id,
rat.cust_trx_type_id,
rat.trx_number,
ral.extended_amount,
rc.customer_name,
ral.interface_line_attribute4,
rat.ct_reference,
rat.trx_date,
ral.description,
ral.customer_trx_line_id,
ral.extended_amount,
NVL (REPLACE (rat.interface_header_attribute14, CHR (9), ''), ''),
NVL (REPLACE (ral.interface_line_attribute3, CHR (9), ''), ''),
ral.accounting_rule_duration,
ral.rule_start_date,
ADD_MONTHS (TO_DATE (ral.rule_start_date),
ral.accounting_rule_duration
),
DECODE (rtp.TYPE,
'INV', 'Invoice',
'CM', 'Credit Memo',
'DM', 'Debit Memo',
'DEP', 'Deposit',
''
),
rat.set_of_books_id,
rat.created_by,
rat.trx_date,
NVL (REPLACE (rat.interface_header_attribute6, CHR (9), ''), ''),
NVL (REPLACE (rat.interface_header_attribute15, CHR (9), ''), '');
By
Deepak J
SELECT glcc.segment1, glcc.segment2, glcc.segment4, glcc.segment5,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
1,
glcc.segment1
) company,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
2,
glcc.segment2
) business_unit,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
3,
glcc.segment3
) business_area,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
4,
glcc.segment4
) region,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
5,
glcc.segment5
) LOCATION,
rat.customer_trx_id cust_id, rat.cust_trx_type_id,
ral.customer_trx_line_id line_id, rat.trx_number invoice_no,
rc.customer_name customer_name,
ral.interface_line_attribute4 customer_code,
rat.ct_reference REFERENCE, rat.trx_date invoice_date,
rat.trx_date,
ral.description invoice_description, rc.customer_id,
DECODE (rtp.TYPE,
'INV', 'Invoice',
'CM', 'Credit Memo',
'DM', 'Debit Memo',
'DEP', 'Deposit',
''
) invoice_type,
rat.set_of_books_id,
NVL (REPLACE (rat.interface_header_attribute14, CHR (9), ''),
''
) branch_oe_no,
NVL (REPLACE (ral.interface_line_attribute3, CHR (9), ''),
''
) contract_number,
ral.accounting_rule_duration DURATION, ral.rule_start_date from_date,
ADD_MONTHS (TO_DATE (ral.rule_start_date),
ral.accounting_rule_duration
) todate,
rat.created_by cus_created_by, NULL meaning,
NVL (REPLACE (rat.interface_header_attribute6, CHR (9), ''),
''
) "Project",
NVL
(REPLACE (rat.interface_header_attribute15, CHR (9), ''),
''
) original_customer_name,
rat.invoice_currency_code
FROM apps.ra_customer_trx_all rat,
apps.ra_customer_trx_lines_all ral,
apps.ra_cust_trx_line_gl_dist_all rag,
apps.gl_code_combinations glcc,
apps.hr_operating_units hr,
apps.ra_customers rc,
apps.ra_cust_trx_types_all rtp
WHERE rat.customer_trx_id = ral.customer_trx_id
AND rat.org_id = ral.org_id
AND rat.customer_trx_id = rag.customer_trx_id
AND ral.customer_trx_line_id = rag.customer_trx_line_id
-- AND rag.account_class = 'REC'
AND ral.line_type <> 'TAX'
AND rag.code_combination_id = glcc.code_combination_id
AND hr.organization_id = rat.org_id
AND rat.bill_to_customer_id = rc.customer_id
AND rat.cust_trx_type_id = rtp.cust_trx_type_id
AND rtp.NAME BETWEEN NVL (p_type_from, rtp.NAME)
AND NVL (p_type_to, rtp.NAME)
AND TRUNC (rat.trx_date)
BETWEEN TO_DATE (NVL (p_from_date,
TO_CHAR (SYSDATE, 'RRRR/MM/DD hh24:mi:ss')
),
'RRRR/MM/DD hh24:mi:ss'
)
AND TO_DATE (NVL (p_to_date,
TO_CHAR (SYSDATE, 'RRRR/MM/DD hh24:mi:ss')
),
'RRRR/MM/DD hh24:mi:ss'
)
AND rc.customer_id = NVL (p_customer, rc.customer_id)
GROUP BY glcc.segment1,
glcc.segment2,
glcc.segment3,
glcc.segment4,
glcc.segment5,
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
1,
glcc.segment1
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
2,
glcc.segment2
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
3,
glcc.segment3
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
4,
glcc.segment4
),
apps.gl_flexfields_pkg.get_description_sql
(glcc.chart_of_accounts_id,
5,
glcc.segment5
),
rat.invoice_currency_code,
rat.customer_trx_id,
rc.customer_id,
ral.customer_trx_line_id,
rat.cust_trx_type_id,
rat.trx_number,
ral.extended_amount,
rc.customer_name,
ral.interface_line_attribute4,
rat.ct_reference,
rat.trx_date,
ral.description,
ral.customer_trx_line_id,
ral.extended_amount,
NVL (REPLACE (rat.interface_header_attribute14, CHR (9), ''), ''),
NVL (REPLACE (ral.interface_line_attribute3, CHR (9), ''), ''),
ral.accounting_rule_duration,
ral.rule_start_date,
ADD_MONTHS (TO_DATE (ral.rule_start_date),
ral.accounting_rule_duration
),
DECODE (rtp.TYPE,
'INV', 'Invoice',
'CM', 'Credit Memo',
'DM', 'Debit Memo',
'DEP', 'Deposit',
''
),
rat.set_of_books_id,
rat.created_by,
rat.trx_date,
NVL (REPLACE (rat.interface_header_attribute6, CHR (9), ''), ''),
NVL (REPLACE (rat.interface_header_attribute15, CHR (9), ''), '');
By
Deepak J
No comments:
Post a Comment